UNIONs vs JOINs
tags: #sql/complex_queries
UNION ALL is used to combine rows from different queries into a single result set, where JOIN is used to retrieve data from multiple tables based on a related column.
UNION ALL
Combines the result sets of two or more SELECT statements into a single result set.
- When you want to combine rows from multiple queries vertically, one after another.
- The columns in each
SELECTstatement must have the same number of columns and compatible data types.
Duplicates are included in the result set, and the order is maintained. This means that if a row appears in multiple SELECT statements being combined with UNION ALL, it will appear multiple times in the final result set.
If you want to eliminate duplicate rows from the final result set, you would use the UNION operator instead of UNION ALL. UNION removes duplicate rows from the combined result set.
Example:
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
-- handle duplciates
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
JOIN
JOIN is used to retrieve data from multiple tables based on a related column between them.
- When you want to combine columns from multiple tables based on a related column's values.
Duplicates are included in the result set, and the order is maintained.
Example:
SELECT column1, column2
FROM table1
INNER JOIN table2 ON table1.column_id = table2.column_id;