INTERSECT and EXCEPT in PostgreSQL - Mini Project: Build & Apply

Using INTERSECT and EXCEPT in PostgreSQL Queries

📖 Scenario: You work for a bookstore that wants to analyze its inventory and sales data. You have two tables: inventory and sales. You want to find which books are both in inventory and sold, and which books are in inventory but have not been sold yet.

🎯 Goal: Build SQL queries using INTERSECT and EXCEPT to find common and exclusive book titles between inventory and sales.

📋 What You'll Learn

Create two tables: inventory and sales with a title column

Insert specific book titles into both tables

Write a query using INTERSECT to find titles present in both tables

Write a query using EXCEPT to find titles in inventory but not in sales

💡 Why This Matters

🌍 Real World

Bookstores and inventory managers often need to compare lists of items to find overlaps or differences, such as which books are in stock and which have been sold.

💼 Career

Understanding INTERSECT and EXCEPT is useful for data analysts and database developers who write queries to compare datasets and generate reports.

1

Create the inventory and sales tables with book titles

Create two tables called inventory and sales. Each table should have one column called title of type VARCHAR. Then insert these exact titles into inventory: 'The Hobbit', '1984', 'Pride and Prejudice', 'To Kill a Mockingbird'. Insert these exact titles into sales: '1984', 'The Hobbit', 'The Catcher in the Rye'.

PostgreSQL

-- Create tables inventory and sales with title column
-- Insert specified book titles into both tables

Hint

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

2

Set up a query to find common book titles using INTERSECT

Write a SQL query that selects the title from inventory and intersects it with the title from sales to find book titles present in both tables. Use the exact query structure: SELECT title FROM inventory INTERSECT SELECT title FROM sales;

PostgreSQL

CREATE TABLE inventory (title VARCHAR);
CREATE TABLE sales (title VARCHAR);

INSERT INTO inventory (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird');

INSERT INTO sales (title) VALUES
('1984'),
('The Hobbit'),
('The Catcher in the Rye');

-- Write a query using INTERSECT to find common titles
-- Your code here

Hint

Use INTERSECT between two SELECT statements to find common rows.

3

Set up a query to find book titles in inventory but not sold using EXCEPT

Write a SQL query that selects the title from inventory and uses EXCEPT with the title from sales to find book titles that are in inventory but have not been sold. Use the exact query: SELECT title FROM inventory EXCEPT SELECT title FROM sales;

PostgreSQL

CREATE TABLE inventory (title VARCHAR);
CREATE TABLE sales (title VARCHAR);

INSERT INTO inventory (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird');

INSERT INTO sales (title) VALUES
('1984'),
('The Hobbit'),
('The Catcher in the Rye');

SELECT title FROM inventory
INTERSECT
SELECT title FROM sales;

-- Write a query using EXCEPT to find titles in inventory but not in sales
-- Your code here

Hint

Use EXCEPT between two SELECT statements to find rows in the first but not in the second.

4

Complete the project by adding comments explaining the queries

Add comments above each query explaining what it does. For the INTERSECT query, add a comment: -- Find books present in both inventory and sales. For the EXCEPT query, add a comment: -- Find books in inventory not sold yet.

PostgreSQL

CREATE TABLE inventory (title VARCHAR);
CREATE TABLE sales (title VARCHAR);

INSERT INTO inventory (title) VALUES
('The Hobbit'),
('1984'),
('Pride and Prejudice'),
('To Kill a Mockingbird');

INSERT INTO sales (title) VALUES
('1984'),
('The Hobbit'),
('The Catcher in the Rye');

-- Add comments explaining the queries below
-- Your code here

Hint

Use SQL comments starting with -- to explain each query.

Practice

1. What does the INTERSECT operator do in PostgreSQL?

easy

A. Returns rows only in the second query

B. Returns all rows from the first query

C. Combines all rows from both queries without duplicates

D. Returns rows common to both queries

Solution

1. Step 1: Understand INTERSECT purpose

   INTERSECT returns only rows that appear in both query results.

2. Step 2: Compare with other set operations

   Unlike UNION or EXCEPT, INTERSECT finds common rows, not all or unique rows.

3. Final Answer:

   Returns rows common to both queries -> Option D

4. Quick Check:

   INTERSECT = common rows [OK]

Hint: INTERSECT = find common rows between two queries [OK]

Common Mistakes:

• Confusing INTERSECT with UNION
• Thinking INTERSECT returns all rows from first query
• Mixing INTERSECT with EXCEPT

2. Which of the following is the correct syntax to find rows in table1 but not in table2 using EXCEPT?

easy

A. SELECT * FROM table1 INTERSECT SELECT * FROM table2;

B. SELECT * FROM table1 EXCEPT SELECT * FROM table2;

C. SELECT * FROM table1 UNION EXCEPT SELECT * FROM table2;

D. SELECT * FROM table1 EXCEPT ALL SELECT * FROM table2;

Solution

1. Step 1: Identify EXCEPT syntax

   The correct syntax is: SELECT ... EXCEPT SELECT ... to get rows in first query not in second.

2. Step 2: Check options for correct usage

   SELECT * FROM table1 EXCEPT SELECT * FROM table2; matches correct syntax; others misuse INTERSECT, UNION, or EXCEPT ALL (which is valid but different).

3. Final Answer:

   SELECT * FROM table1 EXCEPT SELECT * FROM table2; -> Option B

4. Quick Check:

   EXCEPT syntax = SELECT ... EXCEPT SELECT ... [OK]

Hint: Use EXCEPT between two SELECTs to find rows only in first [OK]

Common Mistakes:

• Using INTERSECT instead of EXCEPT
• Adding UNION incorrectly
• Using EXCEPT ALL when not needed

3. Given these tables:
table_a:
id
1
2
3

table_b:
id
2
3
4

What is the result of:
SELECT id FROM table_a INTERSECT SELECT id FROM table_b;

medium

A. [1, 2, 3]

B. [1]

C. [2, 3]

D. [4]

Solution

1. Step 1: List rows from both tables

   table_a has ids 1, 2, 3; table_b has ids 2, 3, 4.

2. Step 2: Find common ids using INTERSECT

   Common ids are 2 and 3 only.

3. Final Answer:

   [2, 3] -> Option C

4. Quick Check:

   INTERSECT = common rows = [2, 3] [OK]

Hint: INTERSECT returns only ids present in both tables [OK]

Common Mistakes:

• Including ids only in one table
• Confusing INTERSECT with UNION
• Expecting all ids combined

4. What is wrong with this query?

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

medium

A. The number of columns in both SELECTs differ

B. EXCEPT cannot be used with SELECT statements

C. The query is missing a WHERE clause

D. The table names are invalid

Solution

1. Step 1: Check column counts in both SELECTs

   First SELECT returns two columns (id, name), second returns one column (id).

2. Step 2: Understand EXCEPT requirements

   EXCEPT requires both SELECTs to have the same number and compatible types of columns.

3. Final Answer:

   The number of columns in both SELECTs differ -> Option A

4. Quick Check:

   EXCEPT needs equal columns [OK]

Hint: EXCEPT requires same number of columns in both SELECTs [OK]

Common Mistakes:

• Ignoring column count mismatch
• Thinking EXCEPT needs WHERE clause
• Assuming table names cause error

5. You have two tables:
employees with columns id, name
contractors with columns id, name
You want to find people who are either employees or contractors but not both.
Which query correctly returns this list?

hard

A. SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors UNION SELECT id, name FROM contractors EXCEPT SELECT id, name FROM employees;

B. SELECT id, name FROM employees INTERSECT SELECT id, name FROM contractors;

C. SELECT id, name FROM employees UNION SELECT id, name FROM contractors;

D. SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors;

Solution

1. Step 1: Understand the requirement

   We want people in employees or contractors but not in both (symmetric difference).

2. Step 2: Use EXCEPT and UNION to find symmetric difference

   EXCEPT finds those in one table but not the other; UNION combines both exclusive sets.

3. Step 3: Analyze options

   SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors UNION SELECT id, name FROM contractors EXCEPT SELECT id, name FROM employees; correctly combines EXCEPT in both directions with UNION to get symmetric difference.

4. Final Answer:

   SELECT id, name FROM employees EXCEPT SELECT id, name FROM contractors UNION SELECT id, name FROM contractors EXCEPT SELECT id, name FROM employees; -> Option A

5. Quick Check:

   Symmetric difference = EXCEPT + UNION [OK]

Hint: Symmetric difference = (A EXCEPT B) UNION (B EXCEPT A) [OK]

Common Mistakes:

• Using only INTERSECT or UNION
• Using EXCEPT one way only
• Confusing symmetric difference with intersection