🎓 Velnov

🗄️ SQL · Topic 14 of 20

Set Operations — SQL Questions with Answers

30 practice questions on Set Operations, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

2 easy · 15 medium · 13 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

UNIONSet operationsUNION ALLPerformanceCompatibilityORDER BYINTERSECTOverlapEXCEPTMINUSSet differenceDirectionColumn namesLiteralsPrecedenceChainingUNION vs JOINErrorsAnti-joinDuplicatesNULLVALUESType compatibilityCAST

Jump to: Easy · Medium · Hard

Easy questions (2)

Q1. easy

What does UNION do?

SELECT city FROM customers
UNION
SELECT city FROM suppliers;
  1. A Combines the rows of two queries into one result, REMOVING duplicates
  2. B Joins two tables side by side
  3. C Multiplies rows
  4. D Filters rows

Answer: A — Combines the rows of two queries into one result, REMOVING duplicates

UNION stacks result sets vertically (adding rows) and removes duplicates. JOIN, by contrast, combines tables horizontally (adding columns).

UNIONSet operations

Q2. easy

What is the difference between UNION and UNION ALL?

  1. A UNION removes duplicates; UNION ALL keeps ALL rows including duplicates (and is faster)
  2. B They are identical
  3. C UNION ALL removes duplicates
  4. D UNION is faster

Answer: A — UNION removes duplicates; UNION ALL keeps ALL rows including duplicates (and is faster)

UNION does an extra de-duplication pass, which costs time. If you know there are no duplicates (or want them), UNION ALL is faster and preferred.

UNION ALLPerformance

Medium questions (15)

Q3. medium

What must be true for two queries to be combined with UNION?

  1. A They must have the SAME number of columns, in compatible data types and order
  2. B They must query the same table
  3. C They must have the same column names
  4. D Nothing

Answer: A — They must have the SAME number of columns, in compatible data types and order

UNION aligns columns by POSITION, not name. Column 1 of both queries must be type-compatible, and so on. The result uses the first query's column names.

UNIONCompatibility

Q5. medium

What does INTERSECT return?

SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter_subscribers;
  1. A Only rows that appear in BOTH queries
  2. B All rows from both
  3. C Rows in the first but not the second
  4. D Nothing

Answer: A — Only rows that appear in BOTH queries

INTERSECT is the overlap - customers who are also suppliers, for example. Like UNION, it removes duplicates by default.

INTERSECTOverlap

Q6. medium

What does EXCEPT (or MINUS in Oracle) return?

SELECT email FROM customers
EXCEPT
SELECT email FROM unsubscribed;
  1. A Rows in the FIRST query that are NOT in the second
  2. B Rows in both
  3. C All rows
  4. D Rows in the second only

Answer: A — Rows in the FIRST query that are NOT in the second

EXCEPT is set difference. Customers who never subscribed = customers EXCEPT subscribers. Oracle spells it MINUS; the meaning is identical.

EXCEPTMINUSSet difference

Q8. medium

How does UNION handle the column NAMES of the result?

  1. A It uses the column names (or aliases) from the FIRST SELECT
  2. B It combines both names
  3. C It uses the second query's names
  4. D Columns become unnamed

Answer: A — It uses the column names (or aliases) from the FIRST SELECT

Only the first query's names (or aliases) appear in the output. Alias the first query's columns to control the result headers.

UNIONColumn names

Q10. medium

How do you tag which query each row came from in a UNION?

  1. A Add a literal column: SELECT name, 'A' AS source ... UNION ALL SELECT name, 'B' ...
  2. B It is automatic
  3. C Use GROUP BY
  4. D It cannot be done

Answer: A — Add a literal column: SELECT name, 'A' AS source ... UNION ALL SELECT name, 'B' ...

A constant column labels the origin of each row - very useful when merging similar data from different sources into one list.

UNIONLiterals

Q12. medium

Can you UNION more than two queries?

  1. A Yes - chain them: SELECT ... UNION SELECT ... UNION SELECT ...
  2. B No, only two
  3. C Only with ALL
  4. D Only three

Answer: A — Yes - chain them: SELECT ... UNION SELECT ... UNION SELECT ...

Any number of compatible queries can be unioned. Each must have the same column count and compatible types.

UNIONChaining

Q14. medium

What happens if the two queries have different column COUNTS?

  1. A An error - 'each UNION query must have the same number of columns'
  2. B Missing columns become NULL
  3. C Extra columns are dropped
  4. D It works anyway

Answer: A — An error - 'each UNION query must have the same number of columns'

The counts must match exactly. If one query lacks a column, add a placeholder like NULL AS missing_col or a constant to line them up.

UNIONErrors

Q16. medium

Does INTERSECT remove duplicates?

  1. A Yes - like UNION, it returns distinct rows (INTERSECT ALL keeps duplicates where supported)
  2. B No, it keeps all
  3. C Only for text
  4. D It errors on duplicates

Answer: A — Yes - like UNION, it returns distinct rows (INTERSECT ALL keeps duplicates where supported)

The set operators default to distinct results. Some databases offer INTERSECT ALL and EXCEPT ALL to preserve duplicate multiplicities.

INTERSECTDuplicates

Q18. medium

What is a practical use of UNION ALL with literals?

SELECT * FROM (VALUES ('S'),('M'),('L')) AS sizes(label);
  1. A Building a small fixed list of values inline without creating a table
  2. B Deleting rows
  3. C Indexing
  4. D Sorting

Answer: A — Building a small fixed list of values inline without creating a table

SELECT 'S' UNION ALL SELECT 'M' UNION ALL SELECT 'L' creates a quick lookup set. (Modern SQL also offers VALUES lists for this.)

UNION ALLVALUES

Q20. medium

How do you combine two monthly report queries into one result?

  1. A UNION ALL the two SELECTs (rows are distinct by month, so no dedup needed)
  2. B JOIN them
  3. C INTERSECT them
  4. D CROSS JOIN them

Answer: A — UNION ALL the two SELECTs (rows are distinct by month, so no dedup needed)

When each query returns non-overlapping rows, UNION ALL simply stacks them efficiently. Add a month label column to distinguish the segments.

UNION ALLReporting

Q22. medium

Can you apply LIMIT to a UNION result?

  1. A Yes - a LIMIT (with ORDER BY) at the end applies to the combined result
  2. B No, LIMIT is forbidden
  3. C Only in each SELECT
  4. D Only with INTERSECT

Answer: A — Yes - a LIMIT (with ORDER BY) at the end applies to the combined result

Place ORDER BY and LIMIT after the final query to page the whole union. To limit an individual branch, wrap that SELECT in a subquery.

UNIONLIMIT

Q24. medium

Do the queries in a UNION need matching WHERE clauses?

  1. A No - each SELECT can have completely different logic, as long as the output columns line up
  2. B Yes, identical WHERE
  3. C They must query one table
  4. D WHERE is not allowed

Answer: A — No - each SELECT can have completely different logic, as long as the output columns line up

The branches are independent queries; only their output shape must match. This flexibility lets you merge quite different selections into one list.

UNIONFlexibility

Q26. medium

How do you deduplicate a combined list of tags from two tables?

  1. A SELECT tag FROM a UNION SELECT tag FROM b (UNION removes duplicates)
  2. B UNION ALL
  3. C JOIN a and b
  4. D CROSS JOIN

Answer: A — SELECT tag FROM a UNION SELECT tag FROM b (UNION removes duplicates)

Plain UNION is exactly the tool for a distinct merged list. Use UNION ALL only if you deliberately want to keep the repeats.

UNIONDeduplication

Q28. medium

Which set operation answers 'who is in both group A and group B'?

  1. A INTERSECT
  2. B UNION
  3. C EXCEPT
  4. D CROSS JOIN

Answer: A — INTERSECT

INTERSECT returns the common membership. UNION would give everyone in either group, and EXCEPT gives those in one but not the other.

INTERSECTMembership

Q30. medium

Which set operation combines two result sets and keeps duplicates for maximum speed?

  1. A UNION ALL
  2. B UNION
  3. C INTERSECT
  4. D EXCEPT

Answer: A — UNION ALL

UNION ALL simply appends the second result to the first with no de-duplication pass, so it is the fastest way to stack rows. Use plain UNION only when you actually need to remove duplicates.

UNION ALLPerformance

Hard questions (13)

Q4. hard

Where does ORDER BY go in a UNION query?

SELECT name, 'customer' AS type FROM customers
UNION ALL
SELECT name, 'supplier' FROM suppliers
ORDER BY name;
  1. A At the very END, after the last SELECT - it sorts the combined result
  2. B In each SELECT
  3. C Before UNION
  4. D ORDER BY is not allowed

Answer: A — At the very END, after the last SELECT - it sorts the combined result

A single ORDER BY at the end applies to the whole union. Putting it inside an individual SELECT is either an error or ignored, since the union reorders everything anyway.

UNIONORDER BY

Q7. hard

Is EXCEPT symmetric - does A EXCEPT B equal B EXCEPT A?

  1. A No - order matters; A EXCEPT B keeps A's extras, B EXCEPT A keeps B's extras
  2. B Yes, always symmetric
  3. C Only for numbers
  4. D They both return everything

Answer: A — No - order matters; A EXCEPT B keeps A's extras, B EXCEPT A keeps B's extras

Set difference is directional, unlike UNION and INTERSECT which are symmetric. Choose the order based on which side's unmatched rows you want.

EXCEPTDirection

Q9. hard

Why might UNION ALL be preferred even when duplicates are possible?

  1. A It avoids the costly de-duplication sort; if downstream logic handles duplicates, it is faster
  2. B It is always wrong
  3. C It removes more duplicates
  4. D It sorts automatically

Answer: A — It avoids the costly de-duplication sort; if downstream logic handles duplicates, it is faster

On large data the distinct pass of UNION is expensive. When you know rows are unique (e.g. non-overlapping date ranges), UNION ALL gives the same result faster.

UNION ALLPerformance

Q11. hard

What is the precedence between INTERSECT and UNION when combined?

  1. A INTERSECT binds tighter than UNION/EXCEPT - use parentheses to be explicit
  2. B UNION binds tighter
  3. C They are equal, left to right
  4. D You cannot combine them

Answer: A — INTERSECT binds tighter than UNION/EXCEPT - use parentheses to be explicit

In standard SQL INTERSECT has higher precedence, so A UNION B INTERSECT C means A UNION (B INTERSECT C). Parentheses remove any doubt and improve readability.

PrecedenceSet operations

Q13. hard

When would you use UNION instead of a JOIN?

  1. A When STACKING similar rows from different sources (adding rows), not correlating tables (adding columns)
  2. B They are interchangeable
  3. C UNION is always better
  4. D Never use UNION

Answer: A — When STACKING similar rows from different sources (adding rows), not correlating tables (adding columns)

JOIN answers 'what related data goes with this row?'. UNION answers 'combine these two lists into one'. Choosing the right one starts with whether you need more columns or more rows.

UNION vs JOIN

Q15. hard

How do you find values in table A that are NOT in table B, using set operations?

  1. A SELECT x FROM A EXCEPT SELECT x FROM B
  2. B A INTERSECT B
  3. C A UNION B
  4. D A JOIN B

Answer: A — SELECT x FROM A EXCEPT SELECT x FROM B

EXCEPT is the direct set-difference tool. Alternatives include LEFT JOIN ... IS NULL and NOT EXISTS, which can be faster and handle multiple columns more flexibly.

EXCEPTAnti-join

Q17. hard

How do set operations treat NULLs when deciding if rows are equal?

  1. A They treat two NULLs as the SAME (unlike = comparisons), so duplicate NULL rows collapse
  2. B NULLs are always different
  3. C NULLs are removed
  4. D It errors

Answer: A — They treat two NULLs as the SAME (unlike = comparisons), so duplicate NULL rows collapse

Set operations use 'not distinct' semantics: NULL matches NULL for de-duplication and intersection. This differs from the WHERE clause, where NULL = NULL is UNKNOWN.

NULLSet operations

Q19. hard

Why does data-type compatibility matter in UNION?

  1. A Corresponding columns are merged, so mixing e.g. a number and a date causes an error or implicit conversion
  2. B It does not matter
  3. C Types are ignored
  4. D Only names matter

Answer: A — Corresponding columns are merged, so mixing e.g. a number and a date causes an error or implicit conversion

Each output column takes a single type derived from both inputs. Incompatible types (text vs date) either fail or coerce unexpectedly - align them with CAST.

Type compatibilityCAST

Q21. hard

What is the row count of 'SELECT 1 UNION SELECT 1 UNION ALL SELECT 1'?

  1. A 2 - the first UNION dedups to one, then UNION ALL adds another
  2. B 3
  3. C 1
  4. D An error

Answer: A — 2 - the first UNION dedups to one, then UNION ALL adds another

Evaluated left to right: SELECT 1 UNION SELECT 1 gives one row, then UNION ALL SELECT 1 appends a duplicate, giving two. Mixing UNION and UNION ALL requires care.

UNIONUNION ALL

Q23. hard

How would you find emails common to two lists AND unique to the first, in one query set?

  1. A Use INTERSECT for the common ones and EXCEPT for the unique ones (as separate queries or combined with care)
  2. B One UNION does it
  3. C A single JOIN
  4. D It is impossible

Answer: A — Use INTERSECT for the common ones and EXCEPT for the unique ones (as separate queries or combined with care)

INTERSECT gives the overlap; EXCEPT gives list-one-only. Set operators are a clean vocabulary for these membership questions, complementing joins.

INTERSECTEXCEPT

Q25. hard

What is a performance advantage of EXCEPT/INTERSECT over equivalent joins?

  1. A They express set logic concisely and can be optimised well - though NOT EXISTS/LEFT JOIN are sometimes faster on large indexed data
  2. B They are always fastest
  3. C They never use indexes
  4. D There is no difference ever

Answer: A — They express set logic concisely and can be optimised well - though NOT EXISTS/LEFT JOIN are sometimes faster on large indexed data

Set operators are readable for whole-row membership tests. For large tables, benchmark against NOT EXISTS and anti-joins, which can leverage indexes more directly.

PerformanceEXCEPT

Q27. hard

Why must you sometimes wrap a UNION in a subquery?

  1. A To apply further operations (GROUP BY, JOIN, additional filtering) to the combined result as a whole
  2. B UNION cannot be used otherwise
  3. C For syntax reasons only
  4. D You never need to

Answer: A — To apply further operations (GROUP BY, JOIN, additional filtering) to the combined result as a whole

A UNION is a single result set; to aggregate or join it, place it in a subquery or CTE: SELECT type, COUNT(*) FROM (SELECT ... UNION ALL ...) t GROUP BY type.

SubqueriesUNION

Q29. hard

What is the safest way to ensure UNION columns align correctly?

  1. A Explicitly list and, if needed, CAST each column in both queries in the same order
  2. B Use SELECT * in both
  3. C Rely on column names matching
  4. D Add extra columns

Answer: A — Explicitly list and, if needed, CAST each column in both queries in the same order

SELECT * across differently structured tables is fragile - column order or count can drift. Naming (and casting) columns makes the alignment explicit and change-proof.

UNIONBest practices

Practice “Set Operations” as a quiz