SQL UNION, INTERSECT and EXCEPT: Combine Query Results

Before Diwali, Malwa Mart's marketing intern is asked to send a greeting SMS to everyone who shopped at the Vijay Nagar or Palasia branch in March. Each branch emails her its own customer list.

She pastes the two lists one below the other. Asha Verma shops at both branches, so her name now appears twice, and she would get two identical messages. The intern deletes the repeat before sending.

The next day the owner has two more questions. "Who shops at both branches?" and "Who shops at Vijay Nagar but has never visited Palasia?" Both are answered by comparing the two lists, not by adding them.

Pasting the lists together and removing repeats is UNION. Pasting them and keeping repeats is UNION ALL. Names on both lists is INTERSECT. Names on the first list only is EXCEPT. Each list is the result of one SELECT query.

UNION, INTERSECT and EXCEPT are SQL set operators that combine the results of two SELECT statements into one result, by stacking all rows, keeping only shared rows, or keeping rows found only in the first query.

These operators are different from SQL joins and from the self and cross joins in the previous article. A join adds columns side by side from related tables. A set operator adds or compares rows from two queries that have the same shape.

How does SQL UNION work?

UNION runs two queries, places the second result under the first, and removes duplicate rows. The column names in the final result come from the first query.

SELECT customer_id FROM orders WHERE store_id = 1   -- Vijay Nagar
UNION
SELECT customer_id FROM orders WHERE store_id = 2;  -- Palasia

A set operator grows the result downwards with more rows of the same columns. A join grows it sideways with more columns.

The rules every UNION must follow

1. Both SELECTs return the same number of columns.
2. Columns are matched by position, not by name. The first column of query two goes under the first column of query one.
3. Matching columns need compatible types. PostgreSQL rejects text under an integer column; SQLite and MySQL quietly convert.
4. ORDER BY and LIMIT go once, at the very end, and sort the whole combined result.

Rule 2 trips people up most. If you write SELECT name, city on top and SELECT city, name below, the database happily stacks cities under names without any error.

UNION vs UNION ALL: what is the difference?

UNION removes duplicate rows, which means the database must compare every row with the others, usually by sorting or hashing. UNION ALL skips that step and simply appends.

In the Malwa Mart data, Vijay Nagar had three orders from customers 1, 1 and 6. Palasia had three orders from customers 2, 4 and 1. UNION ALL returns all six values. UNION returns four distinct customers.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE orders (order_id INT, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT);
INSERT INTO orders VALUES
 (101,1,1,'2026-03-01',1040,'UPI'), (102,2,2,'2026-03-01',330,'Cash'), (103,1,1,'2026-03-03',275,'UPI'),
 (104,3,3,'2026-03-04',180,'Card'), (105,4,2,'2026-03-05',680,'UPI'), (106,2,4,'2026-03-07',120,'Cash'),
 (107,5,3,'2026-03-08',1560,'Card'), (108,6,1,'2026-03-08',220,'UPI'), (109,4,4,'2026-03-10',340,NULL),
 (110,1,2,'2026-03-12',465,'Cash'), (111,6,3,'2026-03-14',NULL,'UPI');
""")

vijay = "SELECT customer_id FROM orders WHERE store_id = 1"
palasia = "SELECT customer_id FROM orders WHERE store_id = 2"
for op in ["UNION ALL", "UNION", "INTERSECT", "EXCEPT"]:
    ids = [r[0] for r in con.execute(f"{vijay} {op} {palasia} ORDER BY 1")]
    print(f"{op:9} -> {ids}")

ids = [r[0] for r in con.execute(f"{palasia} EXCEPT {vijay} ORDER BY 1")]
print("Palasia EXCEPT Vijay Nagar ->", ids)

ORDER BY 1 means "sort by the first column", a handy shortcut when the column name is awkward. Customer 1, Asha, is the only one in both lists. Customer 6 shopped only at Vijay Nagar, while customers 2 and 4 shopped only at Palasia.

INTERSECT and EXCEPT

INTERSECT keeps rows that appear in the result of both queries. EXCEPT keeps rows from the first query that do not appear in the second. Both remove duplicates, just like UNION.

Order matters for EXCEPT. "Vijay Nagar EXCEPT Palasia" gives customer 6, but "Palasia EXCEPT Vijay Nagar" gives customers 2 and 4. INTERSECT and UNION give the same answer whichever query comes first.

The two customer lists overlap only at Asha (customer 1). Each set operator picks a different part of the picture.

Support across databases

On older MySQL versions, write INTERSECT as an IN subquery and EXCEPT as NOT EXISTS or a LEFT JOIN with IS NULL. The SQL subqueries article shows both forms.

One more SQLite quirk. In standard SQL, PostgreSQL and MySQL, INTERSECT binds more tightly than UNION, the way multiplication binds more tightly than addition.

SQLite gives all set operators equal precedence and simply runs them left to right. When you chain three or more queries, wrap parts in a subquery or a common table expression so the order is explicit everywhere.

Practical UNION ALL patterns for reports

Set operators are not only for comparing lists. Analysts use UNION ALL constantly to build reports from pieces.

Adding a grand total row

A store report usually ends with a "total" line. GROUP BY gives the per-store rows; a second query gives the total; UNION ALL stacks them. A sort_key column keeps the total at the bottom, because the order of rows in a combined result is never guaranteed without ORDER BY.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE stores (store_id INT, store_name TEXT, area TEXT);
CREATE TABLE orders (order_id INT, customer_id INT, store_id INT, order_date TEXT, amount INT, payment_mode TEXT);
INSERT INTO stores VALUES (1,'Malwa Mart Vijay Nagar','Vijay Nagar'), (2,'Malwa Mart Palasia','Palasia'),
 (3,'Malwa Mart Rajwada','Rajwada'), (4,'Malwa Mart Bhawarkuan','Bhawarkuan');
INSERT INTO orders VALUES
 (101,1,1,'2026-03-01',1040,'UPI'), (102,2,2,'2026-03-01',330,'Cash'), (103,1,1,'2026-03-03',275,'UPI'),
 (104,3,3,'2026-03-04',180,'Card'), (105,4,2,'2026-03-05',680,'UPI'), (106,2,4,'2026-03-07',120,'Cash'),
 (107,5,3,'2026-03-08',1560,'Card'), (108,6,1,'2026-03-08',220,'UPI'), (109,4,4,'2026-03-10',340,NULL),
 (110,1,2,'2026-03-12',465,'Cash'), (111,6,3,'2026-03-14',NULL,'UPI');
""")

query = """
SELECT 1 AS sort_key, s.area AS label, COUNT(*) AS orders, SUM(o.amount) AS revenue
FROM orders AS o JOIN stores AS s ON s.store_id = o.store_id
GROUP BY s.area
UNION ALL
SELECT 2, 'All stores', COUNT(*), SUM(amount) FROM orders
ORDER BY sort_key, revenue DESC
"""
for _, label, orders, revenue in con.execute(query):
    print(f"{label:12} {orders:>3} orders  {revenue:>5}")

The four stores add up to 1,740 + 1,535 + 1,475 + 460 = 5,210 rupees, which is exactly the "All stores" line. PostgreSQL and MySQL can also produce subtotal rows with GROUP BY ROLLUP, which SQLite does not support, so UNION ALL is the portable method.

Combining similar tables into one list

The Diwali SMS also needs to go to staff. Customers and staff live in different tables, with different columns, but both have a name. Select the same shape from each and add a literal column saying where each row came from.

SELECT name, 'customer' AS person_type FROM customers
UNION ALL
SELECT name, 'staff' FROM staff
ORDER BY person_type, name;

The same idea merges monthly tables (orders_2025, orders_2026) or data from two branch systems into one dataset before analysis.

Common mistakes with UNION, INTERSECT and EXCEPT

• Mismatched column order. Columns pair up by position. Line them up and give them the same aliases.
• Using UNION when you meant UNION ALL. UNION silently removes genuine repeat rows, such as two identical 120-rupee bills. Totals then come out too low.
• ORDER BY inside each part. Only one ORDER BY is allowed, at the end. SQLite reports an error if you put it earlier.
• Expecting INTERSECT to match by one column. It compares whole rows. SELECT customer_id, store_id will rarely intersect.
• NULL handling. Set operators treat two NULLs as equal, unlike = in WHERE. A NULL row in both inputs survives INTERSECT.

Set operators show up in reconciliation tasks that analysts do weekly. To work through those with a trainer, look at Debugshala's analytics course for non-IT graduates, and keep your speed up with aptitude drills on Debugshala Test.

Key takeaways

• UNION stacks two results and removes duplicates; UNION ALL keeps every row and is faster.
• INTERSECT keeps rows found in both results; EXCEPT keeps rows found only in the first.
• Both queries need the same number of columns, matched by position, with compatible types.
• A single ORDER BY at the end sorts the combined result.
• MySQL needs 8.0.31+ for INTERSECT and EXCEPT; SQLite runs chained set operators left to right.
• UNION ALL with a literal label column is the portable way to add total rows or merge similar tables.