SQL 新增连接查询而不影响其他连接查询,你已经有了一个查询语句,它可以返回你想要的数据。你需要一些额外信息,但当你试图获取这些信息的时候,却丢失了原有的查询结果集中的数据。例如,你想查找所有员工的信息,包括他们所在部门的位置,以及他们收到奖金的日期。针对这个问题,EMP_BONUS
表包含了如下数据。
SQL 新增连接查询而不影响其他连接查询 问题描述
你已经有了一个查询语句,它可以返回你想要的数据。你需要一些额外信息,但当你试图获取这些信息的时候,却丢失了原有的查询结果集中的数据。例如,你想查找所有员工的信息,包括他们所在部门的位置,以及他们收到奖金的日期。针对这个问题,EMP_BONUS
表包含了如下数据。
select * from emp_bonus
EMPNO RECEIVED TYPE
---------- ----------- ----------
7369 14-MAR-2005 1
7900 14-MAR-2005 2
7788 14-MAR-2005 3
最初,你使用如下所示的查询语句。
select e.ename, d.loc
from emp e, dept d
where e.deptno=d.deptno
ENAME LOC
---------- -------------
SMITH DALLAS
ALLEN CHICAGO
WARD CHICAGO
JONES DALLAS
MARTIN CHICAGO
BLAKE CHICAGO
CLARK NEW YORK
SCOTT DALLAS
KING NEW YORK
TURNER CHICAGO
ADAMS DALLAS
JAMES CHICAGO
FORD DALLAS
MILLER NEW YORK
对于有奖金的员工,你希望把他们收到奖金的日期也添加到结果集里,但连接了 EMP_BONUS
表后得到的行数却比预期的要少,因为并非所有的员工都有奖金。
select e.ename, d.loc,eb.received
from emp e, dept d, emp_bonus eb
where e.deptno=d.deptno
and e.empno=eb.empno
ENAME LOC RECEIVED
---------- ------------- -----------
SCOTT DALLAS 14-MAR-2005
SMITH DALLAS 14-MAR-2005
JAMES CHICAGO 14-MAR-2005
而你希望得到如下所示的结果集。
ENAME LOC RECEIVED
---------- ------------- -----------
ALLEN CHICAGO
WARD CHICAGO
MARTIN CHICAGO
JAMES CHICAGO 14-MAR-2005
TURNER CHICAGO
BLAKE CHICAGO
SMITH DALLAS 14-MAR-2005
FORD DALLAS
ADAMS DALLAS
JONES DALLAS
SCOTT DALLAS 14-MAR-2005
CLARK NEW YORK
KING NEW YORK
MILLER NEW YORK
SQL 新增连接查询而不影响其他连接查询 解决方案
使用外连接既能够获得额外信息,又不会丢失原有的信息。首先连接 EMP
表和 DEPT
表,得到全部员工和他们所在部门的位置。然后外连接 EMP_BONUS
表,如果某个员工有奖金,则检索其收到奖金的日期。下面是 DB2、MySQL、PostgreSQL 以及 SQL Server 的查询语法。
1 select e.ename, d.loc, eb.received
2 from emp e join dept d
3 on (e.deptno=d.deptno)
4 left join emp_bonus eb
5 on (e.empno=eb.empno)
6 order by 2
对于 Oracle 9i 数据库及其后续版本,上述解决方案仍然适用。除此之外,对于 Oracle 8i 数据库及更早的版本,可以使用 Oracle 专有的外连接语法。
1 select e.ename, d.loc, eb.received
2 from emp e, dept d, emp_bonus eb
3 where e.deptno=d.deptno
4 and e.empno=eb.empno (+)
5 order by 2
也可以使用标量子查询(即把子查询放置在 SELECT
列表里)来模仿外连接操作。
1 select e.ename, d.loc,
2 (select eb.received from emp_bonus eb
3 where eb.empno=e.empno) as received
4 from emp e, dept d
5 where e.deptno=d.deptno
6 order by 2
标量子查询解决方案适用于所有数据库。
SQL 新增连接查询而不影响其他连接查询 扩展知识
外连接查询会返回一个表中的所有行,以及另一个表中与之匹配的行。上一个实例中也出现了这种连接操作。外连接之所以能够解决本问题,是因为它不会过滤掉任何应该被返回的行。上述外连接查询返回的行数和没有外连接时一样多。而且,如果有收到奖金的日期,它也会返回那个日期。
使用标量子查询是解决本问题的一种巧妙做法,因为不需要修改主查询中正确的连接操作。在不破坏当前结果集的情况下,标量子查询是为现有查询语句添加额外数据的好办法。当使用标量子查询时,必须确保它们返回的是标量值(单值)。如果 SELECT
列表里的子查询返回多行,那么查询将会出错。