Categories:

Query Syntax

WHERE

The WHERE clause filters the result of the FROM clause.

Syntax

SELECT ...
FROM ...
WHERE <predicate>
[ ... ]
predicate

A boolean expression. The expression can include logical operators, such as AND, OR, and NOT.

Usage Notes

Joins in the WHERE clause

  • Joins can be expressed in the WHERE clause, either in addition to or instead of specifying them in the FROM clause, by including join conditions in the WHERE clause.

    The following two equivalent queries show how to express a join in either the FROM or WHERE clauses:

    SELECT c1, c2
    FROM t1 INNER JOIN t2
            ON c1 = c2
    ORDER BY 1,2;
    
    SELECT c1, c2
    FROM t1, t2
    WHERE c1 = c2
    ORDER BY 1,2;
    
  • Predicates in the WHERE clause behave as if they are evaluated after the FROM clause (though the optimizer may reorder predicates if it does not impact the results). For example, if a predicate in the WHERE clause references columns of a table participating in an outer join in the FROM clause, it will operate on the rows returned from the join (which may be padded with NULLs).

  • Outer joins can be specified in the WHERE clause using the (+) syntax. To specify an outer join using (+), the columns from the NULL-augmented table of the join are annotated with the (+) in the WHERE clause.

    The following two equivalent queries show how an outer join can be expressed in either the FROM or WHERE clause:

    SELECT c1, c2
    FROM t1 LEFT OUTER JOIN t2
            ON c1 = c2;
    
    SELECT c1, c2
    FROM t1, t2
    WHERE c1 = c2(+);
    

    Note

    There are many restrictions on where the (+) annotation can appear; FROM clause outer joins are more expressive. Snowflake suggests using the (+) notation only when porting code that already uses that notation. New code should avoid that notation.

    Restrictions include:

    • You cannot use the (+) notation to create FULL OUTER JOIN; you can only create LEFT OUTER JOIN and RIGHT OUTER JOIN.

Examples

The following show some simple uses of the WHERE clause:

SELECT * FROM invoices
  WHERE invoice_date < '2018-01-01';

SELECT * FROM invoices
  WHERE invoice_date < '2018-01-01' AND paid = False;

This example uses a subquery and shows all the invoices that have smaller-than-average billing amounts:

SELECT * FROM invoices
  WHERE amount < (SELECT AVG(amount) FROM invoices);