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

Need a 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

Need a 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

Need a 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

Need a hint?

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