Chapter 14 Intermediate 49 Questions

Practice Questions — UNION, UNION ALL, INTERSECT, EXCEPT

← Back to Notes
12 Easy
11 Medium
4 Hard

Topic-Specific Questions

Question 1
Easy
What is the key difference between UNION and UNION ALL?
Duplicates.
UNION removes duplicate rows (costs a sort/hash). UNION ALL keeps all rows and is faster.
Question 2
Easy
Given customers_in (4 rows) and customers_uk (3 rows, 1 duplicate with _in), how many rows does UNION return?
Deduplicated.
6 rows
Question 3
Easy
Same data, UNION ALL instead. How many rows?
No dedup.
7 rows
Question 4
Easy
Does MySQL always support INTERSECT?
Version matters.
Only MySQL 8.0.31 and later. Older versions (and MariaDB) require workarounds like INNER JOIN or IN.
Question 5
Easy
Where can you place ORDER BY in a UNION?
Once, at the end.
Only at the very end of the entire UNION statement. It sorts the combined result.
Question 6
Easy
What error do you get if column counts differ?
MySQL error 1222.
ERROR 1222: The used SELECT statements have a different number of columns
Question 7
Easy
What column names does this produce?
SELECT a AS x, b FROM t1
UNION
SELECT c, d FROM t2;
First query wins.
x, b (from the first SELECT). The second query's column names are ignored.
Question 8
Medium
Given active users (1,2,3) and purchased users (2,3,4), what does INTERSECT return?
Common users.
2 and 3
Question 9
Medium
Same data — what does active EXCEPT purchased return?
In active, not in purchased.
User 1
Question 10
Medium
Write the MySQL workaround (INNER JOIN) for INTERSECT of active and purchased users by email.
JOIN on email + DISTINCT.
SELECT DISTINCT a.email FROM users_active a
JOIN users_purchased p ON a.email = p.email;
Question 11
Medium
Write the MySQL workaround (NOT EXISTS) for 'active EXCEPT purchased'.
NOT EXISTS with correlation.
SELECT a.email FROM users_active a
WHERE NOT EXISTS (
    SELECT 1 FROM users_purchased p WHERE p.email = a.email
);
Question 12
Medium
Why is UNION slower than UNION ALL on large datasets?
Deduplication cost.
UNION must sort or hash the combined result to identify duplicates. UNION ALL just streams both sets through without additional work.
Question 13
Medium
What does this produce if t1 has rows {1,2} and t2 has {2,3,4}?
SELECT x FROM t1 UNION SELECT x FROM t2;
Distinct union.
{1, 2, 3, 4}
Question 14
Hard
What happens with this query?
(SELECT name FROM a ORDER BY name LIMIT 5)
UNION ALL
(SELECT name FROM b ORDER BY name LIMIT 5);
Parentheses + LIMIT trick.
Takes top 5 sorted names from a, top 5 from b, and concatenates them. Per-branch ORDER BY + LIMIT is allowed inside parentheses.
Question 15
Hard
Tag the source of each row in a UNION. Why and how?
Constant column.
SELECT name, email, 'India' AS source FROM customers_in
UNION ALL
SELECT name, email, 'UK'    AS source FROM customers_uk;

Mixed & Application Questions

Question 1
Easy
Which rule does UNION NOT enforce?
Three rules: column count, types, and first query naming.
UNION does NOT require column names to match between branches. Only column count and compatible types matter.
Question 2
Easy
What is Oracle's name for EXCEPT?
Arithmetic sign.
MINUS
Question 3
Easy
Give a real-world use case for UNION ALL.
Partitioned tables.
Combining yearly partitioned sales tables (sales_2024, sales_2025, sales_2026) into a unified report. No duplicates by construction, so UNION ALL is faster.
Question 4
Easy
Is the following valid?
SELECT id, name FROM t1 UNION SELECT id FROM t2;
Column count.
No. Column counts differ (2 vs 1). MySQL returns ERROR 1222.
Question 5
Easy
Which is preferred for 'active users who have not yet purchased' in MySQL 5.7?
NOT EXISTS or LEFT JOIN.
NOT EXISTS or LEFT JOIN + IS NULL — MySQL 5.7 lacks EXCEPT.
Question 6
Medium
Write a UNION query returning all unique cities from two tables customers_in and customers_uk (assume both have a 'city' column).
UNION.
SELECT city FROM customers_in
UNION
SELECT city FROM customers_uk
ORDER BY city;
Question 7
Medium
Write a UNION ALL query tagging each customer with their origin country.
Constant column.
SELECT name, email, 'India' AS country FROM customers_in
UNION ALL
SELECT name, email, 'UK' FROM customers_uk;
Question 8
Medium
Given 3 yearly sales tables, write a query that returns total amount per year.
UNION ALL with constant year, then GROUP BY.
SELECT year, SUM(amount) AS total FROM (
    SELECT amount, 2024 AS year FROM sales_2024
    UNION ALL
    SELECT amount, 2025 FROM sales_2025
    UNION ALL
    SELECT amount, 2026 FROM sales_2026
) t
GROUP BY year;
Question 9
Medium
When would UNION produce the same result as UNION ALL?
When duplicates cannot occur.
When the two SELECTs cannot produce overlapping rows. For example, disjoint WHERE filters on the same table, or unrelated tables with unique IDs.
Question 10
Medium
Why can't you put a separate ORDER BY inside each UNION branch?
Set semantics.
Because UNION treats the combined result as one set. Per-branch ordering would be meaningless — the final order is determined at the end. To order within a branch, wrap it in parentheses with LIMIT: (SELECT ... ORDER BY ... LIMIT N) UNION (SELECT ...).
Question 11
Hard
Emulate FULL OUTER JOIN between customers_in and customers_uk on email using UNION.
LEFT JOIN UNION RIGHT JOIN.
SELECT i.name AS in_name, u.name AS uk_name
FROM customers_in i
LEFT JOIN customers_uk u ON i.email = u.email
UNION
SELECT i.name, u.name
FROM customers_in i
RIGHT JOIN customers_uk u ON i.email = u.email;
Question 12
Hard
Find emails in BOTH customers_in and customers_uk, portable across older MySQL.
INTERSECT workaround.
SELECT DISTINCT i.email FROM customers_in i
JOIN customers_uk u ON i.email = u.email;

Multiple Choice Questions

MCQ 1
What does UNION do?
  • A. Combines and keeps all rows including duplicates
  • B. Combines and removes duplicate rows
  • C. Joins tables side by side
  • D. Returns only common rows
Answer: B
B is correct. UNION deduplicates; UNION ALL keeps duplicates; INTERSECT returns common rows.
MCQ 2
Which is faster on large datasets?
  • A. UNION
  • B. UNION ALL
  • C. Both are equally fast
  • D. It depends on the database
Answer: B
B is correct. UNION ALL avoids the sort/hash dedup step, so it is significantly faster. Use UNION only when you actually need deduplication.
MCQ 3
What must be true of queries combined with UNION?
  • A. They must be from the same table
  • B. They must have the same number of columns with compatible types
  • C. They must have identical column names
  • D. They cannot use WHERE clauses
Answer: B
B is correct. UNION requires matching column count and compatible types. Column names come from the first SELECT; they do not need to match.
MCQ 4
Where do final column names come from in a UNION?
  • A. The second SELECT
  • B. The first SELECT
  • C. Automatically generated
  • D. Must be specified after UNION
Answer: B
B is correct. Column names (including aliases) come from the first SELECT. Put your desired aliases there.
MCQ 5
Which statement lists rows present in the first query but NOT in the second?
  • A. INTERSECT
  • B. UNION
  • C. EXCEPT / MINUS
  • D. JOIN
Answer: C
C is correct. EXCEPT (or MINUS in Oracle) returns rows in the first result set that are not in the second.
MCQ 6
Does MySQL 5.7 support INTERSECT and EXCEPT natively?
  • A. Yes, always
  • B. No. Only MySQL 8.0.31+ supports them. Older versions need workarounds.
  • C. Only INTERSECT is supported
  • D. Only EXCEPT is supported
Answer: B
B is correct. MySQL added INTERSECT and EXCEPT in 8.0.31 (late 2022). MariaDB also requires recent versions. Use INNER JOIN or NOT EXISTS as workarounds.
MCQ 7
Where should ORDER BY appear in a UNION?
  • A. After each SELECT
  • B. Only in the first SELECT
  • C. At the very end, applying to the combined result
  • D. ORDER BY is not allowed
Answer: C
C is correct. ORDER BY at the end sorts the combined rows. Per-branch ordering requires parentheses with a LIMIT.
MCQ 8
The MySQL equivalent of INTERSECT (for older versions) is:
  • A. LEFT JOIN + IS NULL
  • B. INNER JOIN + DISTINCT
  • C. NOT EXISTS
  • D. UNION ALL
Answer: B
B is correct. INNER JOIN with DISTINCT keeps only rows present on both sides. A corresponds to EXCEPT; C also corresponds to EXCEPT.
MCQ 9
Given sets A={1,2,3} and B={2,3,4}, what does A EXCEPT B return?
  • A. {1,2,3,4}
  • B. {2,3}
  • C. {1}
  • D. {4}
Answer: C
C is correct. EXCEPT returns elements in A not in B. Only 1 qualifies.
MCQ 10
What is a common pitfall when emulating EXCEPT with NOT IN?
  • A. NOT IN is slower
  • B. If the subquery returns NULL, NOT IN fails silently and returns no rows
  • C. NOT IN does not work with VARCHAR
  • D. NOT IN is deprecated
Answer: B
B is correct. The NULL trap from the subqueries chapter strikes again. Use NOT EXISTS or filter NULLs out of the subquery.
MCQ 11
What is the result of this query if t1 has 5 rows and t2 has 5 rows with 2 exact duplicates?
SELECT * FROM t1 UNION ALL SELECT * FROM t2;
  • A. 5 rows
  • B. 8 rows
  • C. 10 rows
  • D. 2 rows
Answer: C
C is correct. UNION ALL concatenates: 5 + 5 = 10 rows. Duplicates are kept. UNION (without ALL) would return 8.
MCQ 12
You want the top 3 cheapest products from each of two warehouses combined. What is the correct pattern?
  • A. SELECT * FROM warehouse1 UNION SELECT * FROM warehouse2 ORDER BY price LIMIT 3
  • B. (SELECT * FROM warehouse1 ORDER BY price LIMIT 3) UNION ALL (SELECT * FROM warehouse2 ORDER BY price LIMIT 3)
  • C. SELECT TOP 3 * FROM warehouse1 UNION SELECT TOP 3 * FROM warehouse2
  • D. Not possible in MySQL
Answer: B
B is correct. Wrap each branch in parentheses with ORDER BY and LIMIT. A would sort and limit the combined result, not each branch.
MCQ 13
Which scenario justifies using UNION instead of UNION ALL?
  • A. Combining disjoint date ranges
  • B. Combining two tables where a row could appear in both and you need only one copy
  • C. Concatenating log entries
  • D. Any multi-table query
Answer: B
B is correct. UNION is appropriate when duplicates are possible AND you want them removed. Otherwise, UNION ALL is faster.
MCQ 14
Which pattern correctly emulates EXCEPT in MySQL 5.7?
  • A. SELECT * FROM a EXCEPT SELECT * FROM b (works in 5.7)
  • B. SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.key = a.key)
  • C. SELECT * FROM a NOT IN SELECT * FROM b
  • D. SELECT * FROM a CROSS JOIN b
Answer: B
B is correct. MySQL 5.7 does not support EXCEPT (A fails). C is not valid syntax. D is cartesian. NOT EXISTS is the portable, NULL-safe pattern.

Coding Challenges

Challenge 1: Distinct Customers Across Two Regions

Easy
Combine customers_in and customers_uk into a single deduplicated list of (name, email).
SELECT name, email FROM customers_in
UNION
SELECT name, email FROM customers_uk
ORDER BY name;

Challenge 2: Tagged Combined List

Easy
Combine both customer tables with a 'country' tag. Keep duplicates (dual accounts are interesting data).
SELECT name, email, 'India' AS country FROM customers_in
UNION ALL
SELECT name, email, 'UK' AS country FROM customers_uk
ORDER BY name;

Challenge 3: Common Emails (INTERSECT Workaround)

Medium
Find emails that exist in BOTH customers_in and customers_uk. Use a workaround that works in all MySQL versions.
SELECT DISTINCT i.email
FROM customers_in i
JOIN customers_uk u ON i.email = u.email;

Challenge 4: Active Users Who Have Not Purchased (EXCEPT Workaround)

Medium
From users_active, return users who are NOT in users_purchased. Use NOT EXISTS.
SELECT a.user_id, a.email
FROM users_active a
WHERE NOT EXISTS (
    SELECT 1 FROM users_purchased p WHERE p.email = a.email
);

Challenge 5: Yearly Sales Consolidation

Medium
Combine sales_2024, sales_2025, sales_2026 into one result with a year tag, sorted by year then id.
SELECT id, amount, 2024 AS year FROM sales_2024
UNION ALL
SELECT id, amount, 2025 FROM sales_2025
UNION ALL
SELECT id, amount, 2026 FROM sales_2026
ORDER BY year, id;

Challenge 6: Totals Per Year From Combined Sales

Hard
Using UNION ALL and GROUP BY, return total sales per year across the three yearly tables.
SELECT year, SUM(amount) AS total
FROM (
    SELECT amount, 2024 AS year FROM sales_2024
    UNION ALL
    SELECT amount, 2025 FROM sales_2025
    UNION ALL
    SELECT amount, 2026 FROM sales_2026
) t
GROUP BY year
ORDER BY year;

Challenge 7: Emulate FULL OUTER JOIN Between Two Customer Tables

Hard
Return every customer from India and UK, matched by email where possible, NULL on the side where there is no match.
SELECT i.email AS in_email, i.name AS in_name, u.email AS uk_email, u.name AS uk_name
FROM customers_in i
LEFT JOIN customers_uk u ON i.email = u.email
UNION
SELECT i.email, i.name, u.email, u.name
FROM customers_in i
RIGHT JOIN customers_uk u ON i.email = u.email;

Challenge 8: Top 2 From Each Country

Hard
Return the first 2 customers (by id) from each country as a combined list, tagged by country.
(SELECT id, name, 'India' AS country FROM customers_in ORDER BY id LIMIT 2)
UNION ALL
(SELECT id, name, 'UK' FROM customers_uk ORDER BY id LIMIT 2);

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass