What is the SQL UNION Operator?

The SQL UNION operator is used to combine the result sets of two or more SELECT statements. It ensures that the returned result set contains only unique values, making it highly useful for merging data from multiple tables.

• Each SELECT statement must have the same number of columns.
• The data types of the columns must be similar.
• The columns must be in the same order.

UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
        

By default, the UNION operator removes duplicate rows. To include duplicates, use UNION ALL.

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
        

Example: Combining City Data

Suppose we have two tables: Customers and Suppliers. Below is the Indian version of the data:

Customers Table

Suppliers Table

We can combine the cities from both tables using the following SQL query:

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

This query will return distinct city names:

• Chennai
• Delhi
• Mumbai