The WHERE clause filters the result of the FROM clause.
SELECT ... FROM ... WHERE <predicate> [ ... ]
A boolean expression. The expression can include logical operators, such as
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(+);
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.
You cannot use the
(+)notation to create
FULL OUTER JOIN; you can only create
LEFT OUTER JOINand
RIGHT OUTER JOIN.
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);