Skip to content
Tutorials Center
My learning

Topics

Lesson 43 of 125 ยท Subqueries and Set Operations

Subqueries

Learn SQL subqueries step by step. Use scalar subqueries, IN lists and derived tables, avoid the NOT IN NULL trap, and know when a subquery beats a join.

A subquery is a complete SELECT statement written inside another statement, wrapped in parentheses. The inner query runs first (at least logically), and the outer query uses its result as if it were a value, a list or a table.

Subqueries let you answer questions in steps, the way you'd think about them: "find the average salary, then list everyone above it", or "find the customers who bought a fountain pen, then show their details". Without subqueries you'd run two queries and copy a number from one into the other by hand.

This lesson sorts subqueries by the shape of their result, because the shape decides where you can use them:

Shape Returns Typical place Example
Scalar one row, one column anywhere a single value fits WHERE salary > (SELECT AVG(salary) FROM employees)
List many rows, one column after IN / NOT IN WHERE id IN (SELECT customer_id FROM orders)
Table many rows, many columns in FROM, as a derived table FROM (SELECT ...) AS t

All subqueries in this lesson are self-contained: you could run the inner query on its own. Subqueries that refer to the outer row are called correlated and get their own lesson.

Scalar subqueries in WHERE

A scalar subquery returns exactly one value, so it can stand anywhere you'd write a literal number or string. Who in the company database earns more than the company average?

SQL Above the average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The inner query computes the average (about 90,361) once, and the outer query compares every salary with it. Twelve employees qualify. You can't write WHERE salary > AVG(salary) directly: aggregates aren't allowed in WHERE, because WHERE looks at one row at a time (see HAVING for the logical order of a query). The subquery gives the aggregate its own complete query.

Scalar subqueries can use any table, not just the one in the outer query. Here, the outer query reads products while the inner one finds a category id by name:

SQL Look up a value by name ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This works because category names are unique, so the subquery can only return one id.

Scalar subqueries in SELECT

In the SELECT list, a scalar subquery adds the same computed value to every row. That's perfect for comparing each row with an overall figure:

SQL Each price vs the average ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The average price of all 26 products is 53.69, and each Home Office product is compared with it. Writing the same subquery twice is fine for a lesson; for longer queries, a CTE lets you name it once and reuse it.

When a scalar subquery returns zero or many rows

What if the inner query finds no rows, or several? The standard rule is clear: no rows means NULL, and more than one row is an error. SQLite is more lenient: it silently uses the first row and ignores the rest.

SQL Too many rows, or none ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query prints Farah, but only because she happens to be the first row SQLite reads. PostgreSQL, MySQL and SQL Server would stop with an error such as more than one row returned by a subquery used as an expression. Don't rely on SQLite's behavior: make sure a scalar subquery can only return one row by using an aggregate (MAX, COUNT), a unique column, or ORDER BY ... LIMIT 1.

List subqueries with IN

When the inner query returns one column with many rows, use it as a list after IN. Which customers have ever bought a Fountain Pen (product 12)?

SQL Customers who bought a fountain pen ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Nine customers. Notice that the subquery itself may join tables, filter and group; only its final result has to be a single column. Read the query from the inside out: "the customer ids on orders containing product 12", then "customers whose id is in that list".

IN vs JOIN: duplicates

You could also join customers to orders and order_items directly. The difference shows up when someone bought the pen more than once:

SQL IN doesn't duplicate rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The join returns 10 rows because one customer bought the pen on two orders; IN returns 9, one per customer. IN only asks "is this id in the list?", so duplicates inside the list don't matter. This is called a semi-join, and it's often the clearest way to filter by "has at least one related row". You'll see its sibling, EXISTS, in the EXISTS lesson.

The NOT IN trap

NOT IN looks like the natural opposite, and usually it is. But if the subquery's list contains even one NULL, NOT IN returns no rows at all. Which employees are not anybody's manager?

SQL NOT IN and a NULL ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns 0, the second 29. Why? id NOT IN (1, 2, NULL) means id <> 1 AND id <> 2 AND id <> NULL, and id <> NULL is unknown, never true. An AND that includes an unknown can never be true, so every row is filtered out. (See Working with NULL for three-valued logic.)

Never use NOT IN with a subquery on a nullable column unless you filter out NULLs inside it. NOT EXISTS doesn't have this problem, which is why many teams use it for every "has no related rows" query. Plain IN is safe: a NULL in its list just never matches.

Table subqueries: derived tables

A subquery in the FROM clause produces a temporary result that the outer query reads like a real table. It's called a derived table (or inline view). Use one whenever you need to aggregate, then work with the aggregates: filter them, join them, or aggregate them again.

What's the average of the department averages, and how does it differ from the plain average salary?

SQL Aggregating an aggregate ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The inner query returns one row per department; the outer query treats those six rows as a table called dept and aggregates them again. You can't nest aggregates directly (AVG(AVG(salary)) is an error), so the derived table is how you express "average of averages". The answer, 87,756, is lower than the plain average of 90,361: every department counts equally here, and the CEO's salary (he has no department) isn't included at all.

Derived tables also combine well with joins. This one finds each department's best-paid salary, then joins back to employees to show who earns it:

SQL Top earner per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Unsurprisingly, each department's head is its top earner. If two people tied for the top salary, both would appear. The group for department_id IS NULL (the CEO and the contractor) produces a row in the derived table too, but the inner join to departments drops it.

Aliases for derived tables. SQLite and PostgreSQL 16+ let you omit the alias (FROM (SELECT ...)), but MySQL, SQL Server and older PostgreSQL versions require one (FROM (SELECT ...) AS t). Always write it: it's portable, and you need it anyway to refer to the columns.

Subqueries in HAVING

HAVING filters groups, and a scalar subquery can supply the value to compare with. Which departments pay more than the company-wide average?

SQL Departments above the company average ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Only Finance and Support average more than 90,361. The CEO's high salary pulls the overall average up, which is why most departments fall below it, a nice reminder that averages hide outliers.

Subqueries in INSERT, UPDATE and DELETE

Subqueries aren't limited to SELECT. They're common in data-changing statements, where they pick the rows to change or supply new values. The changes below only affect this run's copy:

SQL Give a raise to project leads ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The UPDATE reports 12 rows affected: there are 13 lead assignments, but Mateo Quist leads two projects, and IN matches each person once, so nobody gets a double raise. You'll use the same pattern in UPDATE and DELETE, and INSERT INTO ... SELECT copies the result of a query into a table (INSERT).

Subquery or join?

Many problems can be solved either way, and modern optimizers often turn one into the other internally, so performance is frequently identical. Choose by clarity:

  • Use a subquery with IN or EXISTS when you only filter by related rows and don't need their columns. It can't duplicate rows.
  • Use a join when you need columns from both tables in the result.
  • Use a derived table when you need to aggregate first and then join or filter the aggregates, to avoid the fan-out problem.
  • Use a scalar subquery for a single value: a threshold, a total for a percentage, a looked-up id.

Subqueries can be nested more than one level deep, but past two levels a query becomes hard to read inside out. That's when Common Table Expressions help: they let you write the same steps top to bottom, each with a name.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does WHERE salary > AVG(salary) fail, while WHERE salary > (SELECT AVG(salary) FROM employees) works?

    WHERE filters single rows before grouping, so it can't contain an aggregate. The subquery computes the average separately and returns one value.
  2. 2. SELECT COUNT(*) FROM t WHERE x NOT IN (SELECT y FROM u) returns 0, although many x values are clearly missing from u. What is the most likely cause?

    A NULL in the list makes every x <> NULL comparison unknown, so no row passes. Filter NULLs out inside the subquery or use NOT EXISTS.
  3. 3. What does SQLite do when a scalar subquery returns three rows?

    SQLite uses the first row and ignores the rest. Other databases raise an error, so make scalar subqueries return one row by design.
  4. 4. A customer bought the same product on three different orders. How often does that customer appear in SELECT * FROM customers WHERE id IN (subquery of buyers)?

    IN only checks membership, so duplicates in the list don't multiply the outer rows. A join would return three rows.
  5. 5. Which query computes the average of per-department average salaries?

    Aggregates can't be nested directly. The derived table computes one average per department, and the outer query averages those rows.

Summary

  • A subquery is a SELECT in parentheses inside another statement; classify it by the shape of its result.
  • Scalar subqueries return one value and work in WHERE, SELECT, HAVING and anywhere else a value fits. No rows gives NULL; SQLite takes the first of several rows, while other databases raise an error.
  • List subqueries feed IN and NOT IN; IN never duplicates outer rows (a semi-join).
  • NOT IN returns nothing if the list contains a NULL; filter NULLs out or use NOT EXISTS.
  • Derived tables in FROM let you aggregate first and then filter, join or aggregate again; always give them an alias.
  • Subqueries also select rows and values in UPDATE, DELETE and INSERT ... SELECT.
  • Pick subqueries for filtering and joins for combining columns; use CTEs when nesting gets deep.

Next: Correlated Subqueries, where the inner query runs once for every row of the outer one.