In SQL, you can combine the results of multiple SELECT queries into a single result set using set operations. The two most common set operators are UNION and UNION ALL.
Rules for SQL UNION
- Each table used within UNION must have the same number of columns.
- The columns must have the same data types.
- The columns in each table must be in the same order.
UNION - Merges Results and Removes Duplicates
The UNION operator combines rows from multiple queries and removes duplicate records. This is useful when you want a unique list of values from two or more tables.
This query returns a list of all names from both employees and clients, ensuring that duplicates are removed.
Practice: Write a query that returns a list of all start_date from both sales and projects, ensuring that duplicates are removed.
UNION ALL - Merges Results and Keeps Duplicates
The UNION ALL operator also combines rows from multiple queries but keeps duplicate records. This is faster than UNION since it skips the duplicate check.
This query returns all names from both tables, including duplicates. Useful when duplicates have meaning, such as counting total entries.
Practice: Write a query that returns a list of all start_date from both sales and projects, with no duplicate removal.
FAQs
1. What is the main difference between UNION and UNION ALL in SQL?
UNION merges results from multiple queries and automatically removes duplicates.
UNION ALL merges results but keeps duplicates, making it faster because no extra duplicate-checking is done.
2. When should I use UNION instead of UNION ALL?
Use UNION when you only need a unique list of values (e.g., getting a distinct set of customer names from two tables).
Use UNION ALL when duplicates have meaning (e.g., counting how many total orders came from different sources).
3. Does UNION affect query performance compared to UNION ALL?
Yes. UNION is slower than UNION ALL because the database must scan and remove duplicates. UNION ALL is faster as it simply appends results without extra checks.
4. Can the tables in UNION have different column names?
Yes. The column names don’t have to match, but the number of columns and their data types must be the same in all SELECT statements.
5. Can I use ORDER BY with UNION or UNION ALL?
Yes, but it must be applied to the result set, not inside individual SELECT queries (unless wrapped in subqueries).
SELECT first_name AS name FROM employees UNION SELECT client_name AS name FROM clients ORDER BY name;