SQL Set Operations Interview Questions
3 questions with answers · SQL Interview Guide
UNION, UNION ALL, INTERSECT, and EXCEPT/MINUS. Combining result sets from multiple queries.
What is the difference between UNION and UNION ALL SQL syntax
UNION combines the results of two or more SELECT statements and removes duplicate rows from the final result. UNION ALL also combines results but keeps all rows including duplicates. UNION ALL is faster because it skips the duplicate-elimination step (which requires a sort or hash operation). Use UNION when you need distinct results across queries, and UNION ALL when you know there are no duplicates or when duplicates are acceptable.
-- UNION removes duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
-- UNION ALL keeps all rows (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;What is Union and Union All ? Explain the differences
UNION combines the result sets of two queries and removes duplicate rows, which requires an extra sorting or hashing step internally. UNION ALL combines the result sets without removing duplicates, so it is faster and uses less memory. You should use UNION ALL whenever you know duplicates are acceptable or impossible, because the deduplication step in UNION adds unnecessary overhead. Both queries must return the same number of columns with compatible data types.
-- UNION removes duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
-- UNION ALL keeps all rows including duplicates (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;What is Union, minus and Interact commands
UNION combines results from two queries and removes duplicates. INTERSECT returns only the rows that appear in both result sets, essentially the overlap. MINUS (called EXCEPT in SQL Server and PostgreSQL) returns rows from the first query that do not appear in the second query. All three require the same number of columns and compatible data types. INTERSECT and MINUS are less commonly supported universally, for example MySQL did not support INTERSECT or EXCEPT until version 8.0.
-- UNION: all rows from both, no duplicates
SELECT city FROM customers UNION SELECT city FROM suppliers;
-- INTERSECT: only cities in both tables
SELECT city FROM customers INTERSECT SELECT city FROM suppliers;
-- EXCEPT/MINUS: cities in customers but not in suppliers
SELECT city FROM customers EXCEPT SELECT city FROM suppliers;Knowing the answers is half the battle
The other half is explaining them clearly under pressure.
Try a free mock interviewarrow_forward