Working with Subqueries¶
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.
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.