Subqueries

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)

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

As a relation

As a scalar

IN and EXISTS

Correlated subqueries

CTEs as an alternative

​As a relation

​As a scalar

​IN and EXISTS

​Correlated subqueries

​CTEs as an alternative

As a relation

As a scalar

IN and EXISTS

Correlated subqueries

CTEs as an alternative