MySQL Subqueries

A subquery is a SELECT statement written inside another SQL statement. The outer statement is called the main query or outer query, and the inner one is the subquery or inner query. Subqueries are always enclosed in parentheses.

Subqueries let you use the result of one query as an input to another — filtering by a computed value, checking membership in a list, or treating a query result as an inline table.

---

Subquery in WHERE — Scalar Subquery

A scalar subquery returns exactly one value (one row, one column). This value is then used directly in a comparison:

-- Products priced above the average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

MySQL first runs the inner query to get the average price, then uses that number in the outer WHERE clause. The inner query runs once and its result is reused.

---

Subquery in WHERE — IN Subquery

When a subquery returns a list of values (one column, multiple rows), you can test membership with IN:

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

The inner query returns a list of customer IDs that appear in the orders table. The outer query then filters customers whose id is in that list.

-- Customers who have never placed an order
SELECT name, city
FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL);

---

Subquery in FROM — Derived Table

A subquery in the FROM clause acts as an inline table (also called a derived table). You must give it an alias:

-- Average of per-customer order totals
SELECT AVG(customer_total) AS avg_spend
FROM (
    SELECT customer_id, SUM(amount) AS customer_total
    FROM orders
    GROUP BY customer_id
) AS totals;

The inner query groups orders and sums each customer's total. The outer query then averages those totals — something that cannot be done with a single AVG() and GROUP BY in one step.

---

Subquery in SELECT — Inline Value

A scalar subquery can appear in the SELECT list to compute an extra column for each row:

-- Each product with the overall average price alongside it
SELECT name, price,
    (SELECT ROUND(AVG(price), 2) FROM products) AS avg_price
FROM products
ORDER BY price DESC;

name	price	avg_price
Laptop	75000.00	28750.00
Phone	35000.00	28750.00
Headphones	12000.00	28750.00
Mouse	2000.00	28750.00

---

Correlated Subquery

A correlated subquery references a column from the outer query. This means it cannot run independently — it re-executes once for each row the outer query processes:

-- Customers whose total spend is above the overall average order amount
SELECT c.name,
    (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id = c.id) AS total_spent
FROM customers c
WHERE (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id = c.id) > 3000;

---

Key Points to Remember

• A subquery is a SELECT inside another SQL statement, always wrapped in parentheses.
• A scalar subquery returns one value and can be used in comparisons.
• An IN subquery returns a list of values to filter with IN or NOT IN.
• A derived table is a subquery in the FROM clause — it must have an alias.
• A correlated subquery references the outer query and reruns for each outer row — use with care on large tables.
• With NOT IN, always filter out NULL values from the inner query to avoid unexpected empty results.