Subqueries with IN operator in MySQL - Mini Project: Build & Apply

Using Subqueries with IN Operator in MySQL

📖 Scenario: You work for a small bookstore that keeps track of books and their authors in two tables. You want to find all books written by authors who have published more than one book.

🎯 Goal: Create a MySQL query using a subquery with the IN operator to list all books by authors who have more than one book in the database.

📋 What You'll Learn

Create a table called authors with columns author_id (integer) and author_name (varchar).

Create a table called books with columns book_id (integer), title (varchar), and author_id (integer).

Insert exactly 5 authors into the authors table with these author_id values: 1, 2, 3, 4, 5.

Insert exactly 7 books into the books table with these exact titles and author_ids:

'The Lost World', 1, 'Dinosaur Tales', 1, 'Ocean Depths', 2, 'Mountain High', 3, 'City Lights', 3, 'Desert Winds', 4, 'Forest Whisper', 5.

Write a subquery that finds author_id values for authors with more than one book.

Use the IN operator with the subquery to select all books by those authors.

💡 Why This Matters

🌍 Real World

Bookstores and libraries often need to find books by authors with multiple works to create special collections or promotions.

💼 Career

Understanding subqueries and the IN operator is essential for database querying roles, data analysis, and backend development.

1

Create tables and insert authors

Create a table called authors with columns author_id as integer primary key and author_name as varchar(50). Then insert these authors exactly: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'Diana'), (5, 'Evan').

MySQL

-- Create authors table and insert 5 authors
-- Your code here

Hint

Use CREATE TABLE to make the authors table. Use INSERT INTO with multiple rows to add authors.

2

Create books table and insert books

Create a table called books with columns book_id as integer primary key, title as varchar(100), and author_id as integer. Then insert these books exactly with their author_ids: (1, 'The Lost World', 1), (2, 'Dinosaur Tales', 1), (3, 'Ocean Depths', 2), (4, 'Mountain High', 3), (5, 'City Lights', 3), (6, 'Desert Winds', 4), (7, 'Forest Whisper', 5).

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana'),
(5, 'Evan');

-- Create books table and insert 7 books
-- Your code here

Hint

Use CREATE TABLE to make the books table with the right columns. Use INSERT INTO with multiple rows to add books.

3

Write subquery to find authors with multiple books

Write a subquery that selects author_id from the books table where the count of books per author is more than 1. Use GROUP BY author_id and HAVING COUNT(*) > 1.

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana'),
(5, 'Evan');

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'The Lost World', 1),
(2, 'Dinosaur Tales', 1),
(3, 'Ocean Depths', 2),
(4, 'Mountain High', 3),
(5, 'City Lights', 3),
(6, 'Desert Winds', 4),
(7, 'Forest Whisper', 5);

-- Write subquery to find author_ids with more than one book
-- Your code here

Hint

Use GROUP BY author_id to group books by author. Use HAVING COUNT(*) > 1 to find authors with more than one book.

4

Use subquery with IN operator to select books by those authors

Write a query to select title and author_id from books where author_id is in the subquery that finds authors with more than one book. Use the IN operator with the subquery from Step 3.

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana'),
(5, 'Evan');

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'The Lost World', 1),
(2, 'Dinosaur Tales', 1),
(3, 'Ocean Depths', 2),
(4, 'Mountain High', 3),
(5, 'City Lights', 3),
(6, 'Desert Winds', 4),
(7, 'Forest Whisper', 5);

SELECT author_id FROM books
GROUP BY author_id
HAVING COUNT(*) > 1;

-- Select titles and author_ids from books where author_id is in the subquery
-- Your code here

Hint

Use WHERE author_id IN (subquery) to select books by authors with multiple books.

Practice

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

easy

A. Checks if a value matches any value returned by the subquery

B. Joins two tables based on a common column

C. Updates values in the main table using subquery results

D. Deletes rows that match the subquery results

Solution

1. Step 1: Understand the IN operator

   The IN operator checks if a value exists within a list of values.

2. Step 2: Role of subquery with IN

   The subquery returns a list of values, and IN checks if the main query's value matches any of these.

3. Final Answer:

   Checks if a value matches any value returned by the subquery -> Option A

4. Quick Check:

   IN with subquery = value match list [OK]

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

Common Mistakes:

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

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

easy

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

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

C. SELECT * FROM employees WHERE IN department_id (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. Others have syntax errors.

3. Final Answer:

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

4. Quick Check:

   IN + (subquery) = correct syntax [OK]

Hint: Use IN followed by parentheses around subquery [OK]

Common Mistakes:

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

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 work in the Sales department

D. Names of employees who do not work in Sales

Solution

1. Step 1: Analyze subquery

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

2. Step 2: Main query filters employees

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

3. Final Answer:

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

4. Quick Check:

   IN filters employees by Sales department IDs [OK]

Hint: Subquery filters IDs; IN matches employees with those IDs [OK]

Common Mistakes:

• Thinking IN causes error with multiple rows
• Ignoring subquery filter condition
• Assuming it returns all employees

4. Identify the error in this query:

SELECT * FROM orders WHERE customer_id IN SELECT id FROM customers WHERE city = 'New York';

medium

A. Subquery returns no rows

B. Incorrect table name 'orders'

C. Using IN instead of EXISTS

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 after IN, causing 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
• Assuming table names cause error

5. You want to find all products that have been ordered by customers from the city 'Chicago'.
Given tables:
products(id, name)
orders(id, product_id, customer_id)
customers(id, city)
Which query correctly uses a subquery with IN to get the product names?

hard

A. SELECT name FROM products WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Chicago'));

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

C. SELECT name FROM products WHERE id IN SELECT product_id FROM orders WHERE customer_id IN SELECT id FROM customers WHERE city = 'Chicago';

D. SELECT name FROM products WHERE id IN (SELECT id FROM customers WHERE city = 'Chicago');

Solution

1. Step 1: Understand the relationships

   Products link to orders by product_id; orders link to customers by customer_id.

2. Step 2: Build nested subqueries

   Inner subquery gets customer IDs from Chicago; middle subquery gets product_ids from orders by those customers; outer query selects product names with those IDs.

3. Final Answer:

   SELECT name FROM products WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Chicago')); -> Option A

4. Quick Check:

   Nested IN subqueries correctly filter products [OK]

Hint: Use nested IN subqueries to filter related tables stepwise [OK]

Common Mistakes:

• Adding = before IN
• Missing parentheses around subqueries
• Using wrong columns in subqueries