This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Subqueries used in search conditions

Subqueries are used as search conditions in order to filter results.

1: Combining search conditions
2: Expressions as subqueries

Subqueries are used as search conditions in order to filter results. They specify the conditions for the rows returned from the containing query's select-list, a query expression, or the subquery itself. The operation evaluates to TRUE, FALSE, or UNKNOWN (NULL).

Syntax

search‑condition {
    [ { AND | OR | NOT } {  predicate | ( search‑condition ) } ]
   }[,... ]
 predicate
     { expression comparison‑operator expression
         | string‑expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB } string-expression
         | expression IS [ NOT ] NULL
         | expression [ NOT ] IN ( subquery | expression[,... ] )
         | expression comparison‑operator [ ANY | SOME ] ( subquery )
         | expression comparison‑operator ALL ( subquery )
         | expression OR ( subquery )
         | [ NOT ] EXISTS ( subquery )
         | [ NOT ] IN ( subquery )
     }

Parameters

`search-condition`	Specifies the search conditions for the rows returned from one of the following: Containing query's select-list Query expression Subquery If the subquery is used with an UPDATE or DELETE statement, UPDATE specifies the rows to update and DELETE specifies the rows to delete.
`{ AND \| OR \| NOT }`	Keywords that specify the logical operators that combine conditions, or in the case of NOT, negate conditions. `AND`: Combines two conditions and evaluates to TRUE when both of the conditions are TRUE. `OR`: Combines two conditions and evaluates to TRUE when either condition is TRUE. `NOT`: Negates the Boolean expression specified by the predicate.
`predicate`	An expression that returns TRUE, FALSE, or UNKNOWN (NULL).
`expression`	A column name, constant, function, or scalar subquery, or combination of column names, constants, and functions connected by operators or subqueries.
`comparison‑operator`	An operator that tests conditions between two expressions, one of the following: `<`: less than `>`: greater than `<=`: less than or equal `>=`: greater than or equal `=`: equal; returns UNKNOWN if either expression does `<=>`: Like the `=` operator, but returns TRUE (instead of UNKNOWN) if both expressions evaluate to UNKNOWN, and FALSE (instead of UNKNOWN) if one expression evaluates to UNKNOWN. `<>`: not equal `!=`: not equal
`string‑expression`	A character string with optional wildcard (`*`) characters.
`[ NOT ] { LIKE \| ILIKE \| LIKEB \| ILIKEB }`	Indicates that the character string following the predicate is to be used (or not used) for pattern matching.
`IS [ NOT ] NULL`	Searches for values that are null or are not null.
`ALL`	Used with a comparison operator and a subquery. Returns TRUE for the left-hand predicate if all values returned by the subquery satisfy the comparison operation, or FALSE if not all values satisfy the comparison or if the subquery returns no rows to the outer query block.
`ANY \| SOME`	`ANY` and `SOME` are synonyms and are used with a comparison operator and a subquery. Either returns TRUE for the left-hand predicate if any value returned by the subquery satisfies the comparison operation, or FALSE if no values in the subquery satisfy the comparison or if the subquery returns no rows to the outer query block. Otherwise, the expression is UNKNOWN.
`[ NOT ] EXISTS`	Used with a subquery to test for the existence of records that the subquery returns.
`[ NOT ] IN`	Searches for an expression on the basis of an expression's exclusion or inclusion from a list. The list of values is enclosed in parentheses and can be a subquery or a set of constants.

1 - Combining search conditions

Logical operators AND and OR combine two conditions:.

Logical operators AND and OR combine two conditions:

AND evaluates to TRUE when both conditions joined by AND are matched.
OR evaluates to TRUE when either condition is matched.

OR subqueries (complex expressions)

Vertica supports subqueries in more complex expressions using OR. For example:

More than one subquery in the conjunct expression:

(SELECT MAX(b) FROM t1) + SELECT (MAX FROM t2) a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2)

An OR clause in the conjunct expression involves at least one subquery:

a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2) a IN (SELECT a from t1) OR b = 5
a = (SELECT MAX FROM t2) OR b = 5

One subquery is present but it is part of a another expression:

x IN (SELECT a FROM t1) = (x = (SELECT MAX FROM t2) (x IN (SELECT a FROM t1) IS NULL

Evaluation of AND queries

Vertica treats expressions separated by AND (conjunctive) operators individually. For example, given the following WHERE clause:

  WHERE (a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2)) AND (c IN (SELECT a FROM t1))

The query is interpreted as two conjunct expressions:

(a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2))
(c IN (SELECT a FROM t1))

The first expression is considered a complex subquery, while the second expression is not.

Examples

The following list shows some of the ways you can filter complex conditions in the WHERE clause:

OR expression between a subquery and a non-subquery condition:

=> SELECT x FROM t WHERE x > (SELECT SUM(DISTINCT x) FROM t GROUP BY y) OR x < 9;

OR expression between two subqueries:

=> SELECT * FROM t WHERE x=(SELECT x FROM t) OR EXISTS(SELECT x FROM tt);

Subquery expression:

=> SELECT * FROM t WHERE x=(SELECT x FROM t)+1 OR x<>(SELECT x FROM t)+1;

OR expression with [NOT] IN subqueries:

=> SELECT * FROM t WHERE NOT (EXISTS (SELECT x FROM t)) OR x >9;

OR expression with IS [NOT] NULL subqueries:

=> SELECT * FROM t WHERE (SELECT * FROM t)IS NULL OR (SELECT * FROM tt)IS NULL;

OR expression with boolean column and subquery that returns Boolean data type:
```
=> SELECT * FROM t2 WHERE x = (SELECT x FROM t2) OR x;
```
Note
To return TRUE, the argument of OR must be a Boolean data type.

OR expression in the CASE statement:

=> SELECT * FROM t WHERE CASE WHEN x=1 THEN x > (SELECT * FROM t)
       OR x < (SELECT * FROM t2) END ;

Analytic function, NULL-handling function, string function, math function, and so on:

=> SELECT x FROM t WHERE x > (SELECT COALESCE (x,y) FROM t GROUP BY x,y) OR
       x < 9;

In user-defined functions (assuming f() is one):

=> SELECT * FROM t WHERE x > 5 OR x = (SELECT f(x) FROM t);

Use of parentheses at different places to restructure the queries:

=> SELECT x FROM t WHERE (x = (SELECT x FROM t) AND y = (SELECT y FROM t))
       OR (SELECT x FROM t) =1;

Multicolumn subqueries:

=> SELECT * FROM t WHERE (x,y) = (SELECT x,y FROM t) OR x > 5;

Constant/NULL on lefthand side of subquery:

=> SELECT * FROM t WHERE x > 5 OR 5 = (SELECT x FROM t);

2 - Expressions as subqueries

Subqueries that return a single value (unlike a list of values returned by IN subqueries) can generally be used anywhere an expression is allowed in SQL: a column name, constant, function, scalar subquery, or a combination of column names, constants, and functions connected by operators or subqueries.

For example:

=> SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2) ORDER BY c1;
=> SELECT c1 FROM t1 WHERE COALESCE((t1.c1 > ANY (SELECT c1 FROM t2)), TRUE);
=> SELECT c1 FROM t1 GROUP BY c1 HAVING
     COALESCE((t1.c1 <> ALL (SELECT c1 FROM t2)), TRUE);

Multi-column expressions are also supported:

=> SELECT c1 FROM t1 WHERE (t1.c1, t1.c2) = ALL (SELECT c1, c2 FROM t2);
=> SELECT c1 FROM t1 WHERE (t1.c1, t1.c2) <> ANY (SELECT c1, c2 FROM t2);

Vertica returns an error on queries where more than one row would be returned by any subquery used as an expression:

=> SELECT c1 FROM t1 WHERE c1 = (SELECT c1 FROM t2) ORDER BY c1;
   ERROR:  more than one row returned by a subquery used as an expression

Subqueries used in search conditions

Syntax

Parameters

1 - Combining search conditions

OR subqueries (complex expressions)

Evaluation of AND queries

Examples

Note

See also

2 - Expressions as subqueries

See also