SQL subqueries enable reuse of the results from a selection query within another query.
CockroachDB supports two kinds of subqueries:
- Relational subqueries which appear as operand in selection queries or table expressions.
- Scalar subqueries which appear as operand in a scalar expression.
Data writes in subqueries
When a subquery contains a data-modifying statement (
DELETE, etc.), the data modification is always executed to
completion even if the surrounding query only uses a subset of the
This is true both for subqueries defined using the
notations, and those defined using
> SELECT * FROM [INSERT INTO t(x) VALUES (1), (2), (3) RETURNING x] LIMIT 1;
This query always inserts 3 rows into
t, even though the surrounding
query only observes 1 row using
New in v19.1: CockroachDB's cost-based optimizer supports most correlated subqueries.
A subquery is said to be "correlated" when it uses table or column names defined in the surrounding query.
For example, to find every customer with at least one order, run:
> SELECT c.name FROM customers AS c WHERE EXISTS( SELECT * FROM orders AS o WHERE o.customer_id = c.id );
The subquery is correlated because it uses
c defined in the surrounding query.
The cost-based optimizer supports most correlated subqueries, with the following exceptions:
Correlated subqueries that generate side effects inside a
Correlated subqueries that result in implicit
LATERALjoins. Given a cross-join expression
bis an application of a set-returning function that references a variable defined in the surrounding query, the
LATERALkeyword is assumed as shown below.
> SELECT e.last_name, s.salary, noise FROM employees AS e, salaries AS s, -- Join with a set-returning function implies LATERAL below generate_series(0, s.salary, 10000) AS noise WHERE e.emp_no = s.emp_no ORDER BY s.salary DESC LIMIT 10;
ERROR: no data source matches prefix: s
For more information, see the Github issue tracking
Note that the example above uses the employees data set that is also used in our Migrate from MySQL instructions (and the MySQL docs).
If you come across an unsupported correlated subquery other than those described above, please file a Github issue.
Performance best practices
CockroachDB is currently undergoing major changes to evolve and improve the performance of subqueries. The restrictions and workarounds listed in this section will be lifted or made unnecessary over time.
Scalar subqueries currently disable the distribution of the execution of a query. To ensure maximum performance on queries that process a large number of rows, make the client application compute the subquery results ahead of time and pass these results directly in the surrounding query.
The results of scalar subqueries are currently loaded entirely into memory when the execution of the surrounding query starts. To prevent execution errors due to memory exhaustion, ensure that subqueries return as few results as possible.