ORACLE连接 (+)和Join总

一.笛卡尔积

a.oracle写法

scott@ORCL> select count(1) from emp;

  COUNT(1)
----------
        14

scott@ORCL> select count(1) from dept;

  COUNT(1)
----------
         4


scott@ORCL> select count(1) from emp,dept;

  COUNT(1)
----------
        56

b.传统sql写法

scott@ORCL> select count(1) from emp cross join dept;

  COUNT(1)
----------
        56

二.等值连接（也叫作内连接）

a.oracle写法

scott@ORCL> select count(1) from emp a,dept b where a.deptno=b.deptno;

  COUNT(1)
----------
        14

b.传统sql写法

scott@ORCL> select * from emp a join dept b on a.deptno=b.deptno;

else1:等值连接的记录筛选

a.

scott@ORCL> select * from emp a,dept b where a.deptno=b.deptno and a.deptno=30;

b.

scott@ORCL> select * from emp a join dept b on a.deptno=b.deptno where a.deptno=30;

else2:不等值连接

a.

scott@ORCL> select * from emp a,salgrade b where a.sal>=b.losal and a.sal<=b.hisal;

b.

scott@ORCL> select * from emp a join salgrade b on a.sal between b.losal and b.hisal;

三.外连接

1.左外连接(left outer join)

a.

scott@ORCL> select * from emp a,dept b where a.deptno=b.deptno(+);

b.

scott@ORCL> select * from emp a left outer join dept b on a.deptno=b.deptno;

2.右外连接

a.

scott@ORCL> select * from emp a,dept b where a.deptno(+)=b.deptno;

b.

scott@ORCL> select * from emp a right outer join dept b on a.deptno=b.deptno;

3.全外连接

a.

scott@ORCL> select * from emp a,dept b where a.deptno(+)=b.deptno union 
  2  select * from emp a,dept b where a.deptno=b.deptno(+);

b.

scott@ORCL> select * from emp a full outer join dept b on a.deptno=b.deptno;

四.自然连接：根据两个表的相同字段

scott@ORCL> select * from emp natural join dept b;

如果两张表没有相同字段会报错：没有选定行

五.using字句：指定表的某个字段作为连接的字段

scott@ORCL> select * from emp a join dept b using(deptno);