Subqueries in WHERE with IN in PostgreSQL - Mini Project: Build & Apply

Using Subqueries in WHERE with IN in PostgreSQL

📖 Scenario: You work at a bookstore that keeps track of books and their authors in two tables. You want to find all books written by authors from a specific country.

🎯 Goal: Build a SQL query using a subquery with IN in the WHERE clause to list all books by authors from 'Canada'.

📋 What You'll Learn

Create a table called authors with columns author_id, name, and country.

Create a table called books with columns book_id, title, and author_id.

Insert the specified data into both tables.

Write a SQL query that selects title from books where author_id is in a subquery selecting author_id from authors where country is 'Canada'.

💡 Why This Matters

🌍 Real World

Bookstores and libraries often need to find books by authors from specific countries or categories. Using subqueries with IN helps filter data efficiently.

💼 Career

Database developers and analysts use subqueries to write complex queries that combine data from multiple tables based on conditions.

1

Create the authors table and insert data

Create a table called authors with columns author_id as integer primary key, name as text, and country as text. Then insert these rows exactly: (1, 'Alice Munro', 'Canada'), (2, 'Gabriel Garcia Marquez', 'Colombia'), (3, 'Margaret Atwood', 'Canada').

PostgreSQL

-- Create the authors table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Create the books table and insert data

Create a table called books with columns book_id as integer primary key, title as text, and author_id as integer. Then insert these rows exactly: (1, 'Dear Life', 1), (2, 'One Hundred Years of Solitude', 2), (3, 'The Handmaid''s Tale', 3).

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO authors (author_id, name, country) VALUES
(1, 'Alice Munro', 'Canada'),
(2, 'Gabriel Garcia Marquez', 'Colombia'),
(3, 'Margaret Atwood', 'Canada');

-- Create the books table and insert data
-- Your code here

Hint

Remember to escape single quotes inside strings by doubling them.

3

Write the subquery to find Canadian authors' IDs

Write a SQL subquery that selects author_id from the authors table where country is 'Canada'. Assign this subquery to a variable called canadian_authors_subquery (for explanation only, actual SQL does not assign variables). Just write the subquery part: (SELECT author_id FROM authors WHERE country = 'Canada').

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO authors (author_id, name, country) VALUES
(1, 'Alice Munro', 'Canada'),
(2, 'Gabriel Garcia Marquez', 'Colombia'),
(3, 'Margaret Atwood', 'Canada');

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'Dear Life', 1),
(2, 'One Hundred Years of Solitude', 2),
(3, 'The Handmaid''s Tale', 3);

-- Write the subquery selecting author_id from authors where country is 'Canada'
-- Your code here

Hint

The subquery must select author_id from authors where country = 'Canada'.

4

Write the main query using the subquery with IN

Write a SQL query that selects title from books where author_id is in the subquery (SELECT author_id FROM authors WHERE country = 'Canada'). This will list all books by Canadian authors.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO authors (author_id, name, country) VALUES
(1, 'Alice Munro', 'Canada'),
(2, 'Gabriel Garcia Marquez', 'Colombia'),
(3, 'Margaret Atwood', 'Canada');

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'Dear Life', 1),
(2, 'One Hundred Years of Solitude', 2),
(3, 'The Handmaid''s Tale', 3);

-- Write the main query selecting title from books where author_id is in the subquery
-- Your code here

Hint

Use IN with the subquery inside the WHERE clause.

Practice

1. What does the IN keyword do in a SQL query when used with a subquery in the WHERE clause?

easy

A. It filters rows where a column's value matches any value returned by the subquery.

B. It joins two tables together based on matching columns.

C. It updates rows in the table with values from the subquery.

D. It deletes rows that do not exist in the subquery.

Solution

1. Step 1: Understand the role of IN with subqueries

   The IN keyword checks if a value exists in a list of values returned by the subquery.

2. Step 2: Identify the filtering effect

   Rows are kept only if their column value matches any value from the subquery's result.

3. Final Answer:

   It filters rows where a column's value matches any value returned by the subquery. -> Option A

4. Quick Check:

   IN filters rows by subquery values [OK]

Hint: Think of IN as 'is in this list' from the subquery [OK]

Common Mistakes:

• Confusing IN with JOIN
• Thinking IN updates or deletes rows
• Assuming IN returns a single value only

2. Which of the following is the correct syntax to use a subquery with IN in PostgreSQL?

easy

A. SELECT * FROM employees WHERE department_id IN (SELECT * FROM departments);

B. SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments);

C. SELECT * FROM employees WHERE department_id IN SELECT id FROM departments;

D. SELECT * FROM employees WHERE department_id = IN (SELECT id FROM departments);

Solution

1. Step 1: Check the correct use of parentheses

   The subquery must be enclosed in parentheses after IN.

2. Step 2: Verify the subquery returns a single column

   The subquery should return one column of values to compare against.

3. Final Answer:

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); -> Option B

4. Quick Check:

   Subquery in parentheses with single column [OK]

Hint: Always put subquery inside parentheses after IN [OK]

Common Mistakes:

• Using = IN instead of just IN
• Omitting parentheses around subquery
• Selecting multiple columns in subquery

3. Given the tables:
employees(id, name, department_id)
departments(id, name)
What will this query return?

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

medium

A. An error because subquery returns multiple rows.

B. Names of all employees regardless of department.

C. Names of employees who do not work in Sales.

D. Names of employees who work in the Sales department.

Solution

1. Step 1: Understand the subquery filtering departments

   The subquery selects department IDs where the department name is 'Sales'.

2. Step 2: Filter employees by matching department IDs

   The main query returns employee names whose department_id matches those IDs.

3. Final Answer:

   Names of employees who work in the Sales department. -> Option D

4. Quick Check:

   IN filters employees by Sales department IDs [OK]

Hint: Subquery filters departments, main query filters employees [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing NOT IN with IN
• Assuming subquery causes error with multiple rows

4. Identify the error in this query:

SELECT * FROM employees WHERE department_id IN SELECT id FROM departments WHERE name = 'HR';

medium

A. Missing parentheses around the subquery after IN.

B. Using IN instead of EXISTS.

C. Subquery returns multiple columns.

D. department_id cannot be compared with id.

Solution

1. Step 1: Check syntax for subquery with IN

   The subquery must be enclosed in parentheses after the IN keyword.

2. Step 2: Identify missing parentheses causing syntax error

   The query lacks parentheses around the subquery, causing invalid syntax.

3. Final Answer:

   Missing parentheses around the subquery after IN. -> Option A

4. Quick Check:

   Subquery must be in parentheses after IN [OK]

Hint: Always wrap subqueries in parentheses after IN [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Confusing IN with EXISTS syntax
• Assuming subquery returns multiple columns

5. You want to find all customers who have placed orders for products in the 'Electronics' category. Given tables:
customers(id, name)
orders(id, customer_id, product_id)
products(id, name, category)
Which query correctly uses a subquery with IN to get these customers?

hard

A. SELECT name FROM customers WHERE id IN (SELECT id FROM products WHERE category = 'Electronics');

B. SELECT name FROM customers WHERE id = IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));

C. SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));

D. SELECT name FROM customers WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM products WHERE category = 'Electronics'));

Solution

1. Step 1: Identify the correct subquery for products in 'Electronics'

   The innermost subquery selects product IDs where category is 'Electronics'.

2. Step 2: Use these product IDs to find matching orders

   The middle subquery selects customer IDs from orders with those product IDs.

3. Step 3: Select customer names whose IDs match those customer IDs

   The main query selects customer names where their ID is in the list from the orders subquery.

4. Final Answer:

   SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics')); -> Option C

5. Quick Check:

   Nested IN subqueries filter customers by product category [OK]

Hint: Use nested IN subqueries to filter step-by-step [OK]

Common Mistakes:

• Using = IN instead of IN
• Mixing up customer_id and product_id columns
• Selecting wrong columns in subqueries