SQL UNION, INTERSECT and EXCEPT

Set operators combine the results of two queries into one. UNION stacks them, INTERSECT keeps rows found in both, and EXCEPT keeps rows in the first but not the second. Where a join adds columns, set operators add or compare rows.

Syntax

SELECT columns FROM table_a
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT columns FROM table_b;

Rules:

1. Both queries must return the same number of columns.

2. Matching columns must have compatible types.

3. Column names come from the first query.

4. One ORDER BY goes at the very end and sorts the combined result.

Examples

1. UNION: one contact list from two tables

Every city where ShopKart has either customers or employees:

SELECT city FROM customers
UNION
SELECT city FROM employees
ORDER BY city;

Result:

(11 rows)

UNION removed duplicates: Bengaluru appears once even though it's in both tables.

2. UNION ALL keeps duplicates

SELECT 'customers' AS source, COUNT(*) AS rows FROM (
  SELECT city FROM customers
  UNION ALL
  SELECT city FROM employees
) AS u;

Result:

(1 row)

12 customers + 12 employees = 24 rows. With UNION, the same subquery gives only the distinct cities.

3. Tag each source

Add a constant column to show where each row came from:

SELECT customer_name AS person, city, 'Customer' AS type
FROM customers WHERE city = 'Bengaluru'
UNION ALL
SELECT employee_name, city, 'Employee'
FROM employees WHERE city = 'Bengaluru'
ORDER BY type, person;

Result:

(6 rows)

4. INTERSECT: in both

Cities with both customers and employees:

SELECT city FROM customers
INTERSECT
SELECT city FROM employees
ORDER BY city;

Result:

(6 rows)

5. EXCEPT: in the first, not the second

Cities with customers but no ShopKart staff:

SELECT city FROM customers
EXCEPT
SELECT city FROM employees
ORDER BY city;

Result:

(5 rows)

Order matters: employees EXCEPT customers would give cities with staff but no customers (none here).

6. Combining metrics in one report

Stack two different summaries into one table:

SELECT 'Delivered orders' AS metric, COUNT(*)::numeric AS value
FROM orders WHERE status = 'Delivered'
UNION ALL
SELECT 'Units sold', SUM(quantity)
FROM order_items
UNION ALL
SELECT 'Active products', COUNT(DISTINCT product_id)
FROM order_items;

Result:

(3 rows)

Common mistakes

Different number of columns:

SELECT customer_name, city FROM customers
UNION
SELECT employee_name FROM employees;

Error: each UNION query must have the same number of columns

• Using UNION when you meant UNION ALL. UNION silently removes legitimate duplicate rows (two identical sales lines, for example) and is slower.
• ORDER BY inside each query. Put one ORDER BY at the end.
• Columns in a different order. SQL matches by position, not name. SELECT name, city UNION SELECT city, name mixes them up.

Practice question

Find customers who have paid with both UPI and Card at least once. Show their customer_id.

SELECT customer_id FROM orders WHERE payment_method = 'UPI'
INTERSECT
SELECT customer_id FROM orders WHERE payment_method = 'Card'
ORDER BY customer_id;

Key takeaways

• UNION combines rows and removes duplicates; UNION ALL keeps them and is faster.
• INTERSECT keeps rows in both results; EXCEPT keeps rows only in the first.
• Queries must have the same number of compatible columns, matched by position.
• One ORDER BY at the end sorts the combined result.