SQL UNION Tutorial

The SQL UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types.

Syntax

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

Example

Consider two tables, Employees and Contractors, both having columns Name and Age.

SELECT Name, Age
FROM Employees
UNION
SELECT Name, Age
FROM Contractors;

This query will return a list of unique names and ages from both tables.

Key Points

• Column Matching: The number of columns and their data types must match in all SELECT statements.
• Duplicate Removal: UNION removes duplicate rows. Use UNION ALL to include duplicates.
• Ordering: Use ORDER BY at the end to sort the combined result set.

UNION vs UNION ALL

• UNION: Removes duplicates.
• UNION ALL: Includes duplicates, which can be faster as it does not perform the duplicate check.

SELECT Name, Age
FROM Employees
UNION ALL
SELECT Name, Age
FROM Contractors;

Best Practices

• Ensure column data types are compatible.
• Use UNION ALL when duplicates are acceptable for better performance.
• Always test queries to ensure they return the expected results.

For more advanced usage, consider combining UNION with other SQL clauses like WHERE, GROUP BY, and HAVING.