Oracle inner join、left join、right join 、+左边或者右边的区别

为了解释这些问题，假设我们有一个表 t1

 num | name
-----+------
   1 | a
   2 | b
   3 | c

和 t2

 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

然后我们用不同的连接方式可以获得各种结果：

=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

 

用 ON 声明的连接条件也可以包含与连接不直接相关 的条件。这种功能可能对某些查询很有用，但是需要我们仔细想想。 比如：

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)

 

 

 

 

 

 ****************************************************************

 

 

 

我们以Oracle自带的表来做例子

主要两张表：dept、emp

一个是宠物，一个是员工表结构如下：

emp

name	null?	Type
Empno	not null	number(4)
ename		varchar2(10)
job		varchar2(9)
mgr		number(4)
hiredate		date
sal		number(7,2)
comm		number(7,2)
deptno		number(2)

dept

name	null?	Type
deptno	not null	number(2)
dname		varchar2(14)
log		varchar2(13)

这两张表 dept是主表 emp是子表，关联的列是deptno

dept表现有数据

 

emp表现有数据

 

 

inner join 意思是内连接 把匹配的信息全部查出来

SQL>select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e inner join dept d on e.deptno=d.deptno order by e.empno;

复制代码

查询的结果：

left join 左连接 意思是包含左边表所有记录，右边所有的匹配的记录，如果没有则用空补齐

SQL>select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e left join dept d on e.deptno=d.deptno order by e.empno;

复制代码

解释SQL :这条SQL语句左边是EMP表 左连接结果查询出EMP所有的记录，然后根据左边表匹配出右边表DEPT所有的记录

查询结果如下： 

right join 右连接  意思是包括右边表所有记录，匹配左边表的记录，如果没有则以空补齐

SQL>select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e right join dept d on e.deptno=d.deptno order by e.empno;

复制代码

解释SQL：这条SQL语句EMP在左边，DEPT在右边，然后我们采用右连接，就查出右边表所有的数据

查询结构：

full join  全连接 意思是左右表所有的记录全部显示出来

SQL>select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e full join dept d on e.deptno=d.deptno order by e.empno;

复制代码

解释SQL:这条SQL语句采用全连接，查询出左表和右表所有的值出来

查询结果：

介绍Oracle +连接方式及说明

+在左边 为右连接

SQL>select e.empno,e.ename,e.job,d.deptno,d.dname
from emp e,dept d where e.deptno(+)=d.deptno order by e.empno;

复制代码

查询结果：

+在右边 为左连接

SQL>select e.empno,e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno(+) order by e.empno;

查询结果：