SQL Subqueries

A subquery is a query inside another query. It lets you use one result as an input to the next question: “employees who earn more than the average”, “customers who placed an order in March”, “the average order value per customer”. This lesson shows the three places a subquery can go, and runs each one step by step.

Three places for a subquery

The inner query is always written in parentheses.

Sample data

Scalar subquery: one value

Employees who earn more than the company average:

SELECT first_name, salaryFROM employeesWHERE salary > (SELECT AVG(salary) FROM employees)ORDER BY salary DESC;

Why not WHERE salary > AVG(salary)? Because aggregates are not allowed in WHERE; the subquery computes the average separately.

SELECT first_name FROM employees WHERE salary > AVG(salary);

ERROR 1111 (HY000): Invalid use of group function

The highest-paid person in Engineering:

SELECT first_name, salaryFROM employeesWHERE dept_id = 1  AND salary = (SELECT MAX(salary) FROM employees WHERE dept_id = 1);

IN subquery: a list of values

Customers who have placed at least one delivered order:

SELECT customer_id, customer_nameFROM customersWHERE customer_id IN (  SELECT customer_id FROM orders WHERE status = 'Delivered')ORDER BY customer_id;

NOT IN asks the opposite question. Products that have never been ordered:

SELECT product_nameFROM productsWHERE product_id NOT IN (SELECT product_id FROM order_items);

Subquery in FROM: derived tables

A subquery in FROM produces a temporary table that the outer query reads. It must have an alias. Average order value per customer:

SELECT t.customer_id,       COUNT(*)           AS orders,       ROUND(AVG(t.total)) AS avg_order_valueFROM (  SELECT o.order_id, o.customer_id, SUM(oi.quantity * p.price) AS total  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  GROUP BY o.order_id, o.customer_id) AS tGROUP BY t.customer_idORDER BY avg_order_value DESC;

Subquery in SELECT

Show each salary next to the department average:

SELECT e.first_name, e.dept_id, e.salary,       (SELECT ROUND(AVG(x.salary))          FROM employees x         WHERE x.dept_id = e.dept_id) AS dept_avgFROM employees eWHERE e.dept_id IN (1, 2)ORDER BY e.dept_id, e.salary DESC;

Subquery in HAVING

Departments whose average salary is above the company average:

SELECT dept_id, ROUND(AVG(salary)) AS avg_salaryFROM employeesWHERE dept_id IS NOT NULLGROUP BY dept_idHAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Subquery or JOIN?

Many subqueries can be rewritten as joins, and MySQL 8 often converts them internally anyway. Choose for clarity:

• “rows that have / do not have a match” reads well as IN, EXISTS or NOT EXISTS;
• when you need columns from the other table, use a join;
• when a query has several steps, a CTE is easier to read than nested subqueries.

SELECT product_name, priceFROM productsWHERE price < (SELECT AVG(price) FROM products)ORDER BY price;

SELECT order_id, customer_id, order_dateFROM ordersWHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'Mumbai');

Subquery FAQ