Why subqueries nest queries in MySQL - See It in Action

Understanding Why Subqueries Nest Queries in MySQL

📖 Scenario: You are managing a small bookstore database. You want to find books that have a price higher than the average price of all books. This requires using a subquery to calculate the average price first, then using that result to filter books.

🎯 Goal: Build a MySQL query using a subquery that nests one query inside another to find books priced above the average price.

📋 What You'll Learn

Create a table called books with columns id, title, and price

Insert exactly three books with specified prices

Write a subquery to calculate the average price of all books

Write a main query that uses the subquery to find books with price greater than the average

💡 Why This Matters

🌍 Real World

Subqueries are used in real databases to perform complex filtering and calculations in one query, like finding products above average price or customers with orders above average value.

💼 Career

Understanding subqueries is essential for database developers and analysts to write efficient and readable SQL queries for data retrieval and reporting.

1

Create the books table and insert data

Create a table called books with columns id as INT, title as VARCHAR(100), and price as DECIMAL(5,2). Then insert these three rows exactly: (1, 'Book A', 10.00), (2, 'Book B', 15.00), and (3, 'Book C', 20.00).

MySQL

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

Hint

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

2

Write a subquery to calculate average price

Create a subquery that calculates the average price of all books using SELECT AVG(price) FROM books and assign it to a variable called avg_price using a SET statement.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10.00),
(2, 'Book B', 15.00),
(3, 'Book C', 20.00);

-- Set avg_price variable using a subquery
-- Your code here

Hint

Use SET @avg_price = (SELECT AVG(price) FROM books); to store the average price.

3

Write the main query using the subquery

Write a SELECT query to get id, title, and price from books where price is greater than the subquery (SELECT AVG(price) FROM books). Use the exact subquery inside the WHERE clause.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10.00),
(2, 'Book B', 15.00),
(3, 'Book C', 20.00);

SET @avg_price = (SELECT AVG(price) FROM books);

-- Select books with price greater than average using a subquery
-- Your code here

Hint

Use the subquery (SELECT AVG(price) FROM books) directly in the WHERE clause.

4

Explain why subqueries nest queries

Add a comment explaining that subqueries nest queries to allow using the result of one query inside another, like using the average price to filter books.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10.00),
(2, 'Book B', 15.00),
(3, 'Book C', 20.00);

SET @avg_price = (SELECT AVG(price) FROM books);

SELECT id, title, price FROM books WHERE price > (SELECT AVG(price) FROM books);

-- Add a comment explaining why subqueries nest queries
-- Your code here

Hint

Write a comment starting with -- explaining the purpose of nesting queries with subqueries.

Practice

1. Why do we use subqueries in MySQL?
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

easy

A. To nest one query inside another and use its results

B. To speed up the database server hardware

C. To create new tables automatically

D. To change the database schema

Solution

1. Step 1: Understand subquery usage

   The inner query SELECT id FROM departments WHERE location = 'NY' finds department IDs in New York.

2. Step 2: Use subquery results in outer query

   The outer query selects employees whose department_id matches those IDs from the subquery.

3. Final Answer:

   To nest one query inside another and use its results -> Option A

4. Quick Check:

   Subqueries = nested queries using results [OK]

Hint: Subqueries let you use one query's results inside another [OK]

Common Mistakes:

• Thinking subqueries change database structure
• Confusing subqueries with hardware upgrades
• Believing subqueries create tables automatically

2. Which of the following is the correct syntax for a subquery in MySQL?

easy

A. SELECT * FROM table1 WHERE id = SELECT id FROM table2 WHERE condition;

B. SELECT * FROM table1 WHERE id == (SELECT id FROM table2 WHERE condition);

C. SELECT * FROM table1 WHERE id IN SELECT id FROM table2 WHERE condition;

D. SELECT * FROM table1 WHERE id = (SELECT id FROM table2 WHERE condition);

Solution

1. Step 1: Check subquery syntax

   Subqueries must be enclosed in parentheses and used with operators like = or IN.

2. Step 2: Identify correct syntax

   SELECT * FROM table1 WHERE id = (SELECT id FROM table2 WHERE condition); uses parentheses correctly and the = operator, which is valid syntax.

3. Final Answer:

   SELECT * FROM table1 WHERE id = (SELECT id FROM table2 WHERE condition); -> Option D

4. Quick Check:

   Subquery syntax requires parentheses [OK]

Hint: Always put subqueries inside parentheses [OK]

Common Mistakes:

• Using double equals (==) instead of single equals (=)
• Omitting parentheses around subquery
• Using IN without parentheses

3. What will be the output of this query?

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

Given:
- departments table has ids 1,2,3 with locations NY, LA, LA respectively
- employees table has:
(name: Alice, department_id: 1),
(name: Bob, department_id: 2),
(name: Carol, department_id: 3),
(name: Dave, department_id: 4)

medium

A. Bob, Carol

B. Alice, Bob, Carol

C. Alice, Dave

D. Dave only

Solution

1. Step 1: Identify department IDs in LA

   From departments, IDs with location 'LA' are 2 and 3.

2. Step 2: Find employees in those departments

   Employees with department_id 2 and 3 are Bob and Carol.

3. Final Answer:

   Bob, Carol -> Option A

4. Quick Check:

   Subquery filters departments = Bob, Carol [OK]

Hint: Match outer query filter with subquery results [OK]

Common Mistakes:

• Including employees from other departments
• Ignoring subquery filtering
• Assuming all employees are returned

4. Identify the error in this query:

SELECT name FROM employees WHERE department_id = SELECT id FROM departments WHERE location = 'NY';

medium

A. Missing WHERE clause in outer query

B. Using = instead of IN for multiple results

C. Missing parentheses around the subquery

D. Wrong table name 'employees'

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses to be valid.

2. Step 2: Identify missing parentheses

   The query lacks parentheses around SELECT id FROM departments WHERE location = 'NY'.

3. Final Answer:

   Missing parentheses around the subquery -> Option C

4. Quick Check:

   Subqueries need parentheses [OK]

Hint: Always wrap subqueries in parentheses [OK]

Common Mistakes:

• Forgetting parentheses causes syntax error
• Confusing = and IN without checking subquery results
• Assuming table names are incorrect without checking

5. You want to find all employees who earn more than the average salary in their department. Which query correctly uses a subquery to achieve this?

hard

A. SELECT name, salary FROM employees WHERE salary > AVG(salary);

B. SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

C. SELECT name, salary FROM employees WHERE salary > (SELECT salary FROM employees);

D. SELECT name, salary FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

Solution

1. Step 1: Understand the problem

   We want employees earning more than their department's average salary.

2. Step 2: Analyze subquery usage

   SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); uses a correlated subquery that calculates average salary per employee's department.

3. Step 3: Check other options

   SELECT name, salary FROM employees WHERE salary > AVG(salary); lacks grouping, C compares salary to all salaries, D compares to all averages without correlation.

4. Final Answer:

   SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); -> Option B

5. Quick Check:

   Correlated subquery per department = SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); [OK]

Hint: Use correlated subqueries to compare per group [OK]

Common Mistakes:

• Using aggregate functions without grouping
• Not correlating subquery with outer query
• Comparing salary to unrelated values