SQL 在外连接查询里使用OR逻辑|极客教程

SQL 在外连接查询里使用OR逻辑，你想返回部门编号为 10 和 20 的全体员工的名子和部门信息，以及部门编号为 30 和 40 的部门信息（但不包含员工信息）。你最初试图这样做。

SQL 在外连接查询里使用OR逻辑问题描述

你想返回部门编号为 10 和 20 的全体员工的名子和部门信息，以及部门编号为 30 和 40 的部门信息（但不包含员工信息）。你最初试图这样做。

select e.ename, d.deptno, d.dname, d.loc
  from dept d, emp e
 where d.deptno = e.deptno
   and (e.deptno = 10 or e.deptno = 20)
 order by 2
　
ENAME       DEPTNO DNAME          LOC
------- ---------- -------------- -------------
CLARK           10 ACCOUNTING     NEW YORK
KING            10 ACCOUNTING     NEW YORK
MILLER          10 ACCOUNTING     NEW YORK
SMITH           20 RESEARCH       DALLAS
ADAMS           20 RESEARCH       DALLAS
FORD            20 RESEARCH       DALLAS
SCOTT           20 RESEARCH       DALLAS
JONES           20 RESEARCH       DALLAS

以上查询里的连接操作是内连接，因此返回的结果集里不包含 DEPTNO 是 30 和 40 的部门信息。
在下面的查询里你又试图将 EMP 表外连接到 DEPT 表，但仍然没有得到正确的结果集。

select e.ename, d.deptno, d.dname, d.loc
  from dept d left join emp e
    on (d.deptno = e.deptno)
 where e.deptno = 10
    or e.deptno = 20
 order by 2
　
ENAME       DEPTNO DNAME          LOC
------- ---------- -------------- -----------
CLARK           10 ACCOUNTING     NEW YORK
KING            10 ACCOUNTING     NEW YORK
MILLER          10 ACCOUNTING     NEW YORK
SMITH           20 RESEARCH       DALLAS
ADAMS           20 RESEARCH       DALLAS
FORD            20 RESEARCH       DALLAS
SCOTT           20 RESEARCH       DALLAS
JONES           20 RESEARCH       DALLAS

其实，你只是希望得到如下所示的结果集。

ENAME       DEPTNO DNAME          LOC
------- ---------- -------------- ---------
CLARK           10 ACCOUNTING     NEW YORK
KING            10 ACCOUNTING     NEW YORK
MILLER          10 ACCOUNTING     NEW YORK
SMITH           20 RESEARCH       DALLAS
JONES           20 RESEARCH       DALLAS
SCOTT           20 RESEARCH       DALLAS
ADAMS           20 RESEARCH       DALLAS
FORD            20 RESEARCH       DALLAS
                30 SALES          CHICAGO
                40 OPERATIONS     BOSTON

SQL 在外连接查询里使用OR逻辑解决方案

DB2、MySQL、PostgreSQL 和 SQL Server
把 OR 条件移到 JOIN 子句里。

1  select e.ename, d.deptno, d.dname, d.loc
2    from dept d left join emp e
3      on (d.deptno = e.deptno
4          and (e.deptno=10 or e.deptno=20))
5   order by 2

或者，我们也可以先利用内嵌视图过滤 EMP.DEPTNO，然后再执行外连接。

1  select e.ename, d.deptno, d.dname, d.loc
2    from dept d
3    left join
4         (select ename, deptno
5            from emp
6           where deptno in ( 10, 20 )
7         ) e on ( e.deptno = d.deptno )
8   order by 2

Oracle
对于 Oracle 9i 及后续版本，上述针对其他数据库的解决方案也适用。除此之外，我们也可以使用 CASE 或 DECODE 的变通方案。下面是使用 CASE 的解决方案。

select e.ename, d.deptno, d.dname, d.loc
  from dept d, emp e
 where d.deptno = e.deptno (+)
   and d.deptno = case when e.deptno(+) = 10 then e.deptno(+)
                       when e.deptno(+) = 20 then e.deptno(+)
                  end
 order by 2

接下来的解决方案做法也是一样的，但用到了 DECODE 函数。

select e.ename, d.deptno, d.dname, d.loc
  from dept d, emp e
 where d.deptno = e.deptno (+)
   and d.deptno = decode(e.deptno(+),10,e.deptno(+),
                                     20,e.deptno(+))
 order by 2

如果用了 Oracle 专有的外连接语法“（+）”，并在外连接列上用到了 IN 或 OR 条件，查询操作就会返回错误。解决办法是把 IN 或 OR 条件移到一个内嵌视图中。

select e.ename, d.deptno, d.dname, d.loc
  from dept d,
      ( select ename, deptno
          from emp
         where deptno in ( 10, 20 )
       ) e
 where d.deptno = e.deptno (+)
 order by 2

SQL 在外连接查询里使用OR逻辑扩展知识

DB2、MySQL、PostgreSQL 和 SQL Server
针对这些数据库，我们提供了两种解决方案。第一种把 OR 条件移到了 JOIN 子句里，使它成为连接条件的一部分。这样一来，我们既能筛选出 EMP 表的数据，又不会丢掉 DEPT 表里 DEPTNO 等于 30 和 40 的数据。
第二种解决方案把过滤条件移到了内嵌视图里。内嵌视图 E 基于 EMP.DEPTNO 过滤数据，从 EMP 表里提取出我们感兴趣的行。然后，这些行被外连接到 DEPT 表。DEPT 表是外连接的基础表，因此包括部门编号为 30 和 40 在内的所有部门都会被返回。
Oracle
旧式的外连接语法似乎存在缺陷，我们使用了 CASE 和 DECODE 函数以避免该问题。除此之外，使用了内嵌视图 E 的那个解决方案，首先从 EMP 表里筛选出我们感兴趣的行，然后再外连接到 DEPT。