多表查询

多表查询

概念

多表查询的推荐写法

查询雇员和其所在部门信息：

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

多表查询时，相同字段名需要跟上表名称。但如果表名称过长，就容易导致WHERE子句可读性降低。所以推荐多表查询时，为表名称添加别名，例子如下：

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

表的连接

概念

数据库表的连接分为两种：内连接、外连接。

• 内连接指的所有满足关联条件的数据出现，不满足的不出现。
• 外链接指指定一张数据表中的全部内容都显示，但是没有对应的其他表数据，内容都为null。

语法

在Oracle中使用了”(+)”来控制连接方式：

• 左外连接：关联字段1 = 关联字段2(+);
• 右外连接：关联字段1(+) = 关联字段2;

准备工作

利用Oracle数据库安装时所预装的数据表中的数据还不足以观察到两种连接方式的区别。还需要做一下的操作：

1）插入一条部门编号为空的雇员信息：

INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (8989, 'HELLO', NULL);

插入后，雇员表中记录为15条。

2）明确Dept（部门）表中存在部门编号为40的部门，而Emp（雇员）表中没有部门编号为40的雇员。

内连接

利用WHERE子句来消除笛卡尔积。只有满足条件的数据才会连接。

接下来查看内连接实现效果。

查询雇员的编号、姓名、部门编号、部门名称：

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

执行后发现，没有部门的雇员以及没有雇员的部门信息都没有出现，因为null的判断不满足。

外连接

分为三种，左（外）连接、右外连接、全外连接。

使用左外连接

使所有的雇员信息都显示出来，即便他没有部门信息。

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);

此时可以发现，没有部门的雇员信息Hello也被查询了出来，但它的部门信息为null。即左表的全部数据显示了出来。

使用右外连接

使所有的部门信息都显示出来。

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;

没有雇员信息的40部门被查询了出来，但它的雇员信息为null。即右表的全部数据显示了出来。

实例

查询雇员的编号、姓名、职位、领导姓名、领导职位：

SELECT E.EMPNO, E.ENAME, E.JOB, M.ENAME, M.JOB
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO(+);

使用左外连接使得领导为空的雇员信息得以显示出来。

SQL:1999语法

数据表的连接操作，各个数据库通用的写法应该是：

SELECT [DISTINCT] * | 列 [别名]， 列 [别名], .....
FROM 表1 [别名]
    [CROSS JOIN 表2 [别名]]
    [NATURAL JOIN 表2 [别名]]
    [JOIN 表2 [别名] ON(条件) | USING(关联字段)]
    [LEFT | RIGHT | FULL OUTER JOIN ON(条件) 表2]；

在进行表连接的时候，内连接使用等值判断，外连接才会使用LEFT|RIGHT等字段。

交叉连接

产生笛卡尔积。

语法

SELECT [DISTINCT] * | 列 [别名]， 列 [别名], .....
FROM 表1 [别名] CROSS JOIN 表2 [别名];

实例

SELECT *
FROM EMP CROSS JOIN DEPT;

产生笛卡尔乘积，等同于：

SELECT *
FROM EMP, DEPT;

自然连接（内连接）

利用关联字段进行笛卡尔积的消除（只要字段名称相同自己匹配）。

语法

SELECT [DISTINCT] * | 列 [别名]， 列 [别名], .....
FROM 表1 [别名] NATURAL JOIN 表2 [别名];

实例

SELECT *
FROM EMP NATURAL JOIN DEPT;

等同于内连接，等同于：

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

特殊的

当两张表中不存在相同的字段名称或存在多个相同的字段名称时，我们就需要使用ON子句设置关联条件，而利用USING子句设置关联字段。

利用ON子句设置关联条件实现自然连接：

SELECT * 
FROM EMP E JOIN DEPT D ON(E.DEPTNO = D.DEPTNO)

利用USING子句设置关联字段实现自然连接：

SELECT *
FROM EMP JOIN DEPT USING(DEPTNO)

外连接

语法

SELECT [DISTINCT] * | 列 [别名]， 列 [别名], .....
FROM 表1 [别名] LEFT | RIGHT | FULL OUTER JOIN 表2；

实例

左外连接

SELECT *
FROM EMP E LEFT OUTER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);

等同于：

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);

右外连接

SELECT *
FROM EMP E RIGHT OUTER JOIN DEPT D ON(E.DEPTNO = D.DEPTNO);

等同于：

SELECT *
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;

数据集合操作

数学集合：交集、并集、差集、补集。

语法

SELECT [DISTINCT] *|列 [别名], 列 [别名], 列 [别名].....
FROM 表名称 [别名]
[WHERE 限定条件(s)]
[ORDER BY 排序字段[ASC|DESC], 排序字段[ASC|DESC], .....]
    UNION | UNION ALL | INTERSECT | MINUS
SELECT [DISTINCT] *|列 [别名], 列 [别名], 列 [别名].....
FROM 表名称 [别名]
[WHERE 限定条件(s)]
[ORDER BY 排序字段[ASC|DESC], 排序字段[ASC|DESC], .....];

即将两个或多个查询结果进行集合运算。

• UNION：合并查询结果，且消除重复。
• UNION ALL：合并所有查询结果，不消除结果。
• INTERSECT：返回交集。
• MINUS：返回差集。

注意：由于集合的操作的结果是将多个查询结果进行合并，所以要求若干个查询结果返回的数据结构必须相同。