SQL UNION Operator

The UNION operator in SQL combines the result sets of two or more SELECT statements into a single result set. It's a very useful way to consolidate data from different queries.

UNION: Definition and Usage

UNION is particularly helpful when you need to gather data from multiple tables or views that have a similar structure. A key point is that UNION automatically removes duplicate rows. This means that if the same row appears in the results of multiple queries being combined, UNION will only include that row once in the final output.

Requirements for Using UNION

  • All SELECT statements must have the same number of columns.
  • The corresponding columns must have compatible data types.
  • The columns in each SELECT statement must be in the same order.

Syntax

Syntax

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2
ORDER BY column_name;

Example

Combining City Data from Two Tables

This example combines the list of cities from the 'Customers' and 'Suppliers' tables, showing only unique city names (duplicates are removed). (This assumes that 'Customers' and 'Suppliers' tables exist with a 'City' column.)

Syntax

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
Output

City
-------
(A list of unique city names from both Customers and Suppliers tables, sorted alphabetically)
**Note:** The example output shows a general structure. The specific cities and their order will depend on the data in your `Customers` and `Suppliers` tables. The `ORDER BY City` clause ensures alphabetical ordering. If a city appears in both tables, it appears only once in the result because `UNION` removes duplicates. To keep duplicates, use `UNION ALL`.