Syntax and usage of conditional subquery expressions - PolarDB

PolarDB for Oracle supports the following SQL-compliant subquery expressions. All expressions return a Boolean (true/false/null) result.

EXISTS

EXISTS (subquery)

EXISTS returns true if the subquery returns at least one row, and false if it returns no rows.

The subquery can reference variables from the outer query, which are treated as constants during evaluation. The subquery runs only long enough to determine whether any row exists — it does not run to completion. Avoid subqueries with side effects (such as calling sequence functions), because whether those side effects occur is unpredictable.

Since the result depends only on whether any rows are returned — not on their contents — the output list of the subquery is normally unimportant. The standard convention is to write EXISTS(SELECT 1 WHERE ...). One exception: subqueries that use INTERSECT.

The following example produces at most one output row per tab1 row, even when multiple matching tab2 rows exist — similar to an inner join on col2, but without duplicates:

SELECT col1
FROM tab1
WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 = tab1.col2);

IN

expression IN (subquery)

The subquery must return exactly one column. IN evaluates the left-hand expression and compares it to each row returned by the subquery.

Result	Condition
true	At least one equal subquery row is found
false	No equal row is found, including when the subquery returns no rows
null	The left-hand expression yields null, or no equal right-hand value exists and at least one right-hand row yields null

The null result is null, not false — consistent with SQL's Boolean null rules. Do not assume the subquery runs to completion.

Row constructor form:

row_constructor IN (subquery)

The subquery must return exactly as many columns as there are expressions in the left-hand row constructor. Comparison is row-wise.

Two rows are considered equal if all corresponding members are non-null and equal. They are unequal if any corresponding members are non-null and unequal. Otherwise, the row comparison result is null. If all per-row results are unequal or null — with at least one null — IN returns null.

NOT IN

expression NOT IN (subquery)

The subquery must return exactly one column. NOT IN evaluates the left-hand expression and compares it to each row returned by the subquery.

Result	Condition
true	Only unequal subquery rows are found, including when the subquery returns no rows
false	At least one equal row is found
null	The left-hand expression yields null, or no equal right-hand value exists and at least one right-hand row yields null

The null result is null, not true — consistent with SQL's Boolean null rules. Do not assume the subquery runs to completion.

Row constructor form:

row_constructor NOT IN (subquery)

The subquery must return exactly as many columns as there are expressions in the left-hand row constructor. Null handling is identical to the row constructor form of IN. If all per-row results are unequal or null — with at least one null — NOT IN returns null.

ANY/SOME

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

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

The subquery must return exactly one column. The operator must yield a Boolean result. ANY applies the operator between the left-hand expression and each subquery row.

Result	Condition
true	The operator returns true for at least one subquery row
false	The operator returns true for no rows, including when the subquery returns no rows
null	No comparison returns true, and at least one right-hand row yields null for the operator's result

To build intuition: using > as the operator, > ANY (1, 2, 3) means "greater than at least one value" — that is, greater than the minimum (1). Do not assume the subquery runs to completion.

Row constructor form:

row_constructor operator ANY (subquery)
row_constructor operator SOME (subquery)

The subquery must return exactly as many columns as there are expressions in the left-hand row constructor. Comparison is row-wise.

Result	Condition
true	The comparison returns true for at least one subquery row
false	The comparison returns false for every subquery row, including when the subquery returns no rows
null	No comparison returns true, and at least one comparison returns null

ALL

expression operator ALL (subquery)

NOT IN is equivalent to <> ALL.

The subquery must return exactly one column. The operator must yield a Boolean result. ALL applies the operator between the left-hand expression and each subquery row.

Result	Condition
true	The operator returns true for all rows, including when the subquery returns no rows
false	The operator returns false for at least one row
null	No comparison returns false, and at least one comparison returns null

To build intuition: using > as the operator, > ALL (1, 2, 3) means "greater than every value" — that is, greater than the maximum (3). Do not assume the subquery runs to completion.

Row constructor form:

row_constructor operator ALL (subquery)

The subquery must return exactly as many columns as there are expressions in the left-hand row constructor. Comparison is row-wise.

Result	Condition
true	The comparison returns true for all subquery rows, including when the subquery returns no rows
false	The comparison returns false for at least one subquery row
null	No comparison returns false, and at least one comparison returns null

Single-row comparison

row_constructor operator (subquery)

The subquery must return exactly as many columns as there are expressions in the left-hand row constructor, and cannot return more than one row. If it returns zero rows, the result is null. The left-hand side is evaluated and compared row-wise to the single subquery result row.