# Subqueries in SQL: Types, Syntax and Examples

Subqueries in SQL are SELECT statements written inside another SQL statement. They let you compute a value, a list, or a set of rows first, then use that result in the outer query. This article covers the main types, where each one is allowed, and how to write them without tripping over the usual errors.

## Quick Answer

- A subquery is a SELECT statement enclosed in parentheses and used inside another statement.
- A scalar subquery returns exactly one row and one column, so it can stand in for a single value.
- An IN subquery returns one column of values, and the outer query tests membership against that list.
- A correlated subquery references a column from the outer query, so it is re-evaluated for each outer row [1].
- Subqueries can appear in SELECT, FROM, WHERE, HAVING and other clauses, but each position has its own rules.

## What Subqueries in SQL Mean

In plain terms, a subquery is a query that answers a smaller question so the outer query can use the answer. You might ask "which orders are above the average amount?" The average is one number, and you get it with a query of its own. That inner query is the subquery.

The precise definition is narrower. A subquery is a parenthesized SELECT expression that produces a result set, and the surrounding expression consumes that result set according to the subquery's form. PostgreSQL documents these forms as subquery expressions, and all of the expression forms it lists return Boolean results [1]. The type of the subquery is determined by how many rows and columns it returns, not by how it is written.

Three shapes matter most:

- **Scalar subquery.** Returns one row and one column. It behaves like a single value in the surrounding expression. If it returns more than one row or more than one column, that is an error. If it returns no rows during a particular execution, the scalar result is taken to be null [2].
- **Row or list subquery.** Returns one column and many rows, typically used with IN, ANY or ALL.
- **Table subquery.** Returns many rows and many columns, typically used in a FROM clause as a derived table.

A subquery can contain most of what an ordinary SELECT can contain, including DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, UNION constructs and functions [3].

## How It Works

The mechanism is easiest to see with the scalar form. Suppose you want every order above the overall average:

$$ \text{keep row} \iff \text{amount} > \text{AVG}(\text{amount}) $$

Each symbol means the following:

- $\text{amount}$ is the column value from the current row of the outer query.
- $\text{AVG}(\text{amount})$ is the value produced by the inner query, computed once over the whole table.
- $\iff$ means the row is kept when the comparison is true.

The inner query runs, produces one number, and the outer query compares each row against that number. The inner query does not see the outer row, so it is evaluated once.

A correlated subquery works differently. It refers to a column from the surrounding query, and that outer value acts as a constant during any one evaluation of the subquery [1]. Conceptually it runs once per outer row, which is why correlated subqueries are often slower than equivalent joins. The article on correlated subqueries covers that pattern in depth.

For IN, the mechanism is membership. The inner query returns a set of values, and the outer query keeps rows whose value appears in that set. For EXISTS, the inner query is evaluated only to determine whether it returns any rows at all. If it returns at least one row, EXISTS is true, and if it returns no rows, EXISTS is false [1].

## Worked Example

The dataset is a small `orders` table with eight rows across five customers. Here is the input.

| order_id | customer_id | amount |
|---|---|---|
| 1 | 101 | 120.50 |
| 2 | 101 | 80.00 |
| 3 | 102 | 200.00 |
| 4 | 103 | 50.25 |
| 5 | 103 | 75.75 |
| 6 | 104 | 300.00 |
| 7 | 104 | 150.00 |
| 8 | 105 | 90.00 |

The query combines a scalar subquery and an IN subquery in the same WHERE clause.

```sql
SELECT customer_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders)
  AND customer_id IN (SELECT customer_id FROM orders WHERE amount > 100);
```

The scalar subquery `(SELECT AVG(amount) FROM orders)` computes the overall average order amount. The outer `WHERE amount > ...` keeps only orders above that average. The IN subquery `(SELECT customer_id FROM orders WHERE amount > 100)` returns the customers who have at least one order over 100. The `AND customer_id IN (...)` further restricts results to those customers. The result was checked with an equivalent SQLite query.

| customer_id | amount |
|---|---|
| 102 | 200.00 |
| 104 | 300.00 |
| 104 | 150.00 |

## How to Interpret It

Read the output as the intersection of two conditions. The first condition is a numeric threshold, and the second is a membership test. A row survives only if it passes both.

Notice what the result does not contain. Customer 101 passes the membership test because its order of 120.50 exceeds 100, but neither of its orders is above the average of 133.31, so it fails the first condition. Customer 105 has an order of 90.00, which is below the average, so it fails the first condition regardless of the second. Customer 103 has no order above 100, so it fails the membership test.

This is the practical value of subqueries. You can express a two-step question in one statement without creating a temporary table or running two separate queries. If you want to see the same logic written with a reusable named query, the common table expression guide shows the alternative syntax.

## When to Use It (and when not to)

Use a scalar subquery when you need one aggregate value as a comparison point, such as an average, a maximum, or a count. Use an IN subquery when the outer query needs to test membership in a set produced by another query. Use EXISTS when you only care whether a matching row exists, since it can stop as soon as it finds one. Use a derived table in FROM when you want to treat a query result as a table and join to it.

Avoid subqueries when a join expresses the same logic more clearly and the optimizer handles it better. A join is usually the better choice when you need columns from both tables in the output. Avoid correlated subqueries over large tables unless the correlated column is indexed, because the inner query may run once per outer row.

There is also a hard restriction to know about. In MySQL, you cannot modify a table and select from the same table in a subquery [3], although PostgreSQL and SQL Server allow it. If you need that pattern, restructure the statement or stage the data first.

## Subquery vs Join

Both combine information from more than one query, but they differ in what they return and how they read.

| Aspect | Subquery | Join |
|---|---|---|
| Output columns | Only outer query columns, unless the subquery is in FROM | Columns from both tables |
| Typical use | Filter by a computed value or set | Combine related rows for display |
| Readability | Reads as a nested question | Reads as a combination of tables |
| Correlation | Can reference the outer query | Not applicable |
| Duplicate rows | IN can behave differently with duplicates | Depends on join type and keys |

If you need to see how join types differ, the comparison of CROSS JOIN vs LEFT JOIN walks through the row counts each one produces.

## Common Mistakes

- **Using a scalar subquery that returns multiple rows.** The database raises an error because a scalar position expects one row. Fix it by adding an aggregate, a LIMIT, or a more selective WHERE clause.
- **Forgetting parentheses.** A subquery must be enclosed in parentheses. Without them the parser reads the inner SELECT as a syntax error or as part of the outer statement.
- **Assuming a scalar subquery returns zero instead of null.** When the subquery returns no rows, the scalar result is null, not zero [2]. Wrap it in COALESCE if you need a numeric default.
- **Confusing IN with EXISTS on nullable columns.** NULL comparisons inside IN can produce unexpected results. EXISTS only checks for row presence and avoids that problem [1].
- **Writing a correlated subquery when a join would do.** The correlated version may run once per outer row. Check the execution plan before committing to it.
- **Referencing an outer alias that is not in scope.** A subquery in FROM cannot see columns from the outer query in most databases. Move the reference or restructure the query.

## Limitations

Subqueries cannot do everything a separate query can. A scalar subquery is limited to one row and one column, and violating that is an error rather than a silent truncation [2]. Correlated subqueries can be expensive because the inner query may execute repeatedly, and the optimizer does not always rewrite them into a join. Performance depends on the database engine, the indexes available, and the size of the tables.

There is also a readability cost. Deeply nested subqueries become hard to debug, and errors in the inner query can be masked by the outer one. When nesting goes beyond two levels, a common table expression or a temporary table is usually easier to read and to test. Finally, behavior around NULL values and duplicate rows differs between IN, EXISTS and joins, so verify results against a known case before trusting a query in production.

## Frequently Asked Questions

### What is a subquery in SQL?

A subquery is a SELECT statement nested inside another SQL statement and enclosed in parentheses. It produces a result that the outer statement consumes as a value, a list, or a table. The outer statement can be a SELECT, INSERT, UPDATE or DELETE in most databases.

### What is the difference between a subquery and a correlated subquery?

A plain subquery is independent of the outer query and can be evaluated once. A correlated subquery references a column from the surrounding query, and that value acts as a constant during each evaluation of the subquery [1]. Correlated subqueries are typically evaluated once per outer row.

### Can a subquery return more than one column?

Yes, if it is used in a position that accepts multiple columns, such as a derived table in the FROM clause. A scalar subquery cannot, because it must produce a single value. Using a multi-column subquery in a scalar position is an error [2].

### Where can I use a subquery?

You can use subqueries in the SELECT list, the FROM clause, the WHERE clause, and the HAVING clause, among other positions. Each position has its own rules about how many rows and columns the subquery may return. A subquery can also contain most ordinary SELECT clauses, including GROUP BY, ORDER BY, LIMIT and UNION [3].

### Does a subquery make a query slower?

Not necessarily. A non-correlated subquery may be evaluated once and reused. A correlated subquery may run once per outer row, which can be slow on large tables. Check the execution plan and compare against an equivalent join before deciding.

## References

1. [PostgreSQL: Documentation: 18: 9.24. Subquery Expressions](https://www.postgresql.org/docs/current/functions-subquery.html)
2. [PostgreSQL: Documentation: 8.2: Value Expressions](https://www.postgresql.org/docs/8.2/sql-expressions.html)
3. [MySQL :: MySQL 8.4 Reference Manual :: Search Results](https://dev.mysql.com/doc/search/?q=hint&d=371&p=5)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [Correlated Subqueries: Definition and SQL Examples](/blog/data-analysis/correlated-subqueries-sql)
- [SQL RIGHT Function: Syntax, Examples and Use Cases](/blog/data-analysis/sql-right-function-syntax-examples)
- [Using CASE in SQL: Syntax and Examples](/blog/data-analysis/using-case-in-sql)
- [SQL UNION Operator: Syntax, Examples and Differences](/blog/data-analysis/sql-union-operator-syntax-examples)
- [SQL SELECT Statement: Syntax, Clauses and Examples](/blog/data-analysis/sql-select-statement-syntax-examples)
- [Tabular Data: What It Is and How to Analyze It](/blog/research-skills/tabular-data-what-it-is-and-how-to-analyze-it)