? MySQL Subqueries

? Quick Overview

Subqueries (also called inner queries or nested queries) are queries nested inside another SQL query. They allow you to break complex problems into smaller steps — for example, selecting rows based on aggregated values, checking existence, or computing derived values.

? Key Concepts

Single-row vs Multi-row — Subqueries can return a single value or multiple rows/columns.
Correlated vs Uncorrelated — Correlated subqueries reference the outer query and run per row; uncorrelated run once.
Placement — Subqueries can appear in SELECT, FROM (as derived tables), WHERE, HAVING, and INSERT/UPDATE statements.
Performance — Use indexes and prefer JOINs for large datasets when possible; correlated subqueries can be costly.

⚙️ Syntax / Theory

Basic forms:

WHERE ... IN (subquery) — tests membership.
WHERE ... = (subquery) — expects single value.
EXISTS (subquery) — checks existence; efficient for correlated checks.
FROM (subquery) AS alias — derived table used like a normal table.

? Code Example(s)

? View Code Example

-- Get customers who have placed at least one order
SELECT customer_id, name
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
);

? View Code Example

-- Correlated subquery: mark employees whose salary > average salary in their department
SELECT e.id, e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);

? View Code Example

-- Use EXISTS to test for related rows (often faster for correlated checks)
SELECT p.id, p.title
FROM posts p
WHERE EXISTS (
SELECT 1
FROM comments c
WHERE c.post_id = p.id
);

? View Code Example

-- Derived table (subquery in FROM) to compute totals per customer, then join
SELECT t.customer_id, t.total_orders
FROM (
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
) AS t
WHERE t.total_orders > 5;

? View Code Example

-- Insert results of a query into another table
INSERT INTO vip_customers (customer_id, name)
SELECT id, name
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10
);

? Live Output / Explanation

Example explanation

The IN (subquery) example returns all customers whose id appears in the list of customer IDs from orders. The correlated example calculates the department average per row and compares each employee's salary to that average. Use EXISTS when you only need to check presence — the subquery can often short-circuit.

? Use Cases

Filtering rows based on aggregated values
Checking existence of related records (EXISTS)
Creating derived tables for reporting
Inserting/updating data based on query results

? Tips & Best Practices

Prefer JOINs for large datasets when you need to combine rows — they are often faster and easier to optimize.
Use EXISTS for correlated checks that only require presence.
When using subqueries in SELECT, ensure they return a single value per row (or use aggregation).
Index the columns used in subquery predicates (e.g., orders.customer_id).
Avoid correlated subqueries on very large tables unless necessary — try to rewrite as a JOIN or derived table.

? Try It Yourself / Practice Tasks

Create a query to find products that were never ordered using a subquery.
Write a correlated subquery that finds employees whose hire date is earlier than the average hire date of their department.
Rewrite a correlated subquery as a JOIN and compare execution plans.
Use a derived table to compute monthly sales and filter months with sales > X.