Subqueries

A subquery is a query within another query.

The following operators for subqueries are supported:

• ALL and ANY
• [NOT] EXISTS
• [NOT] IN.

ALL / ANY

They allow a comparison operator to be applied to the values of the result set of a subquery.

Syntax

expr compOperator {ALL | ANY} (query)

Parameters

compOperator (comparison operator): One of the following operators: =, !=, >, >=, <, <=.

Usage Notes

• expr is compared with each value of the result set from the subquery using the defined operator. Specifically:
  • If ANY is specified and at least one row of the subquery meets the condition, the result of the comparison is TRUE; otherwise, FALSE is returned.
  • If ALL is specified and every row of the subquery meets the condition, TRUE is returned; otherwise, FALSE.
• ANY and ALL subqueries can only be used in a WHERE clause.
• ANY and ALL subqueries cannot be used with an OR operator.
• Multiple entries in the SELECT list are not supported.

Example

Retrieve rows from the first object that do not have a corresponding row in the second object.

with
  cte_2cols1 as ( select * from  (select 'a' as ord, 10 as col1
                                  union all 
                                  select 'b' as ord,  20 as col1
                                  union all
                                  select 'c' as ord,  30 as col1
                                  union all
                                  select 'd' as ord,  40 as col1
                                  union all
                                  select 'f' as ord,  NULL as col2)),
  cte_2cols2 as ( select * from  (select 'a' as ord, 10 as col2
                                  union all
                                  select 'b' as ord, 20 as col2
                                  union all
                                  select 'c' as ord, 30 as col2
                                  union all 
                                  select 'd' as ord,  40 as col2
                                  union all
                                  select 'e' as ord,  50 as col2))
select ord                                              
    from cte_2cols1 table1                              
    where table1.ord != ALL (SELECT table2.ord
                              FROM cte_2cols2 table2);  

+-----+
| ord |
+-----+
| f   |
+-----+

[NOT] EXISTS

An EXISTS subquery is a boolean expression. It can be used in a WHERE clause, a HAVING clause, or in a function that operates with boolean expressions:

• EXISTS expression = TRUE if the subquery produces rows.
• NOT EXISTS expression = TRUE if the subquery produces no rows.

Syntax

[NOT] EXISTS (query)

Usage Notes

• Correlated EXISTS subqueries can only be used in a WHERE clause.
• Correlated EXISTS subqueries cannot be used as an argument with an OR operator.
• Non-correlated EXISTS subqueries are supported where a boolean expression is allowed.

Example

Retrieve rows from the first object that do not have a corresponding row in the second object.

with
  cte_2cols1 as ( select * from  (select 'a' as ord, 10 as col1
                                  union all 
                                  select 'b' as ord,  20 as col1
                                  union all
                                  select 'c' as ord,  30 as col1
                                  union all
                                  select 'd' as ord,  40 as col1
                                  union all
                                  select 'f' as ord,  NULL as col2)),
  cte_2cols2 as ( select * from  (select 'a' as ord, 10 as col2
                                  union all
                                  select 'b' as ord, 20 as col2
                                  union all
                                  select 'c' as ord, 30 as col2
                                  union all 
                                  select 'd' as ord,  40 as col2
                                  union all
                                  select 'e' as ord,  50 as col2))
select ord                                                  
    from cte_2cols1 table1                                  
    where not exists (select 1
                            from cte_2cols2 table2          
                            where
                              table2.ord=table1.ord)        
;

+-----+
| ord |
+-----+
| f   |
+-----+

[NOT] IN

IN and NOT IN check whether an expression from the query is contained in the values of the subquery.

Syntax

expr [NOT] IN (query)

Usage Notes

• IN can be seen as a shorthand for = (equals) ANY and is subject to the same restrictions as other ANY subqueries.

• NOT IN can be seen as a shorthand for != (not equals) ALL and is subject to the same restrictions as other ALL subqueries.

• It is possible to use [NOT] IN as an operator in expressions that do not contain subqueries. See also [NOT] IN.

Example

This query is equivalent to the example for the ALL operator above.

with
  cte_2cols1 as ( select * from  (select 'a' as ord, 10 as col1
                                  union all 
                                  select 'b' as ord,  20 as col1
                                  union all
                                  select 'c' as ord,  30 as col1
                                  union all
                                  select 'd' as ord,  40 as col1
                                  union all
                                  select 'f' as ord,  NULL as col2)),
  cte_2cols2 as ( select * from  (select 'a' as ord, 10 as col2
                                  union all
                                  select 'b' as ord, 20 as col2
                                  union all
                                  select 'c' as ord, 30 as col2
                                  union all 
                                  select 'd' as ord,  40 as col2
                                  union all
                                  select 'e' as ord,  50 as col2))
select ord                                              
    from cte_2cols1 table1                              
    where table1.ord NOT IN (SELECT table2.ord
                              FROM cte_2cols2 table2);  

+-----+
| ord |
+-----+
| f   |
+-----+