MySQL : UNION Operator

In MySQL, the UNION operator is used to combine the results of two or more SELECT statements into a single result set. It removes duplicate rows by default.

Here's the basic syntax of the UNION operator:


SELECT columns
FROM table1
WHERE condition1
UNION
SELECT columns
FROM table2
WHERE condition2;
 

  • columns: The columns you want to retrieve from the tables.
  • table1, table2: The names of the tables you want to retrieve data from.
  • condition1, condition2: Optional conditions to filter rows in each SELECT statement.

Example:

Suppose we have two tables: customers and suppliers. Both tables have the same structure with columns name and city. We want to retrieve a list of all unique names and cities from both tables.


SELECT name, city FROM customers
UNION
SELECT name, city FROM suppliers;
 

In this query:

  • The first SELECT statement retrieves names and cities from the customers table.
  • The second SELECT statement retrieves names and cities from the suppliers table.
  • The UNION operator combines the results of both SELECT statements into a single result set, removing duplicate rows by default.

UNION ALL:

If you want to include duplicate rows in the result set, you can use the UNION ALL operator instead of UNION. UNION ALL does not remove duplicate rows.

Example with UNION ALL:


SELECT name, city FROM customers
UNION ALL
SELECT name, city FROM suppliers;
 

In this query, the result set will include all rows from both tables, including duplicates.

The UNION operator is useful when you want to combine the results of multiple queries into a single result set, especially when dealing with similar data structures across different tables.