PostgreSQL Set Operations and Combined Queries

Q: Can a set operation be used in a subquery?

Yes. SELECT * FROM (A UNION B) AS t is valid; wrap it in parentheses and add an alias.

1. What You'll Learn

UNION (deduplicated merge)
UNION ALL (duplicate-preserving merge)
INTERSECT / INTERSECT ALL (intersection)
EXCEPT / EXCEPT ALL (difference)
ORDER BY in set operations
NULL handling in set operations
Set operations vs JOIN selection

2. The Story

Bob is a data analyst at an e-commerce platform. The CEO asks him three questions:

Which products were best-sellers in Q1, Q2, and Q3? (UNION ALL to merge)
Which products were best-sellers in every quarter? (INTERSECT = evergreen)
Which products sold well in Q1 but not in Q2? (EXCEPT = churned)

Bob realizes these questions map exactly onto SQL's three set operations: UNION, INTERSECT, EXCEPT.

3. Concept

(1) Set Operation Overview

Operation	Meaning	Dedupe	Analogy
UNION	Merge result sets	Yes	A ∪ B
UNION ALL	Merge result sets	No	A ∪ B (with duplicates)
INTERSECT	Intersection	Yes	A ∩ B
INTERSECT ALL	Intersection	No	A ∩ B (with duplicate counts)
EXCEPT	In A but not in B	Yes	A - B
EXCEPT ALL	In A but not in B	No	A - B (with duplicate counts)

flowchart TD
    subgraph Union
        U1((A)) --- U2((B))
        U1 & U2 --> U3["A ∪ B"]
    end
    subgraph Intersect
        I1((A)) --- I2((B))
        I1 ∩ I2 --> I3["A ∩ B"]
    end
    subgraph Except
        E1((A)) --- E2((B))
        E1 - E2 --> E3["A - B"]
    end

    style U3 fill:#c8e6c9
    style I3 fill:#e1f5fe
    style E3 fill:#fff9c4

(2) UNION and UNION ALL

▶ Example: UNION Merges Best-Sellers from Three Quarters

SQL

SELECT product_id, product_name FROM hot_products_q1
UNION
SELECT product_id, product_name FROM hot_products_q2
UNION
SELECT product_id, product_name FROM hot_products_q3
ORDER BY product_id;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

UNION auto-deduplicates: if a product is a best-seller in both Q1 and Q2, it appears only once.

▶ Example: UNION ALL Preserves Duplicates

SQL

SELECT product_id, product_name, 'Q1' AS quarter FROM hot_products_q1
UNION ALL
SELECT product_id, product_name, 'Q2' AS quarter FROM hot_products_q2
UNION ALL
SELECT product_id, product_name, 'Q3' AS quarter FROM hot_products_q3
ORDER BY quarter, product_id;

TEXT

 product_id | product_name | quarter
------------+--------------+---------
        101 | Widget Pro   | Q1
        102 | Gadget Mini  | Q1
        101 | Widget Pro   | Q2
        103 | Server Rack  | Q2
        101 | Widget Pro   | Q3
        104 | Cable Max    | Q3

Scenario	Recommended	Reason
Merge different sources with no duplicates	UNION ALL	No dedupe needed, faster
Merge with possible duplicates that must be removed	UNION	Auto-dedupe
Merge and tag the source	UNION ALL + tag column	Keep dupes and distinguish sources

▶ Example: UNION ALL to Merge Multi-Table Stats

SQL

SELECT 'NA' AS region, COUNT(*) AS order_count, SUM(amount) AS total FROM orders_na
UNION ALL
SELECT 'EU', COUNT(*), SUM(amount) FROM orders_eu
UNION ALL
SELECT 'APAC', COUNT(*), SUM(amount) FROM orders_apac;

TEXT

 count 
-------
     5
(1 row)

(3) INTERSECT and INTERSECT ALL

▶ Example: Find Evergreen Products That Sold Well in All Three Quarters

SQL

SELECT product_id, product_name FROM hot_products_q1
INTERSECT
SELECT product_id, product_name FROM hot_products_q2
INTERSECT
SELECT product_id, product_name FROM hot_products_q3;

TEXT

 product_id | product_name
------------+--------------
        101 | Widget Pro

Widget Pro is the only evergreen product that was a best-seller every quarter.

▶ Example: INTERSECT ALL Preserves Duplicate Counts

Suppose product 101 appears 2 times in the Q1 best-seller list, 1 time in Q2, and 3 times in Q3:

SQL

SELECT product_id FROM hot_products_q1_detail
INTERSECT ALL
SELECT product_id FROM hot_products_q2_detail;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

INTERSECT ALL returns MIN(occurrence count): product 101 returns min(2, 1) = 1 row.

Operation	Dedupe	Duplicate-row handling	Typical use
INTERSECT	Yes	Keep only one row	Find common items
INTERSECT ALL	No	Keep by minimum occurrence count	Match duplicate frequency exactly

▶ Example: INTERSECT Finds Customers Common to Multiple Years

SQL

SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2023
INTERSECT
SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024
INTERSECT
SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025;

TEXT

CREATE TABLE

Loyal customers who placed orders in all three consecutive years.

(4) EXCEPT and EXCEPT ALL

▶ Example: Q1 Best-Sellers That Did Not Make Q2 (Churned)

SQL

SELECT product_id, product_name FROM hot_products_q1
EXCEPT
SELECT product_id, product_name FROM hot_products_q2;

TEXT

 product_id | product_name
------------+--------------
        102 | Gadget Mini

Gadget Mini was a Q1 best-seller but missed the Q2 list—it churned.

▶ Example: Q2 Newly Promoted Best-Sellers

SQL

SELECT product_id, product_name FROM hot_products_q2
EXCEPT
SELECT product_id, product_name FROM hot_products_q1;

TEXT

 product_id | product_name
------------+--------------
        103 | Server Rack

Server Rack newly entered the best-seller list in Q2.

▶ Example: EXCEPT ALL Preserves Duplicate Counts

SQL

SELECT product_id FROM order_items_2024
EXCEPT ALL
SELECT product_id FROM order_items_2025;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

If product 101 appears 5 times in 2024 and 3 times in 2025, EXCEPT ALL returns 5 - 3 = 2 rows.

Operation	Dedupe	Duplicate-row handling	Typical use
EXCEPT	Yes	Keep only one row	Find differences
EXCEPT ALL	No	Keep by occurrence-count difference	Compute exact surplus count

4. Key Points

(1) Rules for Set Operations

Rule one: the column counts must be the same.

SQL

SELECT id, name FROM table_a
UNION
SELECT id, name, price FROM table_b;

TEXT

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

Rule two: corresponding column types must be compatible.

Rule	Requirement	Consequence of violation
Column count	Must be the same	Compile error
Column type	Must be compatible	Implicit cast or error
Column name	Takes the first query's column name	Mind aliases

▶ Example: Unify Column Names with Aliases

SQL

SELECT product_id, product_name AS name FROM products_active
UNION ALL
SELECT sku AS product_id, title AS name FROM products_legacy
ORDER BY product_id;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) ORDER BY Position in Set Operations

ORDER BY may appear only after the last query and applies to the entire result set.

▶ Example: Correct ORDER BY

SQL

SELECT product_id, product_name FROM hot_products_q1
UNION ALL
SELECT product_id, product_name FROM hot_products_q2
ORDER BY product_id;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Control Precedence with Parentheses

SQL

(SELECT product_id FROM hot_products_q1
 EXCEPT
 SELECT product_id FROM hot_products_q2)
UNION ALL
(SELECT product_id FROM hot_products_q2
 EXCEPT
 SELECT product_id FROM hot_products_q3)
ORDER BY product_id;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Compute the difference first, then merge. Without parentheses, UNION has lower precedence than INTERSECT/EXCEPT.

Operation	Precedence	Associativity
INTERSECT	Highest	Left to right
EXCEPT	Middle	Left to right
UNION / UNION ALL	Middle	Left to right

(3) NULL Handling in Set Operations

Set operations treat NULL as equal (unlike ordinary comparisons where NULL <> NULL).

▶ Example: NULL Deduplication in UNION

SQL

SELECT NULL AS val
UNION
SELECT NULL AS val;

TEXT

 val
-----

(1 row)

The two NULLs are treated as the same; UNION deduplicates to a single row.

▶ Example: NULL Matching in INTERSECT

SQL

SELECT NULL AS val
INTERSECT
SELECT NULL AS val;

TEXT

 val
-----

(1 row)

NULL matches NULL; INTERSECT returns one row.

Scenario	NULL behavior	Difference from ordinary comparison
UNION	Two NULLs treated as equal, deduplicated	Ordinary NULL = NULL is UNKNOWN
INTERSECT	Two NULLs treated as equal, matched	Ordinary NULL = NULL is UNKNOWN
EXCEPT	Two NULLs treated as equal, cancelled	Ordinary NULL <> NULL is UNKNOWN

(4) Set Operations vs JOIN

▶ Example: INTERSECT Equivalent to INNER JOIN

SQL

SELECT a.product_id
FROM hot_products_q1 a
INNER JOIN hot_products_q2 b ON a.product_id = b.product_id;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Equivalent to:

SQL

SELECT product_id FROM hot_products_q1
INTERSECT
SELECT product_id FROM hot_products_q2;

Dimension	Set operation	JOIN
Semantics	Set operation over rows	Combination of columns
Output columns	Takes the left side's columns	Both tables' columns available
Dedupe	UNION/INTERSECT/EXCEPT dedupe automatically	Needs manual DISTINCT
NULL matching	NULL = NULL	NULL <> NULL
Performance	Large data may sort to dedupe	Indexed Hash Join may be faster
Best for	Merging/intersecting/diffing same-structured result sets	Joining heterogeneous tables to fetch columns

5. Practice

▶ Example: UNION ALL to Merge Orders and Refund Ledger

SQL

SELECT
  order_id   AS transaction_id,
  amount     AS credit,
  0          AS debit,
  'order'    AS type,
  created_at
FROM orders
UNION ALL
SELECT
  refund_id,
  0,
  refund_amount,
  'refund',
  created_at
FROM refunds
ORDER BY created_at;

TEXT

CREATE TABLE

▶ Example: EXCEPT Finds Registered but Unactivated Users

SQL

SELECT user_id, email FROM registered_users
EXCEPT
SELECT user_id, email FROM activated_users;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: INTERSECT Finds Customers Who Bought Both A and B

SQL

SELECT customer_id FROM order_items WHERE product_id = 101
INTERSECT
SELECT customer_id FROM order_items WHERE product_id = 102;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Three-Year Customer Retention Analysis

SQL

SELECT 'retained' AS status, COUNT(*) AS cnt FROM (
  SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024
  INTERSECT
  SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025
) t
UNION ALL
SELECT 'churned', COUNT(*) FROM (
  SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024
  EXCEPT
  SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025
) t;

TEXT

 count 
-------
     5
(1 row)

▶ Example: UNION ALL + GROUP BY for Trend Summary

SQL

SELECT
  product_id,
  SUM(CASE WHEN quarter = 'Q1' THEN 1 ELSE 0 END) AS q1_count,
  SUM(CASE WHEN quarter = 'Q2' THEN 1 ELSE 0 END) AS q2_count,
  SUM(CASE WHEN quarter = 'Q3' THEN 1 ELSE 0 END) AS q3_count
FROM (
  SELECT product_id, 'Q1' AS quarter FROM hot_products_q1
  UNION ALL
  SELECT product_id, 'Q2' FROM hot_products_q2
  UNION ALL
  SELECT product_id, 'Q3' FROM hot_products_q3
) combined
GROUP BY product_id
ORDER BY product_id;

TEXT

 count 
-------
     5
(1 row)

6. Comprehensive Example

Bob's cross-quarter best-seller analysis—one output for evergreen, new, and churned products:

SQL

WITH q1 AS (
  SELECT product_id, product_name FROM hot_products WHERE quarter = 'Q1'
),
q2 AS (
  SELECT product_id, product_name FROM hot_products WHERE quarter = 'Q2'
),
q3 AS (
  SELECT product_id, product_name FROM hot_products WHERE quarter = 'Q3'
),
evergreen AS (
  SELECT product_id, product_name, 'evergreen' AS trend FROM q1
  INTERSECT
  SELECT product_id, product_name, 'evergreen' FROM q2
  INTERSECT
  SELECT product_id, product_name, 'evergreen' FROM q3
),
new_q2 AS (
  SELECT product_id, product_name, 'new_in_q2' AS trend FROM q2
  EXCEPT
  SELECT product_id, product_name, 'new_in_q2' FROM q1
),
new_q3 AS (
  SELECT product_id, product_name, 'new_in_q3' AS trend FROM q3
  EXCEPT
  SELECT product_id, product_name, 'new_in_q3' FROM q2
),
churned_q2 AS (
  SELECT product_id, product_name, 'churned_in_q2' AS trend FROM q1
  EXCEPT
  SELECT product_id, product_name, 'churned_in_q2' FROM q2
),
churned_q3 AS (
  SELECT product_id, product_name, 'churned_in_q3' AS trend FROM q2
  EXCEPT
  SELECT product_id, product_name, 'churned_in_q3' FROM q3
)
SELECT * FROM evergreen
UNION ALL
SELECT * FROM new_q2
UNION ALL
SELECT * FROM new_q3
UNION ALL
SELECT * FROM churned_q2
UNION ALL
SELECT * FROM churned_q3
ORDER BY trend, product_id;

TEXT

 product_id | product_name |    trend
------------+--------------+---------------
        101 | Widget Pro   | evergreen
        103 | Server Rack  | new_in_q2
        104 | Cable Max    | new_in_q3
        102 | Gadget Mini  | churned_in_q2
        103 | Server Rack  | churned_in_q3

7. Set Operation Execution Flow

flowchart TD
    A["Query A"] --> C{Operation}
    B["Query B"] --> C
    C -->|UNION| D["Combine + Deduplicate"]
    C -->|UNION ALL| E["Combine (keep duplicates)"]
    C -->|INTERSECT| F["Match + Deduplicate"]
    C -->|EXCEPT| G["A - B + Deduplicate"]
    D --> H["ORDER BY (optional)"]
    E --> H
    F --> H
    G --> H
    H --> I["Final Result"]

    style D fill:#c8e6c9
    style F fill:#e1f5fe
    style G fill:#fff9c4

Step	Operation	Description
1	Run each subquery	Executed independently; result sets must share structure
2	Set operation	UNION/INTERSECT/EXCEPT
3	Dedupe (if needed)	UNION/INTERSECT/EXCEPT dedupe by default
4	ORDER BY	Applies to the final result set
5	LIMIT	Limit final output rows

❓ FAQ

Q Which is faster, UNION or UNION ALL?

A UNION ALL is faster because it does not deduplicate. If you are certain there are no duplicates, or you don't need deduplication, prefer UNION ALL.

Q Whose column name does a set operation use?

A It takes the first query's column name (or alias). To unify them, write the alias in the first query.

Q Can a set operation be used in a subquery?

A Yes. SELECT * FROM (A UNION B) AS t is valid; wrap it in parentheses and add an alias.

Q What is the precedence of multiple set operations?

A INTERSECT > (EXCEPT = UNION). INTERSECT has the highest precedence; EXCEPT and UNION are at the same level. Use parentheses to make precedence explicit.

Q Is NULL really equal to NULL in set operations?

A Yes. In set operations two NULLs are treated as equal—this is standard SQL behavior, unlike ordinary comparisons where NULL = NULL is UNKNOWN.

Q When do I use a set operation instead of JOIN?

A Use a set operation when you only need to test "exists / not exists" and don't need the joined columns; use JOIN when you need to combine columns from both tables. Set operations read more intuitively; JOIN is more flexible.

📖 Summary

UNION merges and deduplicates; UNION ALL merges and keeps duplicates (better performance)
INTERSECT takes the intersection; EXCEPT takes the difference
The ALL suffix keeps duplicate counts: INTERSECT ALL / EXCEPT ALL
Set operations require the same column count and compatible types
ORDER BY may appear only at the very end of the statement
NULL is treated as equal in set operations (unlike ordinary comparisons)
Precedence: INTERSECT = EXCEPT > UNION; use parentheses to control it
Set operations suit merging/intersecting/diffing same-structured result sets; JOIN suits joining heterogeneous tables

📝 Exercises

⭐ Use UNION ALL to merge the 2024 and 2025 order tables, adding a year tag column, sorted by amount descending.
⭐ Use EXCEPT to find users who are in the customers table but not in the active_users table.
⭐⭐ Use INTERSECT to find best-seller product IDs that appear in all three quarters, and join the products table to output the product names.
⭐⭐ Use a CTE + EXCEPT for customer churn analysis: customers who had orders in 2024 but none in 2025.
⭐⭐⭐ Write a single SQL statement combining UNION ALL + INTERSECT + EXCEPT: output a report covering three product categories (evergreen / new / churned), each with a tag column, and finally use GROUP BY to count the products in each category.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Set Operations and Combined Queries

1. What You'll Learn

2. The Story

3. Concept

(1) Set Operation Overview

(2) UNION and UNION ALL

▶ Example: UNION Merges Best-Sellers from Three Quarters

▶ Example: UNION ALL Preserves Duplicates

▶ Example: UNION ALL to Merge Multi-Table Stats

(3) INTERSECT and INTERSECT ALL

▶ Example: Find Evergreen Products That Sold Well in All Three Quarters

▶ Example: INTERSECT ALL Preserves Duplicate Counts

▶ Example: INTERSECT Finds Customers Common to Multiple Years

(4) EXCEPT and EXCEPT ALL

▶ Example: Q1 Best-Sellers That Did Not Make Q2 (Churned)

▶ Example: Q2 Newly Promoted Best-Sellers

▶ Example: EXCEPT ALL Preserves Duplicate Counts

4. Key Points

(1) Rules for Set Operations

▶ Example: Unify Column Names with Aliases

(2) ORDER BY Position in Set Operations

▶ Example: Correct ORDER BY

▶ Example: Control Precedence with Parentheses

(3) NULL Handling in Set Operations

▶ Example: NULL Deduplication in UNION

▶ Example: NULL Matching in INTERSECT

(4) Set Operations vs JOIN

▶ Example: INTERSECT Equivalent to INNER JOIN

5. Practice

▶ Example: UNION ALL to Merge Orders and Refund Ledger

▶ Example: EXCEPT Finds Registered but Unactivated Users

▶ Example: INTERSECT Finds Customers Who Bought Both A and B

▶ Example: Three-Year Customer Retention Analysis

▶ Example: UNION ALL + GROUP BY for Trend Summary

6. Comprehensive Example

7. Set Operation Execution Flow

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好