Subquery with IN operator in SQL - Mini Project: Build & Apply

Using Subquery with IN Operator in SQL

📖 Scenario: You work at a bookstore that keeps two tables: books and orders. The books table has details about each book, and the orders table records which books customers have ordered.You want to find all books that have been ordered at least once.

🎯 Goal: Build an SQL query using a subquery with the IN operator to list all books that appear in the orders.

📋 What You'll Learn

Create a table called books with columns book_id (integer) and title (text).

Create a table called orders with columns order_id (integer) and book_id (integer).

Insert the exact data into books: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird'), (4, 'Moby Dick').

Insert the exact data into orders: (101, 2), (102, 3), (103, 2).

Write a SELECT query to find all books where book_id is in the list of book_ids from orders using a subquery with the IN operator.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use subqueries with IN to find related records across tables, like finding products that have sales.

💼 Career

Knowing how to write subqueries with IN is a fundamental SQL skill for data analysts, database developers, and backend engineers.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns book_id (integer) and title (text). Then insert these exact rows: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird'), and (4, 'Moby Dick').

SQL

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

Hint

Use CREATE TABLE books (book_id INTEGER, title TEXT); to create the table. Use one INSERT INTO books statement with multiple rows.

2

Create the orders table and insert data

Write SQL statements to create a table called orders with columns order_id (integer) and book_id (integer). Then insert these exact rows: (101, 2), (102, 3), and (103, 2).

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird'),
(4, 'Moby Dick');
-- Create the orders table and insert data
-- Your code here

Hint

Use CREATE TABLE orders (order_id INTEGER, book_id INTEGER); to create the table. Use one INSERT INTO orders statement with multiple rows.

3

Write the subquery with IN operator

Write a SELECT query to get all columns from books where book_id is in the list of book_ids from orders. Use a subquery with the IN operator.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird'),
(4, 'Moby Dick');

CREATE TABLE orders (order_id INTEGER, book_id INTEGER);
INSERT INTO orders (order_id, book_id) VALUES
(101, 2),
(102, 3),
(103, 2);

-- Write the SELECT query using IN and subquery
-- Your code here

Hint

Use SELECT * FROM books WHERE book_id IN (SELECT book_id FROM orders); to get books that have orders.

4

Complete the query with ordering

Add an ORDER BY clause to the previous SELECT query to sort the results by title in ascending order.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird'),
(4, 'Moby Dick');

CREATE TABLE orders (order_id INTEGER, book_id INTEGER);
INSERT INTO orders (order_id, book_id) VALUES
(101, 2),
(102, 3),
(103, 2);

SELECT * FROM books WHERE book_id IN (SELECT book_id FROM orders)
-- Add ORDER BY title ascending
-- Your code here

Hint

Add ORDER BY title ASC at the end of the SELECT query to sort by title alphabetically.

Practice

1. What does the IN operator do when used with a subquery in SQL?

easy

A. It checks if a value matches any value returned by the subquery.

B. It updates values in the main query based on the subquery.

C. It deletes rows that are returned by the subquery.

D. It creates a new table from the subquery results.

Solution

1. Step 1: Understand the role of IN operator

   The IN operator compares a value to a list of values and returns true if it matches any of them.

2. Step 2: Understand subquery usage

   The subquery returns a list of values that the main query uses to filter rows with the IN operator.

3. Final Answer:

   It checks if a value matches any value returned by the subquery. -> Option A

4. Quick Check:

   IN with subquery = match any value [OK]

Hint: IN checks if value is inside subquery result list [OK]

Common Mistakes:

• Thinking IN updates or deletes rows
• Confusing IN with JOIN
• Assuming IN creates new tables

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

easy

A. SELECT * FROM employees WHERE IN department_id (SELECT id 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: Review correct IN syntax

   The IN operator must be followed by parentheses enclosing the subquery.

2. Step 2: Check each option

   SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments); correctly uses IN with parentheses and a subquery. Options A, B, and D have syntax errors.

3. Final Answer:

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

4. Quick Check:

   IN syntax = IN (subquery) [OK]

Hint: Use parentheses around subquery after IN [OK]

Common Mistakes:

• Adding = before IN
• Missing parentheses around subquery
• Placing IN before column name

3. Given the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What will this query return?

SELECT name FROM Employees WHERE dept_id IN (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who do not work in the Sales department.

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

C. All employee names regardless of department.

D. An error because subquery returns multiple rows.

Solution

1. Step 1: Understand subquery filtering

   The subquery selects dept_id values where dept_name is 'Sales'.

2. Step 2: Main query filters employees

   The main query selects employee names whose dept_id matches any dept_id from the subquery.

3. Final Answer:

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

4. Quick Check:

   IN filters employees by Sales dept_id [OK]

Hint: IN filters rows matching subquery values [OK]

Common Mistakes:

• Thinking it returns employees outside Sales
• Assuming subquery causes error with multiple rows
• Ignoring subquery filtering condition

4. Identify the error in this query:

SELECT emp_id FROM Employees WHERE dept_id IN SELECT dept_id FROM Departments;

medium

A. Subquery should be in the FROM clause.

B. Using IN instead of EXISTS.

C. dept_id should be compared with = not IN.

D. Missing parentheses around the subquery after IN.

Solution

1. Step 1: Check IN operator syntax

   The IN operator requires the subquery to be enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query lacks parentheses around the subquery, causing a syntax error.

3. Final Answer:

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

4. Quick Check:

   IN needs (subquery) [OK]

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

Common Mistakes:

• Omitting parentheses around subquery
• Confusing IN with EXISTS
• Using = instead of IN for multiple values

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

hard

A. SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

B. SELECT name FROM Customers WHERE customer_id = (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

C. SELECT name FROM Customers WHERE customer_id IN (SELECT product_id FROM Products WHERE category = 'Electronics');

D. SELECT name FROM Customers WHERE customer_id IN (SELECT order_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics'));

Solution

1. Step 1: Understand the relationships

   Customers link to Orders by customer_id; Orders link to Products by product_id.

2. Step 2: Analyze the nested subqueries

   The innermost subquery selects product_ids in 'Electronics'. The middle subquery selects customer_ids from Orders with those product_ids. The outer query selects customer names with those customer_ids.

3. Final Answer:

   SELECT name FROM Customers WHERE customer_id IN (SELECT customer_id FROM Orders WHERE product_id IN (SELECT product_id FROM Products WHERE category = 'Electronics')); -> Option A

4. Quick Check:

   Nested IN filters customers by Electronics orders [OK]

Hint: Use nested IN for multi-level filtering [OK]

Common Mistakes:

• Using = instead of IN for multiple customer_ids
• Comparing customer_id with product_id or order_id
• Missing nested subquery for product filtering