Franky's Blog

书山有路勤为径，学海无涯苦作舟……

SQL|EXISTS

   Franky    SQL    ,

WHERE EXISTS ( subquery )

  • EXISTS用在相关子查询中
  • The result of EXISTS is a boolean value True or False，EXISTS returns true if the subquery returns one or more records.
  • It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

不相关子查询

查询在 Sales 部门的员工

 1SELECT *
 2  FROM employees a
 3 WHERE a.dept_id IN (SELECT b.dept_id FROM departments b WHERE b.dept_name = 'Sales');
 4
 5+-----------------+-----------+------------+--------+---------+
 6| employee_number | last_name | first_name | salary | dept_id |
 7+-----------------+-----------+------------+--------+---------+
 8|            1003 | Everest   | Brad       |  71000 |     501 |
 9|            1004 | Horvath   | Jack       |  42000 |     501 |
10+-----------------+-----------+------------+--------+---------+
11

首先通过子查询得到部门名称为 Sales 的部门编号，然后查找 employees （员工）表中的部门编号(a.dept_id)为 Sales 这个部门的编号(b.dept_id)的记录。最终我们可以得到所有在部门 Sales 的员工。

这种类型的查询是先执行子查询，得到一个集合（或值），然后将这个集合（或值）作为一个常量带入到父查询的 WHERE 子句中去。如果单纯地执行子查询，也是可以成功的。

这种类型的查询，叫做 “不相关子查询”

相关子查询

查询每个部门中薪水大于平均工资的员工

 1
 2franky@localhost [test]>select * from employees;
 3+-----------------+-----------+------------+--------+---------+
 4| employee_number | last_name | first_name | salary | dept_id |
 5+-----------------+-----------+------------+--------+---------+
 6|            1001 | Smith     | John       |  62000 |     500 |
 7|            1002 | Anderson  | Jane       |  57500 |     500 |
 8|            1003 | Everest   | Brad       |  71000 |     501 |
 9|            1004 | Horvath   | Jack       |  42000 |     501 |
10+-----------------+-----------+------------+--------+---------+
11
12franky@localhost [test]>select * from employees a 
13                         where salary >= (select avg(salary) 
14                                            from employees b 
15                                           where a.dept_id = b.dept_id);
16+-----------------+-----------+------------+--------+---------+
17| employee_number | last_name | first_name | salary | dept_id |
18+-----------------+-----------+------------+--------+---------+
19|            1001 | Smith     | John       |  62000 |     500 |
20|            1003 | Everest   | Brad       |  71000 |     501 |
21+-----------------+-----------+------------+--------+---------+
22
23

扫描父表中的每一条记录，然后将当前这条记录中的，在子查询中会用到的父表的值(a.dept_id)代入到子查询中去，然后执行子查询并得到结果，然后再将这个结果代入到父查询的条件中，判断父查询的条件表达式的值是否为 True，若为 True，则将当前父表中的这条记录放到结果集中去。若为 False 则不放。

只要子查询不能脱离父查询单独执行，这样的查询就是相关子查询。

EXISTS

EXISTS 关键字的作用，就是判断子查询得到的结果集是否是一个空集，如果不是，则返回 True，如果是，则返回 False。

获取至少下过一次订单的客户名称

 1SELECT first_name, last_name
 2  FROM customers a
 3 WHERE EXISTS (SELECT * FROM orders b WHERE a.customer_id = b.customer_id);
 4
 5+------------+-----------+
 6| first_name | last_name |
 7+------------+-----------+
 8| Joe        | Jackson   |
 9| Jane       | Smith     |
10| Allen      | Reynolds  |
11| Paige      | Anderson  |
12+------------+-----------+
13

在这个查询中，首先会取出 customers 表中的第一条记录，得到其 customer_id 列的值，然后将该值代入到子查询中。若能找到这样的一条记录，说明该客户有订单。因为能找到这样的一条记录，所以子查询的结果不为空集，那么 EXISTS 会返回 True，从而使 customers 表中的第一条记录中的 first_name，last_name 列的值被放入结果集中去。以此类推，遍历 customers 表中的所有记录后，就能得到下过订单的客户的名称。

与 EXISTS 关键字相对的是 NOT EXISTS，作用与 EXISTS 正相反，当子查询的结果为空集时，返回 True，反之返回 False。也就是所谓的 “若不存在”。

 1SELECT first_name, last_name
 2  FROM customers a
 3 WHERE NOT EXISTS (SELECT * FROM orders b WHERE a.customer_id = b.customer_id);
 4
 5+------------+-----------+
 6| first_name | last_name |
 7+------------+-----------+
 8| Samantha   | Ferguson  |
 9| Derek      | Johnson   |
10+------------+-----------+
11

Share