Question 1

UNION ALL keeps duplicates

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

INTERSECT returns common rows

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

UNION removes duplicates

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

EXCEPT removes rows found in the second set

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

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 6

EXCEPT with no remaining rows

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.