Questions: SQL: Set Operations (UNION, INTERSECT, EXCEPT)

Question 1

A company has two tables: active_customers and archived_customers. Some customers appear in both (reactivated users). You want a complete list of every unique customer. Which query is correct?

• A. SELECT * FROM active_customers UNION ALL SELECT * FROM archived_customers
• B. SELECT * FROM active_customers INTERSECT SELECT * FROM archived_customers
• C. SELECT * FROM active_customers UNION SELECT * FROM archived_customers
• D. SELECT * FROM active_customers EXCEPT SELECT * FROM archived_customers

Answer: (C)

UNION removes duplicate rows, so reactivated customers who appear in both tables appear only once — giving the correct unique list. UNION ALL (option A) keeps duplicates, potentially showing the same customer twice. INTERSECT (option B) returns only customers who appear in both tables (just the reactivated ones), not the full list. EXCEPT (option D) returns active customers who never appeared in the archive, dropping the reactivated customers entirely.

Question 2

Query A returns customer IDs who ordered in January. Query B returns customer IDs who ordered in February. What does 'A EXCEPT B' return?

• A. Customers who ordered in both January and February
• B. Customers who ordered in January but not in February
• C. Customers who ordered in February but not in January
• D. All customers who ordered in either month

Answer: (B)

EXCEPT returns rows from the first query (A) that do not appear in the second (B) — January customers minus those who also ordered in February. Note the asymmetry: B EXCEPT A gives the opposite (February-only customers). INTERSECT (option A) would return both-months customers; UNION (option D) would return all customers who ordered in either month.

Question 3

UNION MOST generally returns more rows than UNION when applied to the same two queries.

• T. True
• F. False

Answer: False

If the two queries return completely disjoint rows (no overlapping values), UNION and UNION ALL return the same number of rows — there is nothing to deduplicate. UNION ALL returns more rows only when duplicates actually exist. When queries have mutually exclusive WHERE conditions that guarantee disjoint results, UNION ALL is both correct and faster because it skips the deduplication step entirely.

Question 4

In a SQL set operation, ORDER BY can be placed inside each individual SELECT statement to control sort order before the rows are combined.

• T. True
• F. False

Answer: False

ORDER BY applies to the final combined result, not to individual queries in a set operation. Placing ORDER BY inside individual SELECT statements within UNION/INTERSECT/EXCEPT is either a syntax error or silently ignored, depending on the database. The correct placement is at the very end of the entire statement, after all queries and set operation keywords. The logical order of operations is: combine result sets first, then sort the final combined output.

Question 5

When is it correct to replace UNION with UNION ALL, and what is the benefit?

Model answer: Use UNION ALL when you know the two queries return disjoint rows (no duplicates possible), or when duplicates in the result are acceptable. The benefit is performance: UNION must sort or hash the entire combined result to find and eliminate duplicates — expensive on large datasets. UNION ALL simply concatenates the two result sets without any deduplication work, making it significantly faster. If WHERE clauses are mutually exclusive, UNION ALL is both correct and the more efficient choice.

Deduplication in UNION typically requires an O(n log n) sort or hash over all combined rows. For large tables this can be costly. Developers often reflexively write UNION when UNION ALL would suffice — a common source of unnecessary query slowness. The key is confirming correctness: if duplicates are possible and undesirable (as in the unique-customer scenario), you must use UNION despite the cost. But when you can guarantee disjointness from the query structure, UNION ALL is both safe and faster.