外连接(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更是,千万不能让一时的收获蒙蔽了自己,自己还需花更多的时间去学习。继续加油!