UNION vs UNION ALL

The UNION and UNION ALL operators are used to combine results from multiple SELECT statements. Both require the same number of columns and compatible data types in each query.

UNION

• Removes duplicate rows from the final result set.
• May require extra processing since duplicates must be checked.

Example:

SELECT customer_id FROM Customers
UNION
SELECT customer_id FROM Orders;

UNION ALL

• Returns all rows, including duplicates.
• Faster than UNION because no duplicate check is performed.

Example:

SELECT customer_id FROM Customers
UNION ALL
SELECT customer_id FROM Orders;

Key Difference

If a customer_id appears in both Customers and Orders:

• Using UNION, it will appear once.
• Using UNION ALL, it will appear twice (or more if it exists multiple times).

‍