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?'.

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