Oracle学习笔记（四）：多表查询（二）

外连接(OUTER JOIN) , 交叉连接（CROSS JOIN），和三个操作符（UNION , INTERSECT , MINUS）

外连接(OUTER JOIN)

我们将建立两个表，说明下面sql语句的使用原理

A表：

B表：

CREATE TABLE A(                 --A表  id NUMBER(2) ,  Aname VARCHAR2(5) ) ;CREATE TABLE B(               --B表  id NUMBER(2) ,  Bname VARCHAR2(5)) ;

这两个表中有一些id相同，有一些不同。

我们INSERT:

INSERT INTO a VALUES(1,'A01') ;INSERT INTO a VALUES(6,'A06') ;INSERT INTO a VALUES(7,'A07') ;INSERT INTO a VALUES(8,'A08') ;INSERT INTO a VALUES(12,'A12') ;INSERT INTO a VALUES(13,'A13') ;INSERT INTO a VALUES(14,'A14') ;INSERT INTO B VALUES(2,'B02') ;INSERT INTO B VALUES(3,'B03') ;INSERT INTO B VALUES(4,'B04') ;INSERT INTO B VALUES(5,'B05') ;INSERT INTO B VALUES(6,'B06') ;INSERT INTO B VALUES(7,'B07') ;INSERT INTO B VALUES(8,'B08') ;

对于外连接，Oracle中可以使用（+）来表示，也可以使用LEFT,RIGHT和FULL OUTER JOIN实现多个表外连接

外连接分为3类
1，左外连接(LEFT OUTER JOIN或LEFT JOIN)
2，右外连接(RIGHT OUTER JOIN或RIGHT JOIN)
3，全外连接(FULL OUTER JOIN或FULL JOIN)
使用外连接，可以列出与连接条件相匹配的行，并且列出左表（左外连接时），右表（右外连接时）或两个表（全外连接时），所有符合检索条件的数据行

1，左外连接：

SQL> SELECT a.id , a.aname , b.id , b.bname  2  FROM a LEFT OUTER JOIN b  3  ON a.id = b.id ;        ID ANAME         ID BNAME---------- ----- ---------- -----         6 A06            6 B06         7 A07            7 B07         8 A08            8 B08        13 A13        12 A12         1 A01        14 A14已选择7行。

~注：从定义的A表B表看来，只有id=6,7,8时两个表的ID才会相同，而left outer join会把左表，也就是a表与条件不匹配的也输出来。而对于INNER JOIN ，我们可以对比一下效果：

SQL> SELECT a.id , a.aname , b.id , b.bname  2  FROM a INNER JOIN b  3  ON a.id = b.id ;        ID ANAME         ID BNAME---------- ----- ---------- -----         6 A06            6 B06         7 A07            7 B07         8 A08            8 B08

实际上外连接也可以这样来写：

SQL> SELECT a.id , a.aname , b.id , b.bname  2  FROM a INNER JOIN b  3  ON a.id = b.id(+) ;        ID ANAME         ID BNAME---------- ----- ---------- -----         6 A06            6 B06         7 A07            7 B07         8 A08            8 B08        13 A13        12 A12         1 A01        14 A14已选择7行。

与前面效果一样。注意：这里的(+)要与连接的方向相反。

因此，我们可以把外连接看成是内连接加上左表（当外连接为左时），右表（当外连接为右时）没匹配的数据。

2，右外连接（与左连接类似，只是方向变了）

3，全外连接

这个其实也只是左连接与右连接的综合，比较好理解。

SQL> SELECT a.id , a.aname , b.id , b.bname  2  FROM a FULL OUTER JOIN b  3  ON a.id = b.id ;        ID ANAME         ID BNAME---------- ----- ---------- -----                          2 B02                          3 B03                          4 B04                          5 B05         6 A06            6 B06         7 A07            7 B07         8 A08            8 B08        13 A13        12 A12         1 A01        14 A14已选择11行。

~注：从上面我们可以看出，相同id的两个表都同时在，而id不同的则输出空的值。

交叉连接

除此之外，外连接还有CROSS JOIN,也就是交叉连接，所得到的结果将是这两个表中各行数据的所有组合，即这个表所有数据行的笛卡尔积。

交叉连接与简单连接非常相似，不同的是，使用交叉连接时，在FROM字句中多个表名之间使用的不是逗号，而是使用CROSS JOIN隔开，而且不需要用ON指定条件，但可以在WHERE中添加。

SQL> SELECT a.id , a.aname , b.id , b.bname  2  FROM a CROSS JOIN b  3  WHERE a.id = b.id ;        ID ANAME         ID BNAME---------- ----- ---------- -----         6 A06            6 B06         7 A07            7 B07         8 A08            8 B08

当没有WHERE条件时，将返回：

~注：从上面看，我们发现，交叉连接与之前的简单连接差不多，若没有where语句限制，也会产生笛卡尔积。

UNION , INTERSECT , MINUS操作符

我们继续拿SCOTT的emp表作为例子：

选取empno>7800的员工信息作为集合A，部门号为10的员工信息作为集合B

集合A:

SQL> SELECT empno , ename , sal ,deptno  2  FROM emp  3  WHERE empno > 7800 ;     EMPNO ENAME             SAL     DEPTNO---------- ---------- ---------- ----------      7839 KING             5000         10      7844 TURNER           1500         30      7876 ADAMS            1100         20      7900 JAMES             950         30      7902 FORD             3000         20      7934 MILLER           1300         10已选择6行。

集合B:

SQL> SELECT empno , ename , sal ,deptno  2  FROM emp  3  WHERE deptno = 10 ;     EMPNO ENAME             SAL     DEPTNO---------- ---------- ---------- ----------      7782 CLARK            2450         10      7839 KING             5000         10      7934 MILLER           1300         10

直接看我们知道empno=7839与7934时A和B是相同的

下面我们分类进行解释与试验：

SQL语言中的MINUS集合运算，表示获得给定集合之间的差异，也就意味着所得到的结果集中，其中的元素仅存在于前一个集合中，而不存在于另一集合中

如图所示：

SQL> SELECT empno , ename , sal ,deptno  2  FROM emp  3  WHERE empno > 7800  4  MINUS  5  SELECT empno , ename , sal ,deptno  6  FROM emp  7  WHERE deptno = 10 ;     EMPNO ENAME             SAL     DEPTNO---------- ---------- ---------- ----------      7844 TURNER           1500         30      7876 ADAMS            1100         20      7900 JAMES             950         30      7902 FORD             3000         20

INTERSECT获取公共行，也称为获取结果集的交集

如图所示：

SQL> SELECT empno , ename , sal ,deptno  2  FROM emp  3  WHERE empno > 7800  4  INTERSECT  5  SELECT empno , ename , sal ,deptno  6  FROM emp  7  WHERE deptno = 10 ;     EMPNO ENAME             SAL     DEPTNO---------- ---------- ---------- ----------      7839 KING             5000         10      7934 MILLER           1300         10

UNION返回多个查询返回的行组合起来：

SQL> SELECT empno , ename , sal ,deptno  2  FROM emp  3  WHERE empno > 7800  4  UNION  5  SELECT empno , ename , sal ,deptno  6  FROM emp  7  WHERE deptno = 10 ;     EMPNO ENAME             SAL     DEPTNO---------- ---------- ---------- ----------      7782 CLARK            2450         10      7839 KING             5000         10      7844 TURNER           1500         30      7876 ADAMS            1100         20      7900 JAMES             950         30      7902 FORD             3000         20      7934 MILLER           1300         10

~注：从上面我们看出，这三个运算符，只是在两个SELECT语句中直接插入UNION,MINUS,INTERSECT三个之一。学过集合论的人对这些都不难理解。我们把每次SELECT得到的结果看成是一个集合，那么三面的SQL语句我们就可以把它看成是集合间的运算了。

~综：综合这两篇博客所学到的东西，我看到了Oracle多表连接查询有很多看上去非常复杂的东西，比如INNER JOIN,OUTER JOIN ,CROSS JOIN等等这些，而当我逐步弄清楚后，就会发现这些其实只是多表查询的基础的一些迁移，原理还是一样，只要理解了多表查询的思想，就可以很容易理解外连接，内连接这些东西了。但我觉得这也只是基础。学会了很多SQL的东西，却不一定可以写出好的SQL语句，甚至有些SQL语句也写不出。学习是个积累的过程，对于博大精深的Oralce更是，千万不能让一时的收获蒙蔽了自己，自己还需花更多的时间去学习。继续加油！