This topic describes the SQL-compliant subquery expressions available in PolarDB for Oracle. All expressions described in this topic return Boolean (true/false) results.

EXISTS

The argument of EXISTS is an arbitrary SELECT statement or subquery. The subquery is evaluated to determine whether it returns rows. If at least one row is returned, the result of EXISTS is "true". If the subquery returns no rows, the result of EXISTS is "false".

EXISTS(subquery)

The subquery can refer to variables from the surrounding query, which will act as constants during an evaluation of the subquery.

In most cases, the time required for the subquery to run will only be enough to determine whether a minimum of one row is returned (not until completion). We do not recommend that you write a subquery that produces any potential side effects (such as calling sequence functions). It is difficult to predict when and if potential side effects may occur.

The result of EXISTS only depends on whether rows are returned, rather than on the content of the rows. Therefore, the output list of the subquery can be ignored. A common coding convention is to write all EXISTS tests in the form of EXISTS (SELECT 1 WHERE...). However, exceptions to this rule exist, such as subqueries that use INTERSECT.

This example is similar to an inner join on the deptno column. However, in this example, up to one output row is produced for each dept row and multiple matching emp rows:

SELECT dname FROM dept WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno);

   dname
------------
 ACCOUNTING
 RESEARCH
 SALES
(3 rows)

IN

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of IN is "true" if one equal subquery row is found. The result is "false" if no equal row is found (including the case where the subquery returns no rows).

expression IN (subquery)

Note that the result of the IN construct will be null rather than "false" in either of the following scenarios. 1. The left-hand expression returns null. 2. No equal right-hand values are found and at least one right-hand row returns null. This is in accordance with standard SQL rules for Boolean combinations of null values.

As with EXISTS, we do not recommend you assume that a complete evaluation of the subquery will be performed.

NOT IN

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result. The result of NOT IN is "true" if only unequal subquery rows are found (including the case where the subquery returns no rows). The result is "false" if an equal row is found.

expression NOT IN (subquery)

Note that the result of the NOT IN construct will be null rather than "true" in either of the following scenarios. 1. The left-hand expression returns null. 2. No equal right-hand values are found and at least one right-hand row returns null. This is in accordance with standard SQL rules for Boolean combinations of null values.

As with EXISTS, we do not recommend you assume that a complete evaluation of the subquery will be performed.

ANY/SOME

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result by using the given operator, which must generate a BOOLEAN result. The result of ANY is "true" if a true result is returned. The result is "false" if no true result is found (including the case where the subquery returns no rows).

expression operator ANY (subquery) 
expression operator SOME (subquery)

SOME is a synonym for ANY. IN is equivalent to "= ANY".

Note that if no success is achieved and at least one right-hand row returns null for the operator's result, the result of the ANY construct will be null, not "false". This is in accordance with standard SQL rules for Boolean combinations of null values.

As with EXISTS, we do not recommend you assume that a complete evaluation of the subquery will be performed.

ALL

The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subquery result by using the given operator, which must generate a BOOLEAN result. The result of ALL is "true" if the comparison returns true for all subquery rows (including the case where the subquery returns no rows). The result is "false" if the comparison returns false for a subquery row. The result is null if the comparison does not return false for any subquery row, and the comparison returns null for at least one row.

expression operator ALL (subquery)

NOT IN is equivalent to "<> ALL".

As with EXISTS, we do not recommend you assume that a complete evaluation of the subquery will be performed.