SQL查询高级特性深度解析

### SQL查询高级特性深度解析 #### 1. EXISTS操作符 在SQL查询中，相关子查询常常会与`EXISTS`操作符一同使用。下面通过一个例子来详细说明。 示例查询（清单9 - 8）用于返回所有没有注册记录的课程安排： ```sql select o.* from offerings o where not exists (select r.* from registrations r where r.course = o.course and r.begindate = o.begindate); ``` 查询结果如下： | COURSE | BEGINDATE | TRAINER | LOCATION | | ------ | ----------- | ------- | -------- | | ERM | 15 - JAN - 2001 | | | | PRO | 19 - FEB - 2001 | | DALLAS | | RSD | 24 - FEB - 2001 | 7788 | CHICAGO | | XML | 18 - SEP - 2000 | | BOSTON | `EXISTS`操作符并不关注子查询实际返回的行（以及列值），它仅仅检查子查询是否有结果返回。如果子查询至少返回一行，`EXISTS`操作符的计算结果为`TRUE`；如果子查询没有返回任何行，结果则为`FALSE`。 跟在`EXISTS`操作符后面的子查询通常是相关子查询。若子查询为非相关子查询，对于主查询的每一行，其结果都是相同的，只有两种可能的结果：`EXISTS`操作符对所有行的计算结果都为`TRUE`，或者都为`FALSE`。也就是说，`EXISTS`后面跟非相关子查询时，它变成了一个“全有或全无”的操作符。 需要注意的是，子查询返回空值与子查询返回空集（即没有任何行）是不同的，这一点将在后续部分进行演示。 #### 2. EXISTS、IN还是JOIN？ 下面通过另一个`EXISTS`的例子来结束这部分内容。该查询旨在提供所有曾经教授过SQL课程的员工的个人详细信息。 使用`EXISTS`操作符的相关子查询（清单9 - 9）： ```sql select e.* from employees e where exists (select o.* from offerings o where o.course = 'SQL' and o.trainer = e.empno); ``` 查询结果如下： | EMPNO | ENAME | INIT | JOB | MGR | BDATE | MSAL | COMM | DEPTNO | | ------ | ------ | ---- | ------- | ---- | ----------- | ---- | ---- | ------ | | 7369 | SMITH | N | TRAINER | 7902 | 17 - DEC - 1965 | 800 | | 20 | | 7902 | FORD | MG | TRAINER | 7566 | 13 - FEB - 1959 | 3000 | | 20 | 这个问题也可以使用`IN`操作符来解决（清单9 - 10）： ```sql select e.* from employees e where e.empno in (select o.trainer from offerings o where o.course = 'SQL') ``` 还可以使用`JOIN`来解决该问题（清单9 - 11）： ```sql select DISTINCT e.* from employees e join offerings o on e.empno = o.trainer where o.course = 'SQL' ``` 注意`SELECT`子句中的`DISTINCT`选项。如果移除清单9 - 11中的`DISTINCT`选项，查询结果将包含三行，而不是两行。如果一名员工教授了SQL课程的多个安排，清单9 - 11中的查询可能会返回该员工的多个实例。由于清单9 - 9中的查询使用了相关子查询，所以只返回两行，因为员工一旦与相关子查询中选择的培训师值匹配，就只会被添加到结果集中一次。 #### 3. 子查询结果中的NULL问题 在为Oracle数据库系统编写SQL时，子查询结果中`NULL`与`EXISTS`和`IN`的使用常常会给人们带来问题，尤其是那些习惯为其他数据库系统编写SQL的人。`NULL`不仅会导致混淆，还可能导致结果错误。 需要牢记以下几个关键概念： - `NULL`不是数据，而是数据未知的一种状态。 - `Null = Null`、`NULL != NULL` 或 `NULL IN (NULL)`的计算结果始终为`UNKNOWN`，既不是`TRUE`也不是`FALSE`。 - 不能在连接列中使用`NULL`来连接两行。 下面通过清单9 - 12中的报告来说明`NULL`在`EXISTS`和`IN`查询中带来的问题。这两个查询展示了生成经理列表的两种不同方式，一种使用`IN`，另一种使用`EXISTS`。从表面上看，两种方法都可行，且结果似乎没有差异。 使用`IN`选择所有经理（清单9 - 12）： ```sql select ename from employees where empno in (select mgr from employees); ``` 查询结果如下： | ENAME | | ------ | | JONES | | BLAKE | | CLARK | | SCOTT | | KING | | FORD | 使用`EXISTS`选择所有经理（清单9 - 12）： ```sql select e1.ename from employees e1 where exists (select e2.mgr from employees e2 where e1.empno = e2.mgr); ``` 查询结果如下： | ENAME | | ------ | | JONES | | BLAKE | | CLARK | | SCOTT | | KING | | FORD | 从清单9 - 12可以看出，`IN`和`EXISTS`在结果上是等价的，但实际操作不同。`IN`会构建一个值列表，用于与`EMPNO`进行比较；`EXISTS`会为每个`EMPNO`执行子查询，如果连接找到匹配的`EMPNO`，则返回`TRUE`。这两个查询返回相同的结果，只是因为在`EMPNO`到`MGR`值的评估中没有涉及`NULL`。如果存在`NULL`的`EMPNO`，`EXISTS`子查询将不会为该员工编号返回记录，因为`NULL`的`EMPNO`值不会与`NULL`的`MGR`值进行连接（`NULL = NULL`的计算结果不为`TRUE`）。 `EXISTS`回答的问题是：“这个值是否存在于指定的表列中？” 如果该值存在（即子查询至少返回一行），答案为是，`EXISTS`表达式的计算结果为`TRUE`。由于`NULL`不能进行相等比较，将`NULL`的`MGR`与`NULL`的`EMPNO`进行连接不会返回`TRUE`。本质上，