Categories:

Query Syntax

WHERE

The WHERE clause specifies a condition that matches a subset of rows. You can use the WHERE clause to:

Syntax

...
WHERE <predicate>
[ ... ]
predicate

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

Usage Notes

  • 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, the filter operates on the rows returned from the join (which might be padded with NULLs).

  • Use care when creating expressions that might evaluate NULLs.

    • In most contexts, the boolean expression NULL = NULL returns NULL, not TRUE.

    • In a WHERE clause, if an expression evaluates to NULL, the row for that expression is removed from the result set (i.e. it is filtered out).

Joins in the WHERE clause

Although the WHERE clause is primarily for filtering, the WHERE clause can also be used to express many types of joins. For conceptual information about joins, see Working with Joins.

The following query creates an inner join:

SELECT t1.c1, t2.c2
    FROM t1, t2
    WHERE t1.c1 = t2.c2
    ORDER BY 1,2;

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;

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.

Support for joins in the WHERE clause is primarily for backwards-compatibility with older queries that do not use the FROM ... ON ... syntax. Snowflake recommends using FROM ... ON ... when writing new queries with joins. For details, see JOIN.

Examples

Simple Examples of Filtering

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

Performing Joins in the WHERE Clause

To specify a join in the WHERE clause, list the tables to be joined in the FROM clause, separating the tables with a comma. Specify the join condition as a filter in the WHERE clause, as shown in the following example:

SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Note

The comma operator is older syntax for INNER JOIN. The following statement shows the recommended way to perform a join using newer syntax. The query below is equivalent to the query above:

SELECT t1.col1, t2.col1 FROM t1 JOIN t2 ON t2.col1 = t1.col1 ORDER BY 1, 2;
+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

This next section shows 3-table joins and shows the difference in behavior with 0, 1, or 2 (+) outer join operators.

Before executing the queries, create and load the tables to use in the joins:

create table departments (
    department_ID INTEGER,
    department_name VARCHAR,
    location VARCHAR
    );
insert into departments (department_id, department_name, location) values
    (10, 'CUSTOMER SUPPORT', 'CHICAGO'),
    (40, 'RESEARCH', 'BOSTON'),
    (80, 'Department with no employees yet', 'CHICAGO'),
    (90, 'Department with no projects or employees yet', 'EREHWON')
    ;

create table projects (
    project_id integer,
    project_name varchar,
    department_id integer
    );
insert into projects (project_id, project_name, department_id) values
    (4000, 'Detect fake product reviews', 40),
    (4001, 'Detect false insurance claims', 10),
    (9000, 'Project with no employees yet', 80),
    (9099, 'Project with no department or employees yet', NULL)
    ;

create table employees (
    employee_ID INTEGER,
    employee_name VARCHAR,
    department_id INTEGER,
    project_id INTEGER
    );
insert into employees (employee_id, employee_name, department_id, project_id)
  values
    (1012, 'May Aidez', 10, NULL),
    (1040, 'Devi Nobel', 40, 4000),
    (1041, 'Alfred Mendeleev', 40, 4001)
    ;

Execute a 3-way inner join. This does not use (+) (or the OUTER keyword) and is therefore an inner join. The output includes only rows for which there is a department, project, and employee:

SELECT d.department_name, p.project_name, e.employee_name
    FROM  departments d, projects p, employees e
    WHERE
            p.department_id = d.department_id
        AND
            e.project_id = p.project_id
    ORDER BY d.department_id, p.project_id, e.employee_id;
+------------------+-------------------------------+------------------+
| DEPARTMENT_NAME  | PROJECT_NAME                  | EMPLOYEE_NAME    |
|------------------+-------------------------------+------------------|
| CUSTOMER SUPPORT | Detect false insurance claims | Alfred Mendeleev |
| RESEARCH         | Detect fake product reviews   | Devi Nobel       |
+------------------+-------------------------------+------------------+

Perform an outer join. This is similar to the preceding statement except that this uses (+) to make the second join an outer join. The effect is that if a department is included in the output, then all of that department’s projects are included, even if those projects have no employees:

SELECT d.department_name, p.project_name, e.employee_name
    FROM  departments d, projects p, employees e
    WHERE
            p.department_id = d.department_id
        AND
            e.project_id(+) = p.project_id
    ORDER BY d.department_id, p.project_id, e.employee_id;
+----------------------------------+-------------------------------+------------------+
| DEPARTMENT_NAME                  | PROJECT_NAME                  | EMPLOYEE_NAME    |
|----------------------------------+-------------------------------+------------------|
| CUSTOMER SUPPORT                 | Detect false insurance claims | Alfred Mendeleev |
| RESEARCH                         | Detect fake product reviews   | Devi Nobel       |
| Department with no employees yet | Project with no employees yet | NULL             |
+----------------------------------+-------------------------------+------------------+

Perform two outer joins. This is the same as the preceding statement except that this uses (+) to make both joins into outer joins. The effect is that all departments are included (even if they have no projects or employees yet) and all projects associated with departments are included (even if they have no employees yet). Note that the output excludes projects that have no department.

SELECT d.department_name, p.project_name, e.employee_name
    FROM  departments d, projects p, employees e
    WHERE
            p.department_id(+) = d.department_id
        AND
            e.project_id(+) = p.project_id
    ORDER BY d.department_id, p.project_id, e.employee_id;
+----------------------------------------------+-------------------------------+------------------+
| DEPARTMENT_NAME                              | PROJECT_NAME                  | EMPLOYEE_NAME    |
|----------------------------------------------+-------------------------------+------------------|
| CUSTOMER SUPPORT                             | Detect false insurance claims | Alfred Mendeleev |
| RESEARCH                                     | Detect fake product reviews   | Devi Nobel       |
| Department with no employees yet             | Project with no employees yet | NULL             |
| Department with no projects or employees yet | NULL                          | NULL             |
+----------------------------------------------+-------------------------------+------------------+

(Remember, however, that Snowflake recommends using the OUTER keyword in the FROM clause rather than using the (+) operator in the WHERE clause.)