Why set operations matter in PostgreSQL - Why It Works This Way

Overview - Why set operations matter

What is it?

Set operations in databases are commands that combine results from two or more queries into a single result. They allow you to find common data, unique data, or all data from multiple sets. These operations include UNION, INTERSECT, and EXCEPT, which work like combining or comparing lists of information. They help organize and analyze data efficiently without complex coding.

Why it matters

Without set operations, combining or comparing data from different tables or queries would be slow and complicated. Imagine trying to find shared friends between two groups without a quick way to compare lists. Set operations solve this by letting databases quickly merge or filter data sets, saving time and reducing errors. This makes data analysis faster and more reliable in real-world applications like reporting, data cleaning, and decision-making.

Where it fits

Before learning set operations, you should understand basic SQL queries like SELECT, WHERE, and JOIN. After mastering set operations, you can explore advanced topics like subqueries, window functions, and query optimization. Set operations fit in the middle of your SQL learning journey as a powerful tool for combining and filtering data.

Mental Model

Core Idea

Set operations let you combine or compare whole groups of data like merging or filtering lists to get exactly what you want.

Think of it like...

Imagine you have two guest lists for a party. UNION is like inviting everyone from both lists, INTERSECT is inviting only those who appear on both lists, and EXCEPT is inviting those on the first list but not on the second.

┌─────────────┐   ┌─────────────┐
│   Set A     │   │   Set B     │
│ {1,2,3,4}  │   │ {3,4,5,6}  │
└─────┬───────┘   └─────┬───────┘
      │ UNION           │ INTERSECT
      ▼                 ▼
  {1,2,3,4,5,6}     {3,4}

      │ EXCEPT
      ▼
  {1,2}

Build-Up - 7 Steps

1

Foundation

Understanding Basic SQL Queries

Concept: Learn how to retrieve data from a single table using SELECT statements.

In SQL, the SELECT statement lets you pick columns and rows from one table. For example, SELECT name FROM employees; gets all employee names. This is the foundation before combining data from multiple sources.

Result

A list of employee names from the employees table.

Knowing how to get data from one table is essential before learning how to combine data from multiple tables or queries.

2

Foundation

Introduction to Data Sets as Lists

Concept: Think of query results as lists or sets of data you can compare or combine.

When you run a query, the result is like a list of items. For example, a list of product IDs or customer emails. Understanding this helps you see how set operations work by combining or comparing these lists.

Result

A mental image of query results as lists of items.

Seeing query results as lists prepares you to understand how set operations merge or filter these lists.

3

Intermediate

Using UNION to Combine Results

🤔Before reading on: do you think UNION includes duplicates or removes them? Commit to your answer.

Concept: UNION combines two query results into one list without duplicates.

UNION takes all rows from two queries and merges them, removing any duplicates. For example, combining two customer lists to get all unique customers. Syntax: SELECT column FROM table1 UNION SELECT column FROM table2;

Result

A combined list of unique rows from both queries.

Understanding that UNION removes duplicates helps you avoid counting the same data twice.

4

Intermediate

Finding Common Data with INTERSECT

🤔Before reading on: does INTERSECT return all data or only shared data? Commit to your answer.

Concept: INTERSECT returns only rows that appear in both query results.

INTERSECT finds the overlap between two queries. For example, customers who bought both product A and product B. Syntax: SELECT column FROM table1 INTERSECT SELECT column FROM table2;

Result

A list of rows common to both queries.

Knowing INTERSECT helps you find shared data quickly without manual comparison.

5

Intermediate

Excluding Data with EXCEPT

🤔Before reading on: does EXCEPT return rows in the first or second query? Commit to your answer.

Concept: EXCEPT returns rows from the first query that are not in the second.

EXCEPT filters out rows found in the second query from the first. For example, customers who bought product A but not product B. Syntax: SELECT column FROM table1 EXCEPT SELECT column FROM table2;

Result

A list of rows unique to the first query.

Understanding EXCEPT helps you exclude unwanted data efficiently.

6

Advanced

Set Operations with Different Column Types

🤔Before reading on: do you think set operations work if columns differ in type or number? Commit to your answer.

Concept: Set operations require queries to have the same number and compatible types of columns.

When using UNION, INTERSECT, or EXCEPT, both queries must select the same number of columns with matching data types. For example, SELECT id, name FROM table1 UNION SELECT id, name FROM table2; works, but SELECT id FROM table1 UNION SELECT name FROM table2; does not.

Result

Queries that run successfully only if column counts and types match.

Knowing this prevents errors and ensures meaningful combined results.

7

Expert

Performance and Optimization of Set Operations

🤔Before reading on: do you think set operations always perform fast regardless of data size? Commit to your answer.

Concept: Set operations can be costly on large data; understanding indexing and query plans helps optimize them.

Databases process set operations by scanning and comparing rows, which can be slow on big tables. Using indexes on involved columns and analyzing query plans can speed up these operations. Also, UNION ALL can be used when duplicates are allowed to improve performance.

Result

Faster query execution and efficient resource use in production.

Understanding performance helps you write scalable queries and avoid slowdowns in real systems.

Under the Hood

Set operations work by executing each query separately, then combining their results according to the operation. For UNION and INTERSECT, the database compares rows to remove duplicates or find common rows. For EXCEPT, it filters out rows from the first set that appear in the second. Internally, this involves sorting or hashing to efficiently compare rows.

Why designed this way?

Set operations were designed to simplify combining and comparing data sets without complex joins or manual filtering. Early databases needed a clear, standard way to handle these common tasks. Sorting and hashing were chosen for efficiency and to handle large data sets reliably.

┌───────────────┐       ┌───────────────┐
│   Query 1     │       │   Query 2     │
│  Result Set A │       │  Result Set B │
└───────┬───────┘       └───────┬───────┘
        │                       │
        ├────────────┬──────────┤
        │            │          │
   ┌────▼────┐  ┌────▼────┐ ┌───▼────┐
   │ UNION   │  │INTERSECT│ │ EXCEPT │
   └────┬────┘  └────┬────┘ └───┬────┘
        │            │          │
   ┌────▼────────────▼──────────▼─────┐
   │         Combined Result Set       │
   └───────────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does UNION include duplicate rows from both queries? Commit to yes or no.

Common Belief: UNION just stacks two query results without removing duplicates.

Reality: UNION removes duplicate rows, returning only unique rows from both queries combined.

Why it matters: Assuming UNION keeps duplicates can lead to counting the same data multiple times, causing wrong results in reports.

Quick: Does INTERSECT return rows unique to each query or only shared rows? Commit to your answer.

Common Belief: INTERSECT returns all rows from both queries combined.

Reality: INTERSECT returns only rows that appear in both query results.

Why it matters: Misunderstanding INTERSECT can cause missing data or incorrect filtering when trying to find common elements.

Quick: Can set operations combine queries with different numbers of columns? Commit yes or no.

Common Belief: You can UNION or INTERSECT queries with different column counts or types.

Reality: Set operations require the same number of columns with compatible data types in both queries.

Why it matters: Ignoring this causes syntax errors and confusion, wasting time debugging.

Quick: Does EXCEPT return rows from the second query or the first? Commit your answer.

Common Belief: EXCEPT returns rows found in the second query but not in the first.

Reality: EXCEPT returns rows from the first query that are not in the second.

Why it matters: Reversing EXCEPT logic leads to wrong data exclusion, affecting data accuracy.

Expert Zone

1

Set operations implicitly remove duplicates except when using UNION ALL, which preserves duplicates and improves performance.

2

The order of rows in set operation results is not guaranteed unless you add an ORDER BY at the end of the combined query.

3

Set operations compare entire rows, so even a small difference in any column causes rows to be treated as distinct.

When NOT to use

Avoid set operations when you need to combine data with different structures or when you want to join related columns side-by-side; use JOINs instead. For large datasets where duplicates are allowed, prefer UNION ALL over UNION for better performance.

Production Patterns

In real systems, set operations are used for data deduplication, finding overlaps between customer segments, and filtering out processed records. They often appear in reporting queries, ETL pipelines, and data validation scripts.

Connections

Set Theory (Mathematics)

Set operations in SQL directly implement mathematical set theory concepts like union, intersection, and difference.

Understanding mathematical sets clarifies why SQL set operations behave the way they do and helps predict their results.

Data Cleaning

Set operations help identify duplicates, missing data, or mismatches between datasets during cleaning.

Knowing set operations enables efficient data cleaning by quickly comparing and filtering datasets.

Version Control Systems

Version control uses similar concepts to set operations when merging branches or finding common commits.

Recognizing this connection helps understand how merging and conflict resolution work in software development.

Common Pitfalls

#1 Using UNION when duplicates are allowed and performance matters.

Wrong approach:

SELECT id FROM table1 UNION SELECT id FROM table2;

Correct approach:

SELECT id FROM table1 UNION ALL SELECT id FROM table2;

Root cause:

Not realizing UNION removes duplicates and does extra work, slowing queries when duplicates are acceptable.

#2 Trying to UNION queries with different numbers of columns.

Wrong approach:

SELECT id, name FROM table1 UNION SELECT id FROM table2;

Correct approach:

SELECT id, name FROM table1 UNION SELECT id, name FROM table2;

Root cause:

Misunderstanding that set operations require matching column counts and types.

#3 Assuming EXCEPT returns rows from the second query instead of the first.

Wrong approach:

SELECT id FROM table1 EXCEPT SELECT id FROM table2; -- expecting rows in table2 not in table1

Correct approach:

SELECT id FROM table2 EXCEPT SELECT id FROM table1; -- to get rows in table2 not in table1

Root cause:

Confusing the direction of EXCEPT operation.

Key Takeaways

Set operations combine or compare whole query results like lists to get unique, shared, or excluded data.

UNION merges results removing duplicates, INTERSECT finds common rows, and EXCEPT excludes rows from the first query found in the second.

Queries combined with set operations must have the same number and compatible types of columns.

Using set operations correctly simplifies complex data analysis and improves query clarity and performance.

Understanding set operations connects SQL to math and real-world tasks like data cleaning and version control.

Practice

1. What is the main purpose of set operations like UNION and INTERSECT in PostgreSQL?

easy

A. To delete rows from multiple tables

B. To create new tables from existing ones

C. To update data in a table

D. To combine or compare results from multiple SELECT queries

Solution

1. Step 1: Understand set operations

   Set operations like UNION and INTERSECT combine or compare results from multiple SELECT queries.

2. Step 2: Identify their purpose

   They help find unique, common, or different data across queries without modifying tables.

3. Final Answer:

   To combine or compare results from multiple SELECT queries -> Option D

4. Quick Check:

   Set operations = combine/compare SELECT results [OK]

Hint: Set operations combine query results, not modify tables [OK]

Common Mistakes:

• Thinking set operations modify tables
• Confusing set operations with DML commands
• Assuming set operations create new tables

2. Which of the following is the correct syntax to combine two SELECT queries using UNION in PostgreSQL?

easy

A. SELECT col1 FROM table1 JOIN SELECT col1 FROM table2;

B. SELECT col1 FROM table1 UNION SELECT col1 FROM table2;

C. SELECT col1 FROM table1 + SELECT col1 FROM table2;

D. SELECT col1 FROM table1 INTERSECT SELECT col1 FROM table2;

Solution

1. Step 1: Recall UNION syntax

   The correct syntax uses UNION keyword between two SELECT statements with matching columns.

2. Step 2: Check options

   Only SELECT col1 FROM table1 UNION SELECT col1 FROM table2; is correct. JOIN is for table joins; + is invalid syntax; INTERSECT is a different operation.

3. Final Answer:

   SELECT col1 FROM table1 UNION SELECT col1 FROM table2; -> Option B

4. Quick Check:

   UNION syntax = SELECT ... UNION SELECT ... [OK]

Hint: UNION joins SELECTs with UNION keyword, no + or JOIN [OK]

Common Mistakes:

• Using + instead of UNION
• Confusing JOIN with UNION
• Mixing INTERSECT with UNION syntax

3. Given these tables:
table1: (id) values (1), (2), (3);
table2: (id) values (2), (3), (4);
What is the result of this query?

SELECT id FROM table1 INTERSECT SELECT id FROM table2;

medium

A. [2, 3]

B. [1, 2, 3, 4]

C. [1]

D. [4]

Solution

1. Step 1: Understand INTERSECT operation

   INTERSECT returns only values present in both queries.

2. Step 2: Compare table1 and table2 ids

   table1 has 1,2,3; table2 has 2,3,4; common values are 2 and 3.

3. Final Answer:

   [2, 3] -> Option A

4. Quick Check:

   INTERSECT = common values [OK]

Hint: INTERSECT returns only common rows between queries [OK]

Common Mistakes:

• Including all values from both tables
• Confusing INTERSECT with UNION
• Returning values only from one table

4. Consider this query:

SELECT id FROM table1 UNION ALL SELECT id FROM table2;

It returns duplicate rows. How can you fix it to return only unique rows?

medium

A. Replace UNION ALL with UNION

B. Add DISTINCT inside each SELECT

C. Use INTERSECT instead of UNION ALL

D. Add GROUP BY id at the end

Solution

1. Step 1: Understand UNION ALL vs UNION

   UNION ALL returns all rows including duplicates; UNION removes duplicates.

2. Step 2: Fix query to remove duplicates

   Replacing UNION ALL with UNION removes duplicates automatically.

3. Final Answer:

   Replace UNION ALL with UNION -> Option A

4. Quick Check:

   UNION removes duplicates, UNION ALL does not [OK]

Hint: Use UNION (not UNION ALL) to remove duplicates [OK]

Common Mistakes:

• Using UNION ALL when duplicates are unwanted
• Adding DISTINCT inside each SELECT unnecessarily
• Using INTERSECT which returns only common rows

5. You want to find all unique customer IDs who made purchases in either 2022 or 2023 from two tables:
purchases_2022(customer_id) and purchases_2023(customer_id).
Which query correctly returns this list without duplicates?

hard

A. SELECT customer_id FROM purchases_2022 UNION ALL SELECT customer_id FROM purchases_2023;

B. SELECT customer_id FROM purchases_2022 INTERSECT SELECT customer_id FROM purchases_2023;

C. SELECT customer_id FROM purchases_2022 UNION SELECT customer_id FROM purchases_2023;

D. SELECT DISTINCT customer_id FROM purchases_2022, purchases_2023;

Solution

1. Step 1: Understand the goal

   We want all unique customer IDs from either year, combined without duplicates.

2. Step 2: Evaluate options

   SELECT customer_id FROM purchases_2022 INTERSECT SELECT customer_id FROM purchases_2023; returns only customers in both years (common). SELECT customer_id FROM purchases_2022 UNION SELECT customer_id FROM purchases_2023; returns all unique customers from both years. SELECT customer_id FROM purchases_2022 UNION ALL SELECT customer_id FROM purchases_2023; returns all including duplicates. SELECT DISTINCT customer_id FROM purchases_2022, purchases_2023; creates a cross join, not union.

3. Final Answer:

   SELECT customer_id FROM purchases_2022 UNION SELECT customer_id FROM purchases_2023; -> Option C

4. Quick Check:

   UNION = unique combined rows [OK]

Hint: Use UNION to combine unique rows from multiple tables [OK]

Common Mistakes:

• Using INTERSECT to get all customers
• Using UNION ALL causing duplicates
• Using cross join instead of union