WHERE¶

筛选的简单示例¶

以下显示了 WHERE 子句的一些简单用法：

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

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

Copy

此示例使用子查询，显示所有账单金额小于平均水平的发票：

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

Copy

在 WHERE 子句中执行联接¶

要在 WHERE 子句中指定联接，请在 FROM clause 中列出要联接的表，用逗号分隔这些表。在 WHERE 子句中将联接条件指定为筛选器，如下例所示：

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

下一节将展示 3 表联接，并展示 0、1 或 2 (+) 外部联接运算符的行为差异。

在执行查询之前，创建并加载要在联接中使用的表：

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

执行 3 向内部联接。这不使用 (+) （或 OUTER 关键字），因此是一个内部联接。输出仅包括有部门、项目和员工的行：

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

执行外部联接。除了 使用 (+) 使第二个联接成为右外部联接之外，这与前面的语句类似。其效果是，如果某个部门包含在输出中，则该部门的所有项目都将包括在内，即使这些项目没有员工：

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

执行两个外部联接。这与前面的语句相同， 只是 它使用 (+) 将两个联接都变成外部联接。结果是所有部门都包括在内（即使他们还没有项目或员工），所有与部门相关的项目都包括在内（即使他们还没有员工）。请注意，输出不包括没有部门的项目。

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

（但是，请记住，Snowflake 建议在 FROM 子句中使用 OUTER 关键字，而不是在 WHERE 子句中使用 (+) 运算符。)