Working with Subqueries¶
A subquery is a query within another query. Subqueries in a FROM or WHERE clause are used to provide data that will be used to limit or compare/evaluate the data returned by the containing query.
Types of Subqueries¶
Scalar vs. Non-scalar Subqueries¶
Subqueries can also be categorized as scalar or non-scalar:
A scalar subquery returns a single value (one column of one row). If no rows qualify to be returned, the subquery returns NULL.
A non-scalar subquery returns 0, 1, or multiple rows, each of which may contain 1 or multiple columns. For each column, if there is no value to return, the subquery returns NULL. If no rows qualify to be returned, the subquery returns 0 rows (not NULLs).
Types Supported by Snowflake¶
Snowflake currently supports the following types of subqueries:
Subquery operators operate on nested query expressions. They can be used to compute values that are:
A scalar subquery is a subquery that returns at most one row. A scalar subquery can appear anywhere that a value expression can appear, including the SELECT list, GROUP BY clause, or as an argument to a function in a WHERE or HAVING clause.
A scalar subquery can contain only one item in the SELECT list.
If a scalar subquery returns more than one row, a runtime error is generated.
Correlated scalar subqueries are currently supported only if they can be statically determined to return one row (e.g. if the SELECT list contains an aggregate function with no GROUP BY).
Uncorrelated scalar subqueries are supported anywhere that a value expression is allowed.
Subqueries with a correlation inside of FLATTEN are currently unsupported.
The LIMIT / FETCH clause is allowed only in uncorrelated scalar subqueries.
This example shows a basic uncorrelated subquery in a WHERE clause:
SELECT employee_id FROM employees WHERE salary = (SELECT max(salary) FROM employees);
This example shows an uncorrelated subquery in a FROM clause; this basic subquery
returns a subset of the information in the
The overall query lists jobs in “high-wage” countries where the annual pay
of the job is the same as the per_capita_GDP in that country.
SELECT p.name, p.annual_wage, p.country FROM pay AS p INNER JOIN (SELECT name, per_capita_GDP FROM international_GDP WHERE per_capita_GDP >= 10000.0) AS pcg ON pcg.per_capita_GDP = p.annual_wage AND p.country = pcg.name;
Although subqueries can contain a wide range of SELECT statements, they have the following limitations:
Some clauses are not allowed inside of ANY/ALL/NOT EXISTS subqueries.
The only type of subquery that allows a LIMIT / FETCH clause is an uncorrelated scalar subquery. Also, because an uncorrelated scalar subquery returns only 1 row, the LIMIT clause has little or no practical value inside a subquery.