> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Subqueries

> Queries inside queries: scalar, IN, EXISTS, correlated, and derived tables.

**Applies to:** Embedded ❌, Enterprise ✅

A subquery is allowed anywhere an expression or a relation is.

## As a relation

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT * FROM (
    SELECT category, count(*) AS n FROM documents GROUP BY category
) AS counts
WHERE n > 100
```

A derived table needs an alias. Its result cannot be
[sampled](https://docs.lancedb.com/sql/dql/sampling.md); sample the table inside it instead:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT * FROM (SELECT * FROM documents TABLESAMPLE (10)) d
```

## As a scalar

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT title, (SELECT count(*) FROM revisions r WHERE r.doc_id = d.id) AS revisions
FROM documents AS d
```

A scalar subquery returns one column and at most one row. No rows produces
`NULL`; more than one row is an error.

## IN and EXISTS

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT title FROM documents
WHERE category_id IN (SELECT id FROM categories WHERE active)

SELECT title FROM documents AS d
WHERE EXISTS (SELECT 1 FROM revisions r WHERE r.doc_id = d.id)
```

Use `EXISTS` to test whether a matching row exists and `IN` to test membership
in a set of values. The optimizer may rewrite either into a join; inspect
`EXPLAIN` before choosing one for performance reasons.

`NOT IN` and `NOT EXISTS` differ on nulls: `NOT IN` against a set containing a
null yields no rows, because the comparison is unknown rather than false. Prefer
`NOT EXISTS` unless you have ruled nulls out.

## Correlated subqueries

A subquery referencing a column from the enclosing query, such as `d.id` above,
is correlated, and is conceptually evaluated per outer row. The planner usually
rewrites it into a join.

> [!NOTE]
>
> Check with [`EXPLAIN`](https://docs.lancedb.com/sql/dql/explain) when a correlated subquery is slow. A plan
> showing a join has been optimized; one showing a repeated scan has not, and
> rewriting it as an explicit [join](https://docs.lancedb.com/sql/dql/join.md) is the fix.

## CTEs as an alternative

A [CTE](https://docs.lancedb.com/sql/dql/select.md#ctes) is a named subquery, and is usually more readable when
the same subquery appears twice or when nesting gets deep.

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
WITH active AS (SELECT id FROM categories WHERE active)
SELECT title FROM documents WHERE category_id IN (SELECT id FROM active)
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.