Why subqueries nest queries in MySQL

Introduction

Subqueries let you put one query inside another. This helps you get data step-by-step, like solving a puzzle piece by piece.

When you want to find data based on results from another query.

When you need to filter records using a condition that depends on another table.

When you want to compare each row with a set of values from another query.

When you want to calculate a value first and then use it to get final results.

Syntax

MySQL

SELECT column1, column2
FROM table1
WHERE column3 IN (
  SELECT column3
  FROM table2
  WHERE condition
);

Subqueries are placed inside parentheses and act like a small query inside a bigger one.

The outer query uses the result of the inner query to filter or compare data.

Examples

This finds employees who work in departments located in New York.

MySQL

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

This finds products priced higher than the average price of all products.

MySQL

SELECT product_name
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

Sample Program

This example creates two tables, fills them with data, and then finds employees working in New York departments using a subquery.

MySQL

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  location VARCHAR(50)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department_id INT
);

INSERT INTO departments VALUES
(1, 'Sales', 'New York'),
(2, 'HR', 'Chicago'),
(3, 'IT', 'New York');

INSERT INTO employees VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 3),
(4, 'Diana', 1);

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

Important Notes

Subqueries can return one or many rows depending on how they are used.

Using subqueries can make complex filtering easier to understand.

Summary

Subqueries let you nest one query inside another to use its results.

They help break down complex questions into smaller steps.

Subqueries are useful for filtering, comparing, and calculating data based on other data.