WHERE¶

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;

Copy

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

Copy

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 |
+------+------+

Copy

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

Copy

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       |
+------------------+-------------------------------+------------------+

Copy

Perform an outer join. This is similar to the preceding statement except that this uses (+) to make the second join a right 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             |
+----------------------------------+-------------------------------+------------------+

Copy

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             |
+----------------------------------------------+-------------------------------+------------------+

Copy

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