本页尚未翻译

本页内容仍为英文原文，源自官方文档 sql/dql/subqueries。中文翻译正在分批进行中。

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

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; sample the table inside it instead:

SELECT * FROM (SELECT * FROM documents TABLESAMPLE (10)) d

As a scalar

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

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.

说明

Check with 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 is the fix.

CTEs as an alternative

A CTE is a named subquery, and is usually more readable when the same subquery appears twice or when nesting gets deep.

WITH active AS (SELECT id FROM categories WHERE active)
SELECT title FROM documents WHERE category_id IN (SELECT id FROM active)