MySQL UNION vs UNION ALL, INTERSECT and EXCEPT

Answers — UNION and set thinking

1. Easy. Make one list of all customer names and all product names in a single column called name. Target: 12 rows: 6 customers and 6 products

   SELECT customer_name AS name FROM customers UNION ALL SELECT product_name FROM products;

2. Easy. List the cities of customers 1–3 and of customers 4–6 as one de-duplicated list, A to Z. Target: 4 rows: Delhi, Jaipur, Mumbai, Pune

   SELECT city FROM customers WHERE customer_id <= 3 UNION SELECT city FROM customers WHERE customer_id > 3 ORDER BY city;

3. Easy. Same as the previous problem, but keep the duplicates. Target: 6 rows: Pune and Mumbai appear twice

   SELECT city FROM customers WHERE customer_id <= 3 UNION ALL SELECT city FROM customers WHERE customer_id > 3 ORDER BY city;

4. Easy. List customers and products together with a kind column saying 'customer' or 'product'. Target: 12 rows; kind is customer for 6 rows and product for 6

   SELECT 'customer' AS kind, customer_name AS name FROM customers UNION ALL SELECT 'product', product_name FROM products ORDER BY kind, name;

5. Medium. Using UNION ALL, list pending orders and cancelled orders, in order_id order. Target: 2 rows: 104 cancelled, 107 pending

   SELECT order_id, status FROM orders WHERE status = 'pending' UNION ALL SELECT order_id, status FROM orders WHERE status = 'cancelled' ORDER BY order_id;

6. Medium. List every customer_id that appears in orders, plus every customer from Jaipur, without duplicates. Target: 6 rows: 1, 2, 3, 4, 5, 6

   SELECT customer_id FROM orders UNION SELECT customer_id FROM customers WHERE city = 'Jaipur' ORDER BY customer_id;

7. Medium. Combine Electronics products costing 1000 or more with Stationery products costing under 50. Target: 2 rows: Keyboard 1200.00, Pen 20.00

   SELECT product_name, price FROM products WHERE category = 'Electronics' AND price >= 1000 UNION ALL SELECT product_name, price FROM products WHERE category = 'Stationery' AND price < 50 ORDER BY price DESC;

8. Medium. Order count per status, followed by a TOTAL row as the last row. Target: 4 rows: cancelled 1, completed 6, pending 1, TOTAL 8

   SELECT label, orders FROM (SELECT status AS label, COUNT(*) AS orders FROM orders GROUP BY status UNION ALL SELECT 'TOTAL', COUNT(*) FROM orders) AS r ORDER BY label = 'TOTAL', label;

9. Medium. Find customer ids that have a completed order AND live in Pune, using INTERSECT. Target: 1 row: 1 (Asha) — Charu's only order was cancelled

   SELECT customer_id FROM orders WHERE status = 'completed' INTERSECT SELECT customer_id FROM customers WHERE city = 'Pune';

10. Hard. Find customers who never placed an order, using EXCEPT. Target: 1 row: 6

    SELECT customer_id FROM customers EXCEPT SELECT customer_id FROM orders ORDER BY customer_id;

11. Hard. Which products were sold in completed orders but never in January? Use EXCEPT on product_id. Target: 1 row: 5 (Keyboard)

    SELECT i.product_id FROM order_items i JOIN orders o ON o.order_id = i.order_id WHERE o.status = 'completed' EXCEPT SELECT i.product_id FROM order_items i JOIN orders o ON o.order_id = i.order_id WHERE o.order_date < '2026-02-01' ORDER BY product_id;

12. Hard. Emulate a FULL OUTER JOIN between customers and orders (customer_id, customer_name, order_id). Target: 9 rows; Farah appears with order_id NULL

    SELECT c.customer_id, c.customer_name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id UNION SELECT c.customer_id, c.customer_name, o.order_id FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id ORDER BY customer_id, order_id;

13. Hard. Run a UNION whose two SELECTs return 2 and 1 columns, and record the error number. Target: Error 1222: different number of columns

    SELECT customer_id, customer_name FROM customers UNION SELECT product_id FROM products;

14. Hard. Build a timeline: completed orders as 'sale' and cancelled orders as 'refund', with their dates, oldest first. Target: 7 rows; 101 sale first, 104 refund on 2026-02-10, 108 sale last

    SELECT order_date, order_id, 'sale' AS event FROM orders WHERE status = 'completed' UNION ALL SELECT order_date, order_id, 'refund' FROM orders WHERE status = 'cancelled' ORDER BY order_date, order_id;

15. Professional. Finance wants completed revenue per month plus a TOTAL row that is calculated separately. Target: 4 rows: 2026-01 1600.00, 2026-02 1950.00, 2026-03 2600.00, TOTAL 6150.00

    SELECT period, revenue FROM (SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS period, SUM(i.quantity * i.unit_price) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed' GROUP BY period UNION ALL SELECT 'TOTAL', SUM(i.quantity * i.unit_price) FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.status = 'completed') AS r ORDER BY period = 'TOTAL', period;

16. Professional. One data-quality list: customers with no email and customers with no orders, each with an issue column. Target: 2 rows: Charu — missing email; Farah — no orders

    SELECT customer_name, 'missing email' AS issue FROM customers WHERE email IS NULL UNION ALL SELECT c.customer_name, 'no orders' FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) ORDER BY customer_name;