SQL UNION

UNION Basics

This returns a combined list of unique cities from both tables.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

Column Requirements

When using UNION or UNION ALL to combine the results of two or more SELECT statements, it's important that the queries involved meet specific compatibility requirements:

• Number of Columns: All SELECT statements must return the same number of columns.
• Data Types: The corresponding columns in each SELECT must be of compatible or convertible data types (e.g., string with string, number with number).
• Column Names: The final column names in the result are taken from the first SELECT statement.
• Order Matters: Matching is based on the order of the columns, not their names. So even if the column names are different, they must appear in the same positions and have compatible types.

-- Both queries must return 2 columns: a name and a country
SELECT name, country FROM customers
UNION
SELECT supplier_name, country FROM suppliers;

In this example:

• name from customers and supplier_name from suppliers are expected to be text-compatible.
• country is used in both queries and should be of the same type (e.g., VARCHAR).

If the structure or types don't match, the SQL engine will raise an error. Always test your queries independently first, and ensure alignment before using UNION.

UNION ALL

This includes all duplicates. Faster than UNION because it skips deduplication.

SELECT country FROM customers
UNION ALL
SELECT country FROM suppliers;

ORDER BY with UNION

Apply ORDER BY after the final SELECT statement, not within each SELECT.

SELECT name FROM employees
UNION
SELECT name FROM managers
ORDER BY name;

Best Practices

• Ensure SELECT queries return same number and compatible types of columns
• Use UNION ALL if duplicates are acceptable for better performance
• Wrap individual queries in parentheses when writing complex joins

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic