Combining search 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);
  

See also

• Subquery restrictions