Logical operators AND and OR

The AND and OR logical operators combine two conditions.

The AND and OR logical operators combine two conditions. AND evaluates to TRUE when both of the conditions joined by the AND keyword are matched, and OR evaluates to TRUE when either condition joined by OR 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

How AND queries are evaluated

Vertica treats expressions separated by AND (conjunctive) operators individually. For example if the WHERE clause were:

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

the query would be interpreted as two conjunct expressions:

  1. (a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2))

  2. (c IN (SELECT a FROM t1))

The first expression is considered a complex subquery, whereas 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;

  • 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);

See also