SQL Subquery: Syntax, Examples and EXISTS vs IN

A subquery is a SELECT statement inside another SQL statement, wrapped in parentheses. The inner query runs first and hands its result to the outer query, so you can answer questions like 'who earns more than the average?' in one step. This guide covers every type with nine examples and real output.

By Matthew Rathbone · November 7, 2026

A subquery (also called a nested query or inner query) is a SELECT statement placed inside another SQL statement. The database runs the inner query first, then uses its result in the outer query. Subqueries let you filter, calculate and compare using data that you do not know in advance, such as the current average salary.

Jump to: syntax · sample data · 9 examples · EXISTS vs IN · the NOT IN trap · subquery vs JOIN vs CTE · mistakes · FAQ

SQL subquery syntax

SELECT column
FROM table
WHERE column operator (SELECT column FROM other_table WHERE condition);

Three rules cover most of what you need:

A subquery is always wrapped in parentheses.
It can appear in WHERE (most common), in FROM (as a derived table), or in SELECT (as a single value).
The shape of its result decides which operators work: one value works with =, > and <; a list of values works with IN; and EXISTS only cares whether any row comes back.

Type	Returns	Typical use
Scalar subquery	One value	`WHERE salary > (SELECT AVG(salary) ...)`
Multi-row subquery	A column of values	`WHERE id IN (SELECT ...)`
Table (derived) subquery	A whole result set	`FROM (SELECT ...) AS t`
Correlated subquery	Depends on the outer row	Compare each row with its own group
`EXISTS` subquery	True or false	"Has at least one matching row"

Set up the sample data

Every query below ran on SQLite 3.45 and the output shown is real. Paste this into any SQL client, such as Beekeeper Studio, to follow along. Note that the Sales and Support departments have no projects.

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT,
                        department_id INTEGER, salary INTEGER, manager_id INTEGER);
CREATE TABLE projects (id INTEGER PRIMARY KEY, name TEXT, department_id INTEGER);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'Marketing'),(4,'Support');
INSERT INTO employees VALUES
  (1,'Ana',1,95000,NULL),(2,'Ben',1,82000,1),(3,'Chloe',1,78000,1),
  (4,'Dev',2,64000,NULL),(5,'Eli',2,58000,4),(6,'Fay',2,71000,4),
  (7,'Gus',3,60000,NULL),(8,'Hana',3,55000,7);
INSERT INTO projects VALUES (1,'Website',1),(2,'Mobile app',1),(3,'Q4 campaign',3);

9 SQL subquery examples

1. Subquery in WHERE: above-average salaries

You cannot write WHERE salary > AVG(salary) because aggregates are not allowed in WHERE. A scalar subquery solves it:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

name  | salary
Ana   | 95000
Ben   | 82000
Chloe | 78000
Fay   | 71000

The inner query returns a single number, 70375.0, and the outer query behaves as if you had typed WHERE salary > 70375.0. Learn the basics in how to use WHERE and AVG.

2. Subquery with IN: filter by another table

Find everyone who works in Sales or Marketing, without joining:

SELECT name
FROM employees
WHERE department_id IN (SELECT id FROM departments
                        WHERE name IN ('Sales', 'Marketing'))
ORDER BY name;

name
Dev
Eli
Fay
Gus
Hana

The inner query returns a list (2, 3), and IN checks whether each employee's department is in it. See the IN operator guide.

3. Subquery with MAX: the top earner

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

name | salary
Ana  | 95000

Unlike ORDER BY salary DESC LIMIT 1, this returns every tied row if two people share the top salary.

4. Subquery in SELECT: a value on every row

SELECT name, salary,
       (SELECT ROUND(AVG(salary)) FROM employees) AS company_avg,
       salary - (SELECT ROUND(AVG(salary)) FROM employees) AS diff
FROM employees
ORDER BY id;

name  | salary | company_avg | diff
Ana   | 95000  | 70375.0     | 24625.0
Ben   | 82000  | 70375.0     | 11625.0
Chloe | 78000  | 70375.0     | 7625.0
Dev   | 64000  | 70375.0     | -6375.0
Eli   | 58000  | 70375.0     | -12375.0
Fay   | 71000  | 70375.0     | 625.0
Gus   | 60000  | 70375.0     | -10375.0
Hana  | 55000  | 70375.0     | -15375.0

A subquery in the SELECT list must return exactly one value. It is handy for "compare to the overall figure" columns. (SQLite shows ROUND results as decimals; other databases print 70375.)

5. Subquery in FROM: a derived table

A subquery in FROM acts like a temporary table. Here we compute each department's average, then join it to the department names:

SELECT d.name AS department, s.avg_salary
FROM (SELECT department_id, ROUND(AVG(salary)) AS avg_salary
      FROM employees
      GROUP BY department_id) AS s
JOIN departments d ON d.id = s.department_id
ORDER BY s.avg_salary DESC;

department | avg_salary
Engineering | 85000.0
Sales       | 64333.0
Marketing   | 57500.0

Most databases require an alias (AS s) on a derived table. Support is missing because it has no employees, and the inner join drops it. For a more readable version of this pattern, use a CTE.

6. Correlated subquery: compare each row to its own group

Who earns more than the average of their own department?

SELECT name, department_id, salary
FROM employees e
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id = e.department_id)
ORDER BY id;

name | department_id | salary
Ana  | 1             | 95000
Fay  | 2             | 71000
Gus  | 3             | 60000

This is a correlated subquery: the inner query refers to e.department_id from the outer query, so conceptually it re-runs for every outer row. Ben (82,000) beats the company average but not Engineering's own 85,000, so he is correctly excluded. Compare this with example 1, which used the company-wide average.

7. EXISTS: does a matching row exist?

List departments that have at least one project:

SELECT name
FROM departments d
WHERE EXISTS (SELECT 1 FROM projects p WHERE p.department_id = d.id)
ORDER BY name;

name
Engineering
Marketing

EXISTS returns true as soon as the inner query finds one row, so what you SELECT inside does not matter. SELECT 1 is the convention. This is also a correlated subquery.

8. NOT EXISTS: find what is missing

SELECT name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM projects p WHERE p.department_id = d.id)
ORDER BY name;

name
Sales
Support

"Rows with no match in another table" is one of the most common real-world subquery uses, such as customers with no orders or products never sold.

9. Subquery for a self-lookup

Show each employee's manager name by looking it up in the same table:

SELECT name,
       (SELECT name FROM employees m WHERE m.id = e.manager_id) AS manager
FROM employees e
WHERE manager_id IS NOT NULL
ORDER BY e.id;

name  | manager
Ben   | Ana
Chloe | Ana
Eli   | Dev
Fay   | Dev
Hana  | Gus

A self join gives the same answer and scales better when you need several columns from the manager.

EXISTS vs IN

Both check membership in another table, and for "has a match" questions they return the same rows. The practical differences:

	`IN`	`EXISTS`
Inner query returns	A list of values	Any rows (values ignored)
Correlated?	Usually not	Usually yes
NULLs in the inner result	Can break `NOT IN`	Safe
Matching on several columns	Awkward	Easy (add conditions)

Modern optimizers often plan them identically, so pick for correctness and clarity first. For NOT checks, prefer NOT EXISTS.

The NOT IN and NULL trap

This is the classic subquery bug. Say someone adds a project with no department (NULL):

INSERT INTO projects VALUES (4, 'Research', NULL);

SELECT name FROM departments
WHERE id NOT IN (SELECT department_id FROM projects);

name
(no rows)

Sales and Support should appear, but the result is empty. id NOT IN (1, 1, 3, NULL) evaluates to "unknown" for every row, because comparing anything to NULL is unknown, so no row passes. The NOT EXISTS query from example 8 still returns Sales and Support. Fixes: use NOT EXISTS, or filter the inner query with WHERE department_id IS NOT NULL. Read how NULL works in SQL for the full story.

Subquery vs JOIN vs CTE

Use a subquery when you need a single computed value (an average, a max) or a yes/no existence check. The query reads naturally from the inside out.
Use a JOIN when you need columns from both tables in the result. Subqueries in WHERE cannot give you the other table's columns.
Use a CTE when the same subquery is used more than once, or when nesting goes more than two levels deep. A CTE gives the step a name and reads top to bottom.

Performance is rarely a reason to avoid subqueries on small and medium tables. If a correlated subquery is slow on big data, rewrite it as a join with GROUP BY or a window function (see SQL window functions), and make sure join columns are indexed.

Common subquery mistakes

Returning several rows where one value is expected. salary > (SELECT salary FROM employees WHERE department_id = 2) returns three rows. PostgreSQL and MySQL raise an error ("more than one row returned by a subquery"); SQLite silently uses the first row, so it runs and returns misleading results (Ana, Ben, Chloe, Fay in my test). Use MAX, MIN or IN to be explicit.
Using NOT IN with a column that can be NULL. See the trap above.
Forgetting parentheses or the alias on a FROM subquery. MySQL and PostgreSQL error with "every derived table must have its own alias".
Selecting several columns in a scalar subquery. A subquery in SELECT or compared with = must return one column.
Unqualified column names in correlated subqueries. Prefix columns with the table alias (e.department_id) or the database may bind the name to the wrong table.
Using ALL/ANY in SQLite. salary > ALL (SELECT ...) works in PostgreSQL, MySQL and SQL Server but is a syntax error in SQLite. Use > (SELECT MAX(...)) instead.

FAQ

What is a subquery in SQL?

A subquery is a SELECT statement nested inside another SQL statement (SELECT, INSERT, UPDATE or DELETE) and wrapped in parentheses. Its result feeds the outer query.

What is a correlated subquery?

A correlated subquery references a column from the outer query, so it is evaluated once per outer row. Example 6 compares each employee with their own department's average.

What is the difference between a subquery and a nested query?

Nothing. "Nested query", "inner query" and "subquery" all describe a query inside another query.

Can I use a subquery in UPDATE or DELETE?

Yes. For example, DELETE FROM employees WHERE salary < (SELECT AVG(salary) FROM employees) AND department_id = 3 removed two rows in my test. Run the matching SELECT first to preview what will change.

How many levels of subqueries can I nest?

Databases allow many levels, but readability drops after two. Switch to a CTE if you are nesting deeper.

Is a subquery or a JOIN faster?

Often the same, because optimizers rewrite them to similar plans. Correlated subqueries on large tables can be slower. Measure with EXPLAIN before rewriting.

Practice subqueries

Try the free interactive nested queries lesson in your browser, then work through the SQL practice problems and more SQL query examples. Keep the SQL cheat sheet handy for syntax.

SQL Subquery: What It Is and How to Use It