UNION, INTERSECT and EXCEPT in SQL

UNION, INTERSECT and EXCEPT combine the results of two queries into one, the way you would combine two lists. UNION keeps every row that is in either result, INTERSECT keeps the rows that are in both, and EXCEPT keeps the rows of the first result that the second one lacks. They answer questions such as “who bought online or in the shop”, “who paid both ways” and “which products sold in March but not in April”. A join puts the columns of two tables side by side; a set operation stacks rows on top of each other, so both queries must return the same number of columns. All three remove duplicate rows unless you add ALL, and that one word is behind the most common mistake with them.

You can run each query below where you read it: the page runs SQLite 3.49.1 for you, inside sql.js 1.14.2. The text also notes each place where PostgreSQL 18 or MySQL 8.4 behaves differently, with their manuals as the source.

Three ways to combine two results

Each query below takes two lists of customers from the same table, the ones who paid by upi and the ones who paid by card, and combines them in one of the three ways:

-- Eleven payments of an online shop: who paid, how ('upi', 'card' or 'cod' for cash on delivery) and how much,
-- in paise.
CREATE TABLE payments (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO payments VALUES
  (1,  'Asha',   'upi',   45000), (2,  'Bilal',  'card', 120000), (3,  'Asha',   'card',  30000),
  (4,  'Chitra', 'upi',    8000), (5,  'Dev',    'cod',   56000), (6,  'Bilal',  'card',  15000),
  (7,  'Asha',   'upi',   99000), (8,  'Esha',   'card',  64000), (9,  'Chitra', 'upi',   21000),
  (10, 'Farhan', 'upi',   37000), (11, 'Esha',   'upi',   12000);

-- 1. UNION: customers who paid by upi, by card or both, each name once.
SELECT customer FROM payments WHERE method = 'upi'
UNION
SELECT customer FROM payments WHERE method = 'card'
ORDER BY customer;

-- 2. INTERSECT: customers who paid both ways.
SELECT customer FROM payments WHERE method = 'upi'
INTERSECT
SELECT customer FROM payments WHERE method = 'card'
ORDER BY customer;

-- 3. EXCEPT: customers who paid by card but never by upi.
SELECT customer FROM payments WHERE method = 'card'
EXCEPT
SELECT customer FROM payments WHERE method = 'upi'
ORDER BY customer;

┌──────────┐
│ customer │
├──────────┤
│ Asha     │
│ Bilal    │
│ Chitra   │
│ Esha     │
│ Farhan   │
└──────────┘
┌──────────┐
│ customer │
├──────────┤
│ Asha     │
│ Esha     │
└──────────┘
┌──────────┐
│ customer │
├──────────┤
│ Bilal    │
└──────────┘

UNION returns five names. Asha paid by upi twice and by card once, yet she appears once, because UNION removes duplicate rows from the combined result, including duplicates inside each list. Dev is missing: he only ever paid cash, so neither list contains him. INTERSECT keeps Asha and Esha, the two customers on both lists. EXCEPT keeps the card payers who are not on the upi list, which leaves Bilal alone.

EXCEPT is the one operator whose order matters. Swap the two queries and the question changes: upi EXCEPT card returns Chitra and Farhan, the customers who paid by upi and never by card. UNION and INTERSECT give the same rows whichever query comes first.

UNION or UNION ALL

UNION ALL appends the second result to the first and removes nothing. UNION does the same and then removes every duplicate row, as if SELECT DISTINCT ran over the combined rows. Finding duplicates means sorting or hashing every row, which is why PostgreSQL’s manual says that UNION ALL is usually significantly quicker than UNION and asks you to use ALL when you can.

Speed is the smaller reason. The bigger one is that UNION changes the answer whenever two different events happen to look identical. Refunds recorded in two tables show how:

-- Refunds are recorded in two tables: money sent back to the customer's account, and store credit.
-- Order 102 was refunded half each way, 99 rupees to the account and 99 rupees as credit.
CREATE TABLE account_refunds (order_id INTEGER NOT NULL, amount_paise INTEGER NOT NULL);
CREATE TABLE credit_refunds  (order_id INTEGER NOT NULL, amount_paise INTEGER NOT NULL);
INSERT INTO account_refunds VALUES (101, 25000), (102, 9900), (105, 9900);
INSERT INTO credit_refunds  VALUES (102, 9900), (104, 5000);

-- Every refund, five rows in all.
SELECT 'account' AS source, order_id, amount_paise FROM account_refunds
UNION ALL
SELECT 'credit', order_id, amount_paise FROM credit_refunds;

-- The total refunded, three ways.
SELECT 'UNION of amounts'          AS combined_with, sum(amount_paise) / 100.0 AS rupees
FROM (SELECT amount_paise FROM account_refunds UNION SELECT amount_paise FROM credit_refunds) AS r
UNION ALL
SELECT 'UNION of order and amount', sum(amount_paise) / 100.0
FROM (SELECT order_id, amount_paise FROM account_refunds
      UNION SELECT order_id, amount_paise FROM credit_refunds) AS r
UNION ALL
SELECT 'UNION ALL', sum(amount_paise) / 100.0
FROM (SELECT amount_paise FROM account_refunds UNION ALL SELECT amount_paise FROM credit_refunds) AS r;

┌─────────┬──────────┬──────────────┐
│ source  │ order_id │ amount_paise │
├─────────┼──────────┼──────────────┤
│ account │ 101      │ 25000        │
│ account │ 102      │ 9900         │
│ account │ 105      │ 9900         │
│ credit  │ 102      │ 9900         │
│ credit  │ 104      │ 5000         │
└─────────┴──────────┴──────────────┘
┌───────────────────────────┬────────┐
│       combined_with       │ rupees │
├───────────────────────────┼────────┤
│ UNION of amounts          │ 399.0  │
│ UNION of order and amount │ 498.0  │
│ UNION ALL                 │ 597.0  │
└───────────────────────────┴────────┘

The first query lists the five refunds, 597 rupees in all. Selecting only the amounts and combining them with UNION leaves three distinct values, 250, 99 and 50 rupees, so the total drops to 399: the three refunds of 99 rupees became one row. Adding the order number helps, but not enough, because order 102 was refunded 99 rupees each way and those two rows are still identical. Only UNION ALL keeps all five refunds and returns 597.

The rule that follows: when the rows are events you will count or add up, such as payments, refunds, log lines or stock movements, combine them with UNION ALL, and add a column that says where each row came from, like source in the first query. Use UNION when you want a list of distinct values, such as the names in the previous section.

The rules every compound query follows

A query built from several queries with set operators is a compound query. Each of its parts is an ordinary SELECT, but the parts must fit together, and the next example breaks the rules on purpose:

-- The rules of a compound query, tried on a small table of products.
CREATE TABLE products (id INTEGER PRIMARY KEY, code TEXT NOT NULL, price_paise INTEGER NOT NULL);
INSERT INTO products VALUES (1, 'TEA-250', 25000), (2, 'RICE-5K', 42000);

-- 1. The result's column names come from the first query.
SELECT id AS product, code AS label FROM products
UNION ALL
SELECT 3 AS another_name, 'GHEE-1L' AS ignored;

-- 2. SQLite mixes an integer and a text in one column; PostgreSQL refuses.
SELECT id AS value, typeof(id) AS type FROM products
UNION ALL
SELECT code, typeof(code) FROM products;

-- 3. Both sides must return the same number of columns.
SELECT id, code FROM products
UNION
SELECT id FROM products;

-- 4. ORDER BY belongs after the last query, where it sorts the whole result.
SELECT code FROM products ORDER BY code
UNION
SELECT 'GHEE-1L';

┌─────────┬─────────┐
│ product │  label  │
├─────────┼─────────┤
│ 1       │ TEA-250 │
│ 2       │ RICE-5K │
│ 3       │ GHEE-1L │
└─────────┴─────────┘
┌─────────┬─────────┐
│  value  │  type   │
├─────────┼─────────┤
│ 1       │ integer │
│ 2       │ integer │
│ TEA-250 │ text    │
│ RICE-5K │ text    │
└─────────┴─────────┘

Parse error near line 16: SELECTs to the left and right of UNION do not have the same number of result columns
Parse error near line 21: ORDER BY clause should come after UNION not before

• The names come from the first query. The first result has the columns product and label; the aliases another_name and ignored in the second query change nothing. This holds in all three engines, so name the columns in the first query.
• The types must fit together. SQLite keeps an integer and a text in the same result column, as typeof() shows. MySQL gives the column a type that fits both sides, a string type here. PostgreSQL resolves one type for each result column and fails when the types belong to different categories: UNION types integer and text cannot be matched. When you really mean to mix them, cast one side: CAST(id AS TEXT) in PostgreSQL and SQLite, CAST(id AS CHAR) in MySQL.
• The column counts must match. The third query asks for two columns on one side and one on the other. SQLite answers that the SELECTs to the left and right of UNION do not have the same number of result columns; PostgreSQL says that each UNION query must have the same number of columns, and MySQL fails with error 1222.
• ORDER BY goes at the very end. SQLite rejects the fourth query with “ORDER BY clause should come after UNION not before”; PostgreSQL and MySQL report a syntax error. At the end of a compound query, ORDER BY and LIMIT apply to the whole result, as the last section shows.

Duplicates and NULLs

A set operation compares whole rows, and for that comparison two NULLs count as the same value. This is a different rule from the = operator, under which NULL = NULL is not true, and it makes set operations behave differently from joins on columns that contain NULLs:

-- Orders of two months with the coupon each used; NULL means no coupon.
CREATE TABLE march_orders (id INTEGER PRIMARY KEY, coupon TEXT);
CREATE TABLE april_orders (id INTEGER PRIMARY KEY, coupon TEXT);
INSERT INTO march_orders VALUES (1, 'WELCOME10'), (2, NULL), (3, 'FESTIVE'), (4, NULL);
INSERT INTO april_orders VALUES (5, 'FESTIVE'), (6, NULL), (7, 'SUMMER5');

-- 1. UNION: every coupon value of both months, the two NULLs of March and the one of April kept once.
SELECT coupon FROM march_orders
UNION
SELECT coupon FROM april_orders
ORDER BY coupon;

-- 2. INTERSECT: values that occur in both months. NULL counts as the same value on both sides.
SELECT coupon FROM march_orders
INTERSECT
SELECT coupon FROM april_orders
ORDER BY coupon;

-- 3. The same question as a join: NULL = NULL is not true, so the NULL rows never pair up.
SELECT DISTINCT m.coupon
FROM march_orders AS m
JOIN april_orders AS a ON a.coupon = m.coupon
ORDER BY m.coupon;

-- 4. EXCEPT: March values that April does not have. March's NULL goes, because April has a NULL too.
SELECT coupon FROM march_orders
EXCEPT
SELECT coupon FROM april_orders
ORDER BY coupon;

┌───────────┐
│  coupon   │
├───────────┤
│           │
│ FESTIVE   │
│ SUMMER5   │
│ WELCOME10 │
└───────────┘
┌─────────┐
│ coupon  │
├─────────┤
│         │
│ FESTIVE │
└─────────┘
┌─────────┐
│ coupon  │
├─────────┤
│ FESTIVE │
└─────────┘
┌───────────┐
│  coupon   │
├───────────┤
│ WELCOME10 │
└───────────┘

In these output tables an empty cell is a NULL. UNION returns four values, and the three NULLs of the two months become a single row. INTERSECT returns FESTIVE and the NULL, because both months had orders without a coupon. The join asks the same question with a.coupon = m.coupon, a comparison that is never true for NULLs, so it finds only FESTIVE. EXCEPT removes March’s NULL as well, because April also has one. PostgreSQL sorts the NULL row last instead of first; the rows are the same.

The comparison also follows the column’s collation, its rule for comparing text. SQLite compares text byte by byte unless a column says otherwise, and PostgreSQL’s built-in collations count two strings as equal only when they have the same bytes, so 'Asha' and 'ASHA' stay two rows in both. MySQL 8.4’s default collation, utf8mb4_0900_ai_ci, is case- and accent-insensitive, so there SELECT 'Asha' UNION SELECT 'ASHA' returns a single row, and so does SELECT 'Jose' UNION SELECT 'José'. When the same data gives different row counts in two engines, compare the collations first.

INTERSECT and EXCEPT in each engine

The three operators exist in all three engines, but not every form of them, and they are not all read in the same order:

With ALL, the two operators count copies instead of only noting that a row exists. PostgreSQL’s manual gives the arithmetic: a row that appears m times on the left and n times on the right appears min(m, n) times after INTERSECT ALL and max(m − n, 0) times after EXCEPT ALL. A product sold three times in March and once in April therefore comes out once from INTERSECT ALL and twice from EXCEPT ALL, which is the difference in sales counts rather than in the list of products. MySQL added INTERSECT and EXCEPT in version 8.0.31, with DISTINCT and ALL for both; in SQLite only UNION has an ALL form.

INTERSECT goes first, except in SQLite

PostgreSQL and MySQL evaluate INTERSECT before UNION and EXCEPT, the way multiplication goes before addition, and their manuals say so in almost the same words. SQLite does not: its manual says that a compound query of three or more parts is grouped from left to right. The same text therefore gives different answers:

-- The same payments, and a list of VIP customers.
CREATE TABLE payments (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, method TEXT NOT NULL);
INSERT INTO payments VALUES
  (1, 'Asha', 'upi'), (2, 'Bilal', 'card'), (3, 'Asha', 'card'), (4, 'Chitra', 'upi'), (5, 'Dev', 'cod'),
  (6, 'Bilal', 'card'), (7, 'Asha', 'upi'), (8, 'Esha', 'card'), (9, 'Chitra', 'upi'), (10, 'Farhan', 'upi'),
  (11, 'Esha', 'upi');
CREATE TABLE vip (customer TEXT NOT NULL);
INSERT INTO vip VALUES ('Dev'), ('Farhan');

-- The question: every VIP, plus the customers who paid both by card and by upi.

-- 1. Written in that order. SQLite reads it from left to right: (vip UNION card) INTERSECT upi.
SELECT customer FROM vip
UNION
SELECT customer FROM payments WHERE method = 'card'
INTERSECT
SELECT customer FROM payments WHERE method = 'upi'
ORDER BY customer;

-- 2. The INTERSECT first: left to right is now the intended reading, in every engine.
SELECT customer FROM payments WHERE method = 'card'
INTERSECT
SELECT customer FROM payments WHERE method = 'upi'
UNION
SELECT customer FROM vip
ORDER BY customer;

-- 3. The INTERSECT inside a subquery: no reading order to remember.
SELECT customer FROM vip
UNION
SELECT customer FROM (
  SELECT customer FROM payments WHERE method = 'card'
  INTERSECT
  SELECT customer FROM payments WHERE method = 'upi'
) AS both_ways
ORDER BY customer;

┌──────────┐
│ customer │
├──────────┤
│ Asha     │
│ Esha     │
│ Farhan   │
└──────────┘
┌──────────┐
│ customer │
├──────────┤
│ Asha     │
│ Dev      │
│ Esha     │
│ Farhan   │
└──────────┘
┌──────────┐
│ customer │
├──────────┤
│ Asha     │
│ Dev      │
│ Esha     │
│ Farhan   │
└──────────┘

The question is “every VIP, plus the customers who paid both by card and by upi”, and the intended answer is Asha, Dev, Esha and Farhan. Written in the order of the sentence, SQLite first forms vip UNION card and then intersects that with the upi list, which drops Dev, a VIP who only ever paid cash. PostgreSQL and MySQL return all four names for the same text. The second and third queries return the right answer everywhere: the second puts the INTERSECT first, so that reading from left to right and the precedence of PostgreSQL and MySQL agree, and the third moves the INTERSECT into a subquery, which needs no rule at all. PostgreSQL and MySQL would also accept parentheses around card INTERSECT upi, but SQLite rejects parentheses around the parts of a compound query, so the subquery is the form that runs in all three.

Sorting and limiting the combined result

ORDER BY and LIMIT written after the last query belong to the compound query as a whole, so they sort and cut the combined rows. To sort or limit one part on its own, give that part its own subquery:

-- The shop's payments again, with their amounts in paise.
CREATE TABLE payments (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  method       TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL
);
INSERT INTO payments VALUES
  (1,  'Asha',   'upi',   45000), (2,  'Bilal',  'card', 120000), (3,  'Asha',   'card',  30000),
  (4,  'Chitra', 'upi',    8000), (5,  'Dev',    'cod',   56000), (6,  'Bilal',  'card',  15000),
  (7,  'Asha',   'upi',   99000), (8,  'Esha',   'card',  64000), (9,  'Chitra', 'upi',   21000),
  (10, 'Farhan', 'upi',   37000), (11, 'Esha',   'upi',   12000);

-- 1. ORDER BY and LIMIT at the end apply to the combined result: the three largest upi or card payments.
SELECT id, customer, method, amount_paise FROM payments WHERE method = 'upi'
UNION ALL
SELECT id, customer, method, amount_paise FROM payments WHERE method = 'card'
ORDER BY amount_paise DESC
LIMIT 3;

-- 2. The two largest payments of each method: each query sorts and limits inside a subquery.
SELECT * FROM (SELECT id, customer, method, amount_paise FROM payments
               WHERE method = 'upi' ORDER BY amount_paise DESC LIMIT 2) AS top_upi
UNION ALL
SELECT * FROM (SELECT id, customer, method, amount_paise FROM payments
               WHERE method = 'card' ORDER BY amount_paise DESC LIMIT 2) AS top_card
ORDER BY method, amount_paise DESC;

┌────┬──────────┬────────┬──────────────┐
│ id │ customer │ method │ amount_paise │
├────┼──────────┼────────┼──────────────┤
│ 2  │ Bilal    │ card   │ 120000       │
│ 7  │ Asha     │ upi    │ 99000        │
│ 8  │ Esha     │ card   │ 64000        │
└────┴──────────┴────────┴──────────────┘
┌────┬──────────┬────────┬──────────────┐
│ id │ customer │ method │ amount_paise │
├────┼──────────┼────────┼──────────────┤
│ 2  │ Bilal    │ card   │ 120000       │
│ 8  │ Esha     │ card   │ 64000        │
│ 7  │ Asha     │ upi    │ 99000        │
│ 1  │ Asha     │ upi    │ 45000        │
└────┴──────────┴────────┴──────────────┘

The first query returns the three largest payments of both methods together: Bilal’s 1,200 rupees by card, Asha’s 990 by upi and Esha’s 640 by card. The second takes the two largest of each method separately, which only works because each ORDER BY ... LIMIT 2 sits inside its own subquery; the final ORDER BY then arranges the four rows. In PostgreSQL and MySQL you may instead put each query in parentheses with its own ORDER BY and LIMIT, as their manuals show, but the subquery form runs everywhere. MySQL requires a name for each subquery in FROM, which is why they are called top_upi and top_card.

Your turn

-- One row per product ordered in March and never in April: product_id.
SELECT product_id
FROM order_lines
WHERE ordered_on >= '2026-03-01' AND ordered_on < '2026-04-01';

# Sample tests: each one builds a small order_lines table, runs your query on it and compares the rows it returns
# (in any order).
tests:
  - name: keeps a product sold in March only and drops one sold in both months
    setup: |
      CREATE TABLE order_lines (order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, ordered_on TEXT NOT NULL);
      INSERT INTO order_lines VALUES
        (1, 10, '2026-03-05'), (2, 11, '2026-03-12'), (3, 11, '2026-04-02'), (4, 12, '2026-04-20');
    expected:
      columns: [product_id]
      rows:
        - [10]
  - name: lists a product once however often it sold in March
    setup: |
      CREATE TABLE order_lines (order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, ordered_on TEXT NOT NULL);
      INSERT INTO order_lines VALUES
        (1, 20, '2026-03-02'), (2, 20, '2026-03-09'), (3, 20, '2026-03-30'), (4, 21, '2026-03-15'),
        (5, 21, '2026-03-16');
    expected:
      columns: [product_id]
      rows:
        - [20]
        - [21]
  - name: counts the first and last days of each month correctly
    setup: |
      CREATE TABLE order_lines (order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, ordered_on TEXT NOT NULL);
      INSERT INTO order_lines VALUES
        (1, 30, '2026-03-01'), (2, 31, '2026-03-31'), (3, 31, '2026-04-01'), (4, 32, '2026-03-20'),
        (5, 32, '2026-04-30'), (6, 33, '2026-03-10'), (7, 33, '2026-05-01'), (8, 34, '2026-02-28');
    expected:
      columns: [product_id]
      rows:
        - [30]
        - [33]

Interview questions

Fresher · What is the difference between UNION and UNION ALL, and when do you use each?

Both put the rows of two queries with matching column counts into one result. UNION ALL keeps every row, so its result has as many rows as the two inputs together. UNION also removes duplicate rows, both across the two inputs and inside each one, which costs a sort or a hash of the whole result. Use UNION ALL whenever the rows are events you will count or add up, such as payments or log lines, because two different events can look identical and UNION would silently merge them; adding a column that names each row’s source keeps them apart. Use UNION when you want distinct values, such as the list of customers who used either of two channels. If the inputs cannot overlap at all, UNION ALL gives the same answer with less work.

Key takeaways

• UNION keeps rows in either result, INTERSECT rows in both, EXCEPT rows of the first result that the second lacks; only EXCEPT depends on which query comes first.
• All three remove duplicates unless you write ALL. Combine events you will count or add up with UNION ALL, or identical rows from different events vanish from the total.
• The parts must return the same number of columns with compatible types; the result’s column names come from the first part, and ORDER BY and LIMIT go after the last one.
• Set operations treat two NULLs as the same value and compare text with the column’s collation, which in MySQL is case-insensitive by default.
• PostgreSQL and MySQL evaluate INTERSECT before UNION and EXCEPT; SQLite reads from left to right, so put the INTERSECT first or inside a subquery. INTERSECT ALL and EXCEPT ALL do not exist in SQLite.