Question 1

Row count when Bob appears twice

Accepted Answer

UNION ALL concatenates the two result sets without removing duplicates. table_a has 2 rows, table_b has 2 rows - total 4 rows. Bob appears twice because he is in both tables. Prefer UNION ALL over UNION when you know there are no duplicates or you want them - it avoids the sorting/hashing overhead that UNION uses to deduplicate.

Question 2

Who makes the cut in both lists?

Accepted Answer

INTERSECT returns only rows that appear in both result sets. Bob and Carol are in both tables; Alice is only in table_a; Dave is only in table_b. Like UNION, INTERSECT removes duplicates from the result. INTERSECT ALL keeps duplicates - if Bob appeared twice in each table, he would appear twice in the result.

Question 3

What happens to the shared row?

Accepted Answer

UNION combines two result sets and removes duplicate rows. Bob appears in both tables but the result shows him only once. UNION ALL would keep both copies of Bob and return 4 rows. The two queries in a UNION must have the same number of columns and compatible data types.

Question 4

Which name survives the subtraction?

Accepted Answer

EXCEPT (MINUS in Oracle) returns rows from the first query that do not appear in the second query. Bob and Carol appear in active_users and are removed. Alice is in all_users but not in active_users, so she is returned. This is the 'find inactive users' pattern - rows in A that are not in B.

Question 5

EXCEPT when the second set fully covers the first

Accepted Answer

EXCEPT removes from the first set any row that exists in the second set. Both A101 and B202 appear in discontinued, so both are removed. The result is empty - every product in the first set was found in the second set. C303 only appears in discontinued, so it is not part of the first query and never shows up. Order matters with EXCEPT: switching the two queries would return C303, not an empty set.

Question 6

INTERSECT vs inner join

Accepted Answer

INTERSECT is the right tool when you want the overlap - rows present in both result sets. UNION would give you all emails from either table (with deduplication), not just the shared ones. EXCEPT would give you registered users who are NOT subscribers, which is the opposite of what we want. An equivalent using JOIN would be: SELECT r.email FROM registered_users r INNER JOIN newsletter_subscribers n ON r.email = n.email - both approaches work, but INTERSECT reads more clearly for this pattern.

Question 7

UNION vs UNION ALL

Accepted Answer

UNION performs a deduplication step after stacking the two result sets, similar to running DISTINCT on the combined output. UNION ALL skips that step and returns every row from both queries, even if some rows are identical. UNION ALL is almost always faster because deduplication requires sorting or hashing the combined result - prefer it when you know duplicates cannot exist or when you need to count them.

Question 8

UNION with mismatched column names

Accepted Answer

In any set operation, the result column names are taken from the first SELECT statement. The second SELECT's column names are ignored entirely - only the data values matter, not the names. Column types must still be compatible, but names do not need to match at all.

Question 9

Why does this UNION break at runtime?

Accepted Answer

UNION, UNION ALL, INTERSECT, and EXCEPT all require the same number of columns in both queries. The first query returns 2 columns (id, name); the second returns 1 column (name) - this is a mismatch and causes a syntax/runtime error. Column names do not need to match - the first query's column names are used for the result. Data types must be compatible though.

Question 10

Where does ORDER BY live in a UNION?

Accepted Answer

ORDER BY applies to the combined result of the entire UNION, not to individual queries within it. Placing ORDER BY inside one of the UNION's sub-queries is not valid in standard SQL and will cause an error in most databases. Write: SELECT ... UNION SELECT ... ORDER BY column - the ORDER BY at the end sorts the final combined result.

Question 11

Which set operators are order-sensitive?

Accepted Answer

UNION and INTERSECT produce the same result regardless of which side each query is on, because 'all rows from either' and 'rows in both' do not depend on order. EXCEPT is directional: `A EXCEPT B` removes B from A, while `B EXCEPT A` removes A from B - these are different operations. A practical consequence is that accidentally swapping the operands of EXCEPT is a silent logic bug - the query runs without error but returns wrong data.

Question 12

Swapped EXCEPT operands, same data

Accepted Answer

EXCEPT returns rows from the first set that are absent from the second set. Here the first set is opted_out, so it keeps rows in opted_out that are not in registered. bob@x.com is in both, so it is removed. dave@x.com is only in opted_out, so it survives. Flipping the order - `registered EXCEPT opted_out` - would give alice@x.com and carol@x.com, a completely different answer. Always think about which set is the 'source' and which is the 'filter' when writing EXCEPT.

Question 13

ORDER BY inside a UNION member

Accepted Answer

Standard SQL forbids ORDER BY inside an individual query that is part of a set operation. The correct pattern is a single ORDER BY clause placed after the last SELECT in the chain, which sorts the entire combined result. Some databases allow a workaround using a subquery: wrap the individual SELECT in parentheses, but even then the outer ORDER BY controls the final order.

Question 14

When is swapping UNION for UNION ALL safe?

Accepted Answer

UNION must sort or hash the combined result to find and remove duplicates, which costs extra CPU and memory. UNION ALL simply concatenates the two results without any deduplication step, making it faster. The trade-off is correctness: if duplicates exist and you do not want them, UNION ALL gives you the wrong result. A common safe case is combining two queries that select from non-overlapping date ranges or mutually exclusive filter conditions.

Question 15

INTERSECT with duplicate rows in one table

Accepted Answer

INTERSECT returns only rows that appear in both result sets and then removes duplicates from the output. X appears in both tables, so it qualifies. Even though group_a has two rows with X, the deduplication means the result contains exactly one row: X. To preserve all matching copies, use INTERSECT ALL - that would return two rows of X because group_a contributes two copies and group_b contributes one (the minimum of the two counts).

Question 16

UNION ALL on a table with repeated values

Accepted Answer

UNION ALL never removes duplicates - not within each SELECT and not across the two SELECTs. The first SELECT returns 'login' twice (both rows match), and the second SELECT also returns 'login' twice. Concatenating them gives four rows. UNION would collapse all of those into a single 'login' row.

Question 17

Compatible data types in set operations

Accepted Answer

Set operations match columns by position: the first column of each SELECT is combined, the second column of each SELECT is combined, and so on. Here, order_date (a date type) is paired with price (a decimal), which are incompatible - the database cannot determine what type the result column should be. Column names do not need to match; only the number of columns and their positional type compatibility matter.

Question 18

Rows or columns - what do they each add?

Accepted Answer

UNION combines rows from two result sets into a single column-aligned list - you get more rows, same number of columns per row. JOIN combines columns from two tables for matching rows - you get the same number of rows (approximately) but more columns per row. Both are essential but solve fundamentally different problems: UNION merges homogeneous data sets, JOIN correlates related data.

Question 19

Choosing UNION or JOIN for a real scenario

Accepted Answer

When two tables share the same column structure and you want all rows from both in one list, UNION is exactly right. It stacks the rows vertically - each row still has name and email, just sourced from different tables. A JOIN would try to match rows between the two tables using a condition, producing columns from both tables side by side - not what we want here. Use UNION ALL instead of UNION if you expect no duplicates (or want them), since UNION pays the cost of deduplication.

Question 20

Mixed set operators without parentheses

Accepted Answer

INTERSECT has higher precedence than UNION, so the engine evaluates `b INTERSECT c` first, which yields just {2}. Then it computes `a UNION {2}`, giving {1, 2}. Most people assume left-to-right evaluation and expect {1, 2, 3, 4} INTERSECT c = {2, 4}, which is wrong. Use parentheses to make intent explicit: `(a UNION b) INTERSECT c` would give {2}.

Question 21

Subquery grouping changes the result

Accepted Answer

Wrapping the UNION in a subquery forces it to run first, producing {1, 2, 3}. Then INTERSECT with c ({2, 4}) keeps only the common value: {2}. This is the result most people intuitively expect from `a UNION b INTERSECT c` - but without the parentheses the answer is different because INTERSECT binds more tightly than UNION.

Question 22

INTERSECT when both tables contain NULL

Accepted Answer

Set operations treat two NULLs as equal for the purpose of matching rows - unlike the = operator, where NULL = NULL evaluates to UNKNOWN. Because NULL appears in both t1 and t2, INTERSECT considers it a match and includes it in the output. This is one of the few places in SQL where NULL behaves like a regular value for comparison purposes, and it catches many developers off guard.

Question 23

NULL handling in EXCEPT

Accepted Answer

EXCEPT uses the same NULL-equality rule as all set operations: two NULLs are considered identical. The NULL in source matches the NULL in exclusions, so it is removed from the result. Only 10 survives because it does not appear in exclusions. This is the opposite of a WHERE v <> exclusions.v filter, where the NULL comparison would produce UNKNOWN and the NULL row would slip through.

Set Operations