SQL Subqueries: WHERE, SELECT and FROM Examples

Who is paid the most? You cannot write WHERE salary = MAX(salary), because aggregates are not allowed in WHERE. A subquery solves it in two steps: the inner query finds the highest salary, the outer query finds who earns it:

SELECT first_name, last_name, department, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

▶ Run this query in the SQL Playground

The inner query, SELECT MAX(salary) FROM employees, returns one number. The outer query then reads as WHERE salary = 204449. Subqueries are also called inner queries or nested queries.

SELECT columns
FROM table
WHERE column operator (SELECT column FROM table WHERE ...);

Most subqueries are not correlated: they do not refer to the outer query, so they can be run on their own and are evaluated once. Copy the bracketed part into the Playground and run it to see what the outer query will receive.

A single value

Employees who earn more than the company average:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC
LIMIT 6;

▶ Run this query in the SQL Playground

A list of values with IN

When the subquery can return several rows, use IN. These are the orders placed by premium customers:

SELECT COUNT(*) AS orders_by_premium_customers
FROM orders
WHERE user_id IN (SELECT id FROM users WHERE is_premium = 1);

▶ Run this query in the SQL Playground

Using = instead of IN with a multi-row subquery is an error in PostgreSQL, SQL Server, MySQL and Oracle. SQLite does not complain; it silently uses the first row, which gives a wrong answer without warning.

NOT IN and NOT EXISTS

To find rows with no match, NOT EXISTS is safer than NOT IN, which returns nothing if its list contains a NULL. Customers who have never ordered:

SELECT COUNT(*) AS never_ordered
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

▶ Run this query in the SQL Playground

See EXISTS and IN vs EXISTS for the details.

A subquery that returns a single value can be a column. Here every product is shown with the average price of the whole catalogue, and its difference from it:

SELECT name, price,
       (SELECT ROUND(AVG(price), 2) FROM products) AS catalogue_avg,
       ROUND(price - (SELECT AVG(price) FROM products), 2) AS difference
FROM products
ORDER BY id
LIMIT 6;

▶ Run this query in the SQL Playground

If the subquery refers to the outer row, it becomes correlated and can give a different value per row, such as each customer's number of orders:

SELECT u.first_name, u.last_name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS orders
FROM users u
ORDER BY orders DESC, u.id
LIMIT 6;

▶ Run this query in the SQL Playground

A subquery in FROM acts as a temporary table. It must have an alias. This is the usual way to filter or join on aggregated results. Average salary per department, keeping only departments above 130,000:

SELECT d.department, d.employees, d.avg_salary
FROM (SELECT department,
             COUNT(*) AS employees,
             ROUND(AVG(salary)) AS avg_salary
      FROM employees
      GROUP BY department) AS d
WHERE d.avg_salary > 130000
ORDER BY d.avg_salary DESC;

▶ Run this query in the SQL Playground

The same result could come from GROUP BY with HAVING. Derived tables become necessary when you aggregate twice, for example the average of per-customer totals:

SELECT COUNT(*) AS customers,
       ROUND(AVG(spent), 2) AS avg_spend_per_customer,
       MAX(spent) AS top_spend
FROM (SELECT user_id, SUM(total) AS spent
      FROM orders
      GROUP BY user_id) AS per_customer;

▶ Run this query in the SQL Playground

When a derived table gets long, or you need it twice, a CTE says the same thing more readably.

A subquery can contain another subquery. Read them from the inside out. This counts the customers who have bought something in the category with the highest average price:

SELECT COUNT(DISTINCT o.user_id) AS customers
FROM orders o
WHERE o.id IN (
  SELECT oi.order_id
  FROM order_items oi
  WHERE oi.product_id IN (
    SELECT p.id
    FROM products p
    WHERE p.category = (
      SELECT category
      FROM products
      GROUP BY category
      ORDER BY AVG(price) DESC
      LIMIT 1)));

▶ Run this query in the SQL Playground

SELECT category
FROM products
GROUP BY category
ORDER BY AVG(price) DESC
LIMIT 1;

▶ Run this query in the SQL Playground

Three levels is about the limit for readability. Beyond that, the same logic written with JOINs or CTEs is easier to follow.

Many subqueries can be written as joins. The premium-orders count from above, as a JOIN:

SELECT COUNT(*) AS orders_by_premium_customers
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.is_premium = 1;

▶ Run this query in the SQL Playground

Choose the form that says what you mean. Use a JOIN when you need columns from the other table; use a subquery when the other table only decides which rows to keep.

Five errors and how to avoid them.

SELECT *
FROM orders
WHERE user_id = (SELECT id FROM users
                 WHERE is_premium = 1);

SELECT *
FROM orders
WHERE user_id IN (SELECT id FROM users
                  WHERE is_premium = 1);

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

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

SELECT *
FROM (SELECT department, AVG(salary) AS a
      FROM employees GROUP BY department);

SELECT *
FROM (SELECT department, AVG(salary) AS a
      FROM employees GROUP BY department) AS d;

WHERE id NOT IN (SELECT manager_id FROM employees)

WHERE NOT EXISTS (SELECT 1 FROM employees r
                  WHERE r.manager_id = e.id)

WHERE id IN (SELECT ... WHERE x IN (SELECT ...
  WHERE y = (SELECT ... WHERE z IN (SELECT ...))))

WITH step1 AS (...),
     step2 AS (SELECT ... FROM step1)
SELECT ... FROM step2;

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List the products that cost more than the average product price. Show the 6 cheapest of them. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, price
   FROM products
   WHERE price > (SELECT AVG(price) FROM products)
   ORDER BY price
   LIMIT 6;

   ▶ Run this query in the SQL Playground

   The subquery returns one number, so a plain > comparison works.

2. 2Easy Find the employee or employees hired earliest. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT first_name, last_name, hire_date
   FROM employees
   WHERE hire_date = (SELECT MIN(hire_date) FROM employees);

   ▶ Run this query in the SQL Playground

   MIN in a subquery gives the earliest date; = keeps everyone hired that day.

3. 3Medium List the products that have at least one 1-star review. Show 8 by name. Table: reviews, products

   Show solution

   SQLite · runs in the Playground

   SELECT name
   FROM products
   WHERE id IN (SELECT product_id FROM reviews WHERE rating = 1)
   ORDER BY name
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   The subquery returns many product ids, so IN is needed.

4. 4Medium Using a derived table, find how many customers have placed more than 2 orders. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS customers_with_3_plus_orders
   FROM (SELECT user_id, COUNT(*) AS orders
         FROM orders
         GROUP BY user_id) AS t
   WHERE t.orders > 2;

   ▶ Run this query in the SQL Playground

   Count per customer first, then count the customers that pass the filter.

5. 5Medium Show each department with its number of employees and the company-wide employee count. Table: departments, employees

   Show solution

   SQLite · runs in the Playground

   SELECT d.name,
          (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.id) AS employees,
          (SELECT COUNT(*) FROM employees) AS company_total
   FROM departments d
   ORDER BY employees DESC, d.name;

   ▶ Run this query in the SQL Playground

   One correlated and one plain subquery in the same SELECT.

6. 6Hard Find the cities whose customers spent more in total than the average city. Table: orders, users

   Show solution

   SQLite · runs in the Playground

   SELECT city, spent
   FROM (SELECT u.city, ROUND(SUM(o.total), 2) AS spent
         FROM orders o
         JOIN users u ON u.id = o.user_id
         GROUP BY u.city) AS c
   WHERE spent > (SELECT AVG(city_spent)
                  FROM (SELECT SUM(o.total) AS city_spent
                        FROM orders o
                        JOIN users u ON u.id = o.user_id
                        GROUP BY u.city) AS x)
   ORDER BY spent DESC;

   ▶ Run this query in the SQL Playground

   A derived table of city totals, compared with the average of the same totals. A CTE would avoid writing the city totals twice.