Why set operations matter in PostgreSQL - See It in Action

Why Set Operations Matter in SQL

📖 Scenario: You work for a small bookstore that keeps track of books sold in two different branches. You want to find out which books were sold in either branch, which books were sold in both branches, and which books were unique to each branch.

🎯 Goal: Build SQL queries using set operations to find the union, intersection, and difference of books sold in two branches.

📋 What You'll Learn

Create two tables named branch1_sales and branch2_sales with a single column book_title.

Insert exact book titles into both tables as specified.

Write a query using UNION to find all unique books sold in either branch.

Write a query using INTERSECT to find books sold in both branches.

Write a query using EXCEPT to find books sold in branch1 but not in branch2.

💡 Why This Matters

🌍 Real World

Set operations help combine and compare data from different sources, like sales from multiple store branches.

💼 Career

Understanding set operations is essential for data analysts and database developers to write efficient queries that summarize and compare datasets.

1

Create tables and insert data

Create two tables called branch1_sales and branch2_sales each with one column book_title of type TEXT. Insert these exact book titles into branch1_sales: 'The Hobbit', '1984', 'Dune'. Insert these exact book titles into branch2_sales: 'Dune', 'The Catcher in the Rye', '1984'.

PostgreSQL

-- Create tables branch1_sales and branch2_sales
-- Insert specified book titles into each table

Hint

Use CREATE TABLE to make tables and INSERT INTO to add the exact book titles.

2

Set up a query to find all unique books sold

Write a SQL query that uses UNION to find all unique book titles sold in either branch1_sales or branch2_sales. Select the column book_title from both tables and combine them with UNION.

PostgreSQL

CREATE TABLE branch1_sales (book_title TEXT);
CREATE TABLE branch2_sales (book_title TEXT);

INSERT INTO branch1_sales (book_title) VALUES
('The Hobbit'),
('1984'),
('Dune');

INSERT INTO branch2_sales (book_title) VALUES
('Dune'),
('The Catcher in the Rye'),
('1984');

-- Write a query using UNION to find all unique books sold

Hint

Use SELECT book_title FROM branch1_sales UNION SELECT book_title FROM branch2_sales to combine unique titles.

3

Find books sold in both branches

Write a SQL query that uses INTERSECT to find book titles sold in both branch1_sales and branch2_sales. Select book_title from both tables and combine them with INTERSECT.

PostgreSQL

CREATE TABLE branch1_sales (book_title TEXT);
CREATE TABLE branch2_sales (book_title TEXT);

INSERT INTO branch1_sales (book_title) VALUES
('The Hobbit'),
('1984'),
('Dune');

INSERT INTO branch2_sales (book_title) VALUES
('Dune'),
('The Catcher in the Rye'),
('1984');

SELECT book_title FROM branch1_sales
UNION
SELECT book_title FROM branch2_sales;

-- Write a query using INTERSECT to find books sold in both branches

Hint

Use INTERSECT to find common book titles in both tables.

4

Find books unique to branch1

Write a SQL query that uses EXCEPT to find book titles sold in branch1_sales but not in branch2_sales. Select book_title from branch1_sales and subtract those in branch2_sales using EXCEPT.

PostgreSQL

CREATE TABLE branch1_sales (book_title TEXT);
CREATE TABLE branch2_sales (book_title TEXT);

INSERT INTO branch1_sales (book_title) VALUES
('The Hobbit'),
('1984'),
('Dune');

INSERT INTO branch2_sales (book_title) VALUES
('Dune'),
('The Catcher in the Rye'),
('1984');

SELECT book_title FROM branch1_sales
UNION
SELECT book_title FROM branch2_sales;

SELECT book_title FROM branch1_sales
INTERSECT
SELECT book_title FROM branch2_sales;

-- Write a query using EXCEPT to find books sold only in branch1

Hint

Use EXCEPT to find books in branch1 that are not in branch2.

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