Why set operations matter in PostgreSQL - Visual Breakdown

Concept Flow - Why set operations matter

Start with two tables

↓

Choose set operation

↓

UNION: combine unique rows

↓

INTERSECT: common rows only

↓

EXCEPT: rows in first not in second

↓

Execute operation

↓

Get result set

↓

Use result for analysis or display

Set operations combine or compare rows from two tables to get meaningful results like all unique rows, common rows, or differences.

Execution Sample

PostgreSQL

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

This query combines cities from customers and suppliers, showing each city only once.

Execution Table

Step	Action	Input Data	Operation	Output
1	Read cities from customers	customers.city = ['Paris', 'Berlin', 'London']	None	['Paris', 'Berlin', 'London']
2	Read cities from suppliers	suppliers.city = ['Berlin', 'Madrid', 'Rome']	None	['Berlin', 'Madrid', 'Rome']
3	Apply UNION operation	['Paris', 'Berlin', 'London'] and ['Berlin', 'Madrid', 'Rome']	Combine unique rows	['Paris', 'Berlin', 'London', 'Madrid', 'Rome']
4	Return final result	Combined unique cities	Output result set	['Paris', 'Berlin', 'London', 'Madrid', 'Rome']

💡 All unique cities combined, no duplicates, operation complete

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
customers_cities	empty	['Paris', 'Berlin', 'London']	['Paris', 'Berlin', 'London']	['Paris', 'Berlin', 'London']	['Paris', 'Berlin', 'London']
suppliers_cities	empty	empty	['Berlin', 'Madrid', 'Rome']	['Berlin', 'Madrid', 'Rome']	['Berlin', 'Madrid', 'Rome']
result_set	empty	empty	empty	['Paris', 'Berlin', 'London', 'Madrid', 'Rome']	['Paris', 'Berlin', 'London', 'Madrid', 'Rome']

Key Moments - 3 Insights

Why does UNION remove duplicates?

UNION returns only unique rows by default, as shown in execution_table step 3 where 'Berlin' appears once in the output even though it is in both inputs.

What happens if we use UNION ALL instead of UNION?

UNION ALL keeps all rows including duplicates, so the output would include 'Berlin' twice. This differs from the UNION step 3 output.

Why do we need set operations instead of just joining tables?

Set operations combine or compare whole rows from two queries, useful for finding all unique values or differences, unlike joins which combine columns side-by-side.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output after step 3?

A ['Paris', 'Berlin', 'London', 'Berlin', 'Madrid', 'Rome']

B ['Paris', 'Berlin', 'London', 'Madrid', 'Rome']

C ['Berlin', 'Madrid', 'Rome']

D ['Paris', 'London']

💡 Hint

Check the 'Output' column in execution_table row for step 3

At which step do we read cities from suppliers?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column in execution_table for when suppliers data is read

If we replaced UNION with INTERSECT, what would the output be?

A ['Paris', 'Berlin', 'London', 'Madrid', 'Rome']

B ['Paris', 'London']

C ['Berlin']

D ['Madrid', 'Rome']

💡 Hint

INTERSECT returns only rows common to both inputs, check variable_tracker for common cities

Concept Snapshot

Set operations combine results from two queries.
UNION returns unique rows from both.
INTERSECT returns only common rows.
EXCEPT returns rows in first not in second.
Useful for comparing or merging data sets.

Full Transcript

Set operations in SQL let you combine or compare rows from two tables or queries. UNION merges rows and removes duplicates, INTERSECT finds common rows, and EXCEPT finds rows in one set but not the other. For example, combining city lists from customers and suppliers with UNION gives all unique cities. This helps analyze data across tables easily without complex joins. The execution steps show reading each table, applying the set operation, and returning the final result. Understanding these operations helps you write queries that answer questions like 'Which cities do both customers and suppliers have?' or 'Which cities are unique to customers?'.