Multi Row Subqueries
A multi row subquery returns one or more rows. Since it returns multiple values, the query must use the set comparison operators (IN,ALL,ANY). If you use a multi row subquery with the equals comparison operators, the database will return an error if more than one row is returned. I am looking for all the book_key values that have been sold in South Carolina
SQL> select
2 book_key
3 from
4 sales
5 where
6 store_key = (select
7 store_key
8 from
9 store
10 where store_state = 'SC');
store_key = (select
*
ERROR at line 6:
ORA-01427: single-row subquery returns more than one row
In the example above, the subquery returns multiple rows, so the outer query could not evaluate the equals sign. All I need to do is change the equals sign to a set operator.
SQL> select
2 book_key
3 from
4 sales
5 where
6 store_key in (select
7 store_key
8 from
9 store
10 where store_state = 'SC');
BOOK_K
------
B111
B110
B103
B102
…
B116
B106
B102
26 rows selected.
The IN operator returns TRUE if the comparison value is contained in the list; in this case, the results of the subquery. The ANY and ALL operators work with the equal operators. The ANY operator returns TRUE if the comparison value matches any of the values in the list. The ALL operator returns TRUE only if the comparison value matches all the values in the list.
SQL> select
2 book_key
3 from
4 sales
5 where
6 store_key = ANY (select
7 store_key
8 from
9 store
10 where store_state = 'SC');
BOOK_K
------
B111
B110
…
B102
26 rows selected.
As you can see, the =ANY comparison is the same as the IN comparison.
SQL> select
2 book_key
3 from
4 sales
5 where
6 store_key = ALL (select
7 store_key
8 from
9 store
10 where store_state = 'SC');
no rows selected
Using the ALL operator in the above query will return no rows, since the individual store keys cannot ever match all the store keys in the list. With the IN operator, you can add the NOT operator (NOT IN) to exclude values on the list as opposed to including them.
The difference in single and multi row subqueries is the operator you use in the comparison. Be careful with single row subqueries. Sometimes you will get one row returned because of the data you are developing your query with, but once the query is in use, you may find that it can produce multiple rows, resulting in errors.
Oracle Training from Don Burleson |
This topic provides reference information about the subquery operators supported in Snowflake. A subquery is a query within another query.
In this Topic:
ALL / ANY¶
The ALL and ANY keywords can be used to apply a comparison operator to the values produced by a subquery (which can return more than one row).
Syntax¶
<expr> comparisonOperator { ALL | ANY} ( <query> )
Where:
comparisonOperator ::= { = | != | > | >= | < | <= }
Usage Notes¶
The expression is compared with the operator to each value that the subquery returns:
If ANY is specified, then the result is TRUE if any row of the subquery satisfies the condition, otherwise it returns FALSE.
If ALL is specified, then the result is TRUE if every row of the subquery satisfies the condition, otherwise it returns FALSE.
ANY/ALL subqueries are currently supported only in a WHERE clause.
ANY/ALL subqueries cannot appear as an argument to an OR operator.
The subquery must contain only one item in its SELECT list.
Examples¶
Use a != ALL subquery to find the departments that have no employees:
SELECT department_id FROM departments d WHERE d.department_id != ALL (SELECT e.department_id FROM employees e);
[ NOT ] EXISTS¶
An EXISTS subquery is a boolean expression that can appear in a WHERE or HAVING clause, or in any function that operates on a boolean expression:
An EXISTS expression evaluates to TRUE if any rows are produced by the subquery.
A NOT EXISTS expression evaluates to TRUE if no rows are produced by the subquery.
Syntax¶
[ NOT ] EXISTS ( <query> )
Usage Notes¶
Correlated EXISTS subqueries are currently supported only in a WHERE clause.
Correlated EXISTS subqueries cannot appear as an argument to an OR operator.
Uncorrelated EXISTS subqueries are supported anywhere that a boolean expression is allowed.
Examples¶
Use a correlated NOT EXISTS subquery to find the departments that have no employees:
SELECT department_id FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);
[ NOT ] IN¶
The IN and NOT IN operators check if an expression is included or not included in the values returned by a subquery.
Syntax¶
<expr> [ NOT ] IN ( <query> )
Usage Notes¶
IN is shorthand for = ANY, and is subject to the same restrictions as ANY subqueries.
NOT IN is shorthand for != ALL, and is subject to the same restrictions as ALL subqueries.
[NOT] IN can also be used as an operator in expressions that do not involve a subquery. For details, see [ NOT ] IN.
Examples¶
Use a NOT IN subquery that is equivalent to the != ALL subquery example (earlier in this topic):
SELECT department_id FROM departments d WHERE d.department_id NOT IN (SELECT e.department_id FROM employees e);