SQL UNION and UNION ALL

A join puts columns side by side; UNION stacks rows on top of each other. Use it to merge similar lists from different tables or conditions: all people in a mailing list (customers and employees), or a report row plus a total row. UNION removes duplicates, UNION ALL keeps them.

UNION syntax

SELECT column1, column2 FROM table1UNION [ALL]SELECT column1, column2 FROM table2[ORDER BY ...];

Rules:

• every SELECT must return the same number of columns;
• columns are matched by position, and should have compatible types;
• the column names come from the first SELECT;
• one ORDER BY at the very end sorts the combined result.

Combine two lists

Every city where we have an employee or a customer:

SELECT city FROM employeesUNIONSELECT city FROM customersORDER BY city;

UNION ALL keeps duplicates

SELECT city FROM employeesUNION ALLSELECT city FROM customersORDER BY city;

Label where each row came from

A contact list from two tables, with a type column:

SELECT CONCAT(first_name, ' ', last_name) AS name, city, 'Employee' AS typeFROM employeesWHERE city = 'Mumbai'UNION ALLSELECT customer_name, city, 'Customer'FROM customersWHERE city = 'Mumbai'ORDER BY type, name;

A total row

SELECT category, SUM(stock) AS unitsFROM productsGROUP BY categoryUNION ALLSELECT 'TOTAL', SUM(stock)FROM products;

Column count must match

SELECT first_name, city FROM employeesUNIONSELECT customer_name FROM customers;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

UNION vs JOIN

SELECT first_name AS name FROM employees WHERE first_name LIKE 'A%'UNIONSELECT customer_name FROM customers WHERE customer_name LIKE 'A%'ORDER BY name;

UNION FAQ