ORACLE连接查询，内外连接-优快云博客

事情的起源是这样子的：
在《剑破冰山》这本书里看到了一个用 where限制外连接的例子，当时不明白，查了下资料，做个总结。

测试代码。
CREATE TABLE a (ID NUMBER,NAME VARCHAR2(10));
CREATE TABLE b (ID NUMBER,NAME VARCHAR2(10));
insert into a values(1,'a');
insert into a values(2,'b');
insert into a values(3,'c');
insert into b values(1,'a');
insert into b values(2,'b');
insert into b values(4,'d');
commit;

先给个连接：http://apps.hi.baidu.com/share/detail/34752243

查询语句分类如下：
1.单表查询
2.多表查询(交叉连接、内连接、外连接）

一.单表查询
单表查询:用where过滤表的记录形成中间表，然后select 语句选出要显示的列。

二.多表查询(对多个表按照一定的条件进行关联查询)
关于多表查询，上面的连接中的说法“两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果”是有问题的，dingjun123作了说明http://www.itpub.net/thread-1506543-1-1.html我就不讲了，免得讲错了误导大家。

下面介绍连接查询。

1.内连接
用的最多的连接查询，只返回表中符合条件的行。如：
select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno and a.job in('SALESMAN',''MANAGER)
或：select a.ename,b.dname from emp a inner join dept b on a.deptno=b.deptno where a.job in ...
或：select a.ename,b.dname from emp a inner join dept b on a.deptno=b.deptno and a.job in ...
（内连接时三种写法都可以，外连接不一样）

2.左外连接
左外连接：除了返回符合条件的行外，还返回左表中所有的行。
select a.ename ,b.dname from emp a left join dept b on a.deptno=b.deptno and a.job in ('SALESMAN','MANAGER');
select a.ename ,b.dname from emp a left join dept b on a.deptno=b.deptno where a.job in ('SALESMAN','MANAGER');
select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno(+) and a.job in ...
注意：条件写在where中和定在on中是不一样的，一会分析。

3.右外连接
右外连接：除了返回符合条件的行外，还返回右表中所有的行。
select a.ename ,b.dname from emp a right join dept b on a.deptno=b.deptno and a.empno=7788;
select a.ename ,b.dname from emp a right join dept b on a.deptno=b.deptno where a.empno=7788;
select a.ename ,b.dname from emp a , dept b where a.deptno(+)=b.deptno and a.empno=7788;
注意：条件写在where中和定在on中是不一样的，一会分析。

4.全外连接
全外连接：除和返回符合条件的行外，还返回左表和右表中不符合条件的行
select a.ename ,b.dname from emp a full join dept b on a.deptno=b.deptno and a.empno=7788;
select a.ename ,b.dname from emp a full join dept b on a.deptno=b.deptno where a.empno=7788;
用(+)无法写出全外连接，必须与union(或union all) 一起用才行。

5.交叉连接
交叉连接：即不写连接条件或无效的连接条件形成笛卡尔积。
select a.ename ,b.dname from emp a cross join dept b ;
select a.ename ,b.dname from emp a ,dept b ;

条件放在ON和放在WHERE后的区别：
1.放在ON后面，条件作为形成中间集的限制条件：
select a.ename ,b.dname from emp a left join dept b on a.deptno=b.deptno and a.job in ('SALESMAN','MANAGER');
ENAME      DNAME
---------- --------------
mary
Sean
SMITH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT
KING
TURNER     SALES
ADAMS
JAMES
FORD
MILLER
在形成中间集果，会找到a.job in ('SALESMAN','MANAGER')这些行，获得这些行的deptno值，通过a.deptno=b.deptno得到对应的b.deptno的值，通过这些值进行左连接，结果会显示a表的所有行和通过a.jon in得到的b.deptno的行.即先通过限制条件选出连接行，再生成中间集，条件作用在生成中间集之时。

2.在where后面，生成中间集，再通过条件选择出中间集中符合条件的行
select a.ename ,b.dname from emp a join dept b on a.deptno=b.deptno where a.job in ('SALESMAN','MANAGER');
先select a.ename ,b.dname from emp a join dept b on a.deptno=b.deptno 形成中间集，再在这个集合中选择a.job in('SALESMAN','MANAGER')的行。
ENAME      DNAME
---------- --------------
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
TURNER     SALES

看到结果，应该明白了吧
所以，要在外连接时只让特定的行参与连接，则把条件放在on后面，要想在连接后对结果进行过滤，条件放在where中。
另外：select a.ename ,b.dname from emp,dept b where a.deptno=b.deptno and a.job in ('SALESMAN','MANAGER');
相当于：select a.ename ,b.dname from emp a join dept b on a.deptno=b.deptno where a.job in ('SALESMAN','MANAGER');
。

下在看一个更深入一点的吧：
SELECT * FROM a,b WHERE a.ID=decode(a.NAME,'a',b.id(+));
这个结果是什么呢？
提示：只要知道了a.name='a'是在形成中间集时的限制条件还是对中间集筛选时的限制条件，答案就出来了。

12月15日更新：
select a.ename ,b.dname,b.deptno from emp a left join dept b on a.deptno=b.deptno and b.deptno =30;
select a.ename ,b.dname,b.deptno from emp a left join dept b on a.deptno=b.deptno where b.deptno =30;
select a.ename,b.dname,b.deptno from emp a,dept b where a.deptno=b.deptno(+) and b.deptno(+) =30;
在做了外连接的表上通过过滤条件筛选时（第三个语句），在字段上加(+)(如：b.deptno(+) =30)会得到与第一个语句一样的效果，先选择再外连接。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24478467/viewspace-709924/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24478467/viewspace-709924/