两表连接各种Join图示，SQL及查询结果

按：SQL术语Join在中文对应的翻译是“连接”还是“联结”说法不一，下文将统一采用“连接”的译法。

开局一张图：

前奏/准备工作：

Emp表结构：

create table emp(
    empid number(4,0),
    empname nvarchar2(20),
    deptno number(4,0)
)

Emp充值语句：

insert into emp(empid,empname,deptno) values('1','Andy','1');
insert into emp(empid,empname,deptno) values('2','Bill','1');
insert into emp(empid,empname,deptno) values('3','Cindy','2');
insert into emp(empid,empname,deptno) values('4','Douglas','2');
insert into emp(empid,empname,deptno) values('5','张三','4');
insert into emp(empid,empname,deptno) values('6','李四','6');
insert into emp(empid,empname,deptno) values('7','王五','7');

Dept表结构：

create table dept(
    deptid number(4,0),
    deptname nvarchar2(20)
)

Dept充值语句：

insert into dept(deptid,deptname) values('1','研发');
insert into dept(deptid,deptname) values('2','销售');
insert into dept(deptid,deptname) values('3','市场');
insert into dept(deptid,deptname) values('4','管理');
insert into dept(deptid,deptname) values('5','公关');
insert into dept(deptid,deptname) values('8','咨询');

正文：

内连接 ：

SQL语句：

select emp.*,dept.* from emp inner join dept on emp.deptno=dept.deptid

查询结果：

SQL> select emp.*,dept.* from emp inner join dept on emp.deptno=dept.deptid;

     EMPID EMPNAME                                      DEPTNO     DEPTID DEPTNAME
---------- ---------------------------------------- ---------- ---------- ----------------------------------------
         2 Bill                                              1          1 研发
         4 Douglas                                           2          2 销售
         3 Cindy                                             2          2 销售
         5 张三                                               4          4 管理

已用时间:  00: 00: 00.01

左连接：

SQL语句：

select emp.*,dept.* from emp left join dept on emp.deptno=dept.deptid

查询结果：

SQL> select emp.*,dept.* from emp left join dept on emp.deptno=dept.deptid;

     EMPID EMPNAME                                      DEPTNO     DEPTID DEPTNAME
---------- ---------------------------------------- ---------- ---------- ----------------------------------------
         2 Bill                                              1          1 研发
         4 Douglas                                           2          2 销售
         3 Cindy                                             2          2 销售
         5 张三                                               4          4 管理
         6 李四                                               6
         7 王五                                               7

已选择6行。

已用时间:  00: 00: 00.00

右连接：

SQL：

select emp.*,dept.* from emp right join dept on emp.deptno=dept.deptid

查询结果：

SQL> select emp.*,dept.* from emp right join dept on emp.deptno=dept.deptid;

     EMPID EMPNAME                                      DEPTNO     DEPTID DEPTNAME
---------- ---------------------------------------- ---------- ---------- ----------------------------------------
         2 Bill                                              1          1 研发
         3 Cindy                                             2          2 销售
         4 Douglas                                           2          2 销售
         5 张三                                               4          4 管理
                                                                        5 公关
                                                                        8 咨询
                                                                        3 市场

已选择7行。

已用时间:  00: 00: 00.01

左连接去除内连

SQL：

select emp.*,dept.* from emp left join dept on emp.deptno=dept.deptid where dept.deptid IS NULL

查询结果：

SQL> select emp.*,dept.* from emp left join dept on emp.deptno=dept.deptid where dept.deptid IS NULL;

     EMPID EMPNAME                                      DEPTNO     DEPTID DEPTNAME
---------- ---------------------------------------- ---------- ---------- ----------------------------------------
         6 李四                                              6
         7 王五                                              7

已用时间:  00: 00: 00.00

右连接去除内连

SQL：

select emp.*,dept.* from emp right join dept on emp.deptno=dept.deptid where emp.deptno IS NULL

查询结果：

SQL> select emp.*,dept.* from emp right join dept on emp.deptno=dept.deptid where emp.deptno IS NULL;

     EMPID EMPNAME                                      DEPTNO     DEPTID DEPTNAME
---------- ---------------------------------------- ---------- ---------- ----------------------------------------
                                                                        5 公关
                                                                        8 咨询
                                                                        3 市场

已用时间:  00: 00: 00.01

全连接

SQL：

select emp.*,dept.* from emp full join dept on emp.deptno=dept.deptid 

查询结果：

SQL> select emp.*,dept.* from emp full join dept on emp.deptno=dept.deptid ;

     EMPID EMPNAME                                      DEPTNO     DEPTID DEPTNAME
---------- ---------------------------------------- ---------- ---------- ----------------------------------------
         2 Bill                                              1          1 研发
         4 Douglas                                           2          2 销售
         3 Cindy                                             2          2 销售
                                                                        3 市场
         5 张三                                              4          4 管理
                                                                        5 公关
                                                                        8 咨询
         6 李四                                              6
         7 王五                                              7

已选择9行。

已用时间:  00: 00: 00.01

 全连接去除内连接，这种查询适合比较两个结果集的差异，具体请见https://www.cnblogs.com/xiandedanteng/p/12239597.html

SQL：

select emp.*,dept.* from emp full join dept on emp.deptno=dept.deptid where emp.deptno IS NULL or dept.deptid IS NULL

查询结果：

SQL> select emp.*,dept.* from emp full join dept on emp.deptno=dept.deptid where emp.deptno IS NULL or dept.deptid IS NULL;

     EMPID EMPNAME                                      DEPTNO     DEPTID DEPTNAME
---------- ---------------------------------------- ---------- ---------- ----------------------------------------
                                                                        3 市场
                                                                        5 公关
                                                                        8 咨询
         6 李四                                              6
         7 王五                                              7

已用时间:  00: 00: 00.00

全外连接去除内连接

SQL：

select emp.*,dept.* from emp full outer join dept on emp.deptno=dept.deptid where emp.deptno IS NULL or dept.deptid IS NULL

查询结果：

SQL> select emp.*,dept.* from emp full outer join dept on emp.deptno=dept.deptid where emp.deptno IS NULL or dept.deptid IS NULL;

     EMPID EMPNAME                                      DEPTNO     DEPTID DEPTNAME
---------- ---------------------------------------- ---------- ---------- ----------------------------------------
                                                                        3 市场
                                                                        5 公关
                                                                        8 咨询
         6 李四                                              6
         7 王五                                              7

已用时间:  00: 00: 00.00

IN半连接：

SQL：

select emp.* from emp where emp.deptno in (select deptid from dept)

查询结果：

SQL> select emp.* from emp where emp.deptno in (select deptid from dept);

     EMPID EMPNAME                                      DEPTNO
---------- ---------------------------------------- ----------
         2 Bill                                              1
         4 Douglas                                           2
         3 Cindy                                             2
         5 张三                                               4

已用时间:  00: 00: 00.00

EXISTS半连接：

SQL：

select emp.* from emp where exists  (select NULL from dept where dept.deptid=emp.deptno)

查询结果：

SQL> select emp.* from emp where exists  (select NULL from dept where dept.deptid=emp.deptno);

     EMPID EMPNAME                                      DEPTNO
---------- ---------------------------------------- ----------
         2 Bill                                              1
         4 Douglas                                           2
         3 Cindy                                             2
         5 张三                                               4

已用时间:  00: 00: 00.01

IN反连接：

SQL：

select emp.* from emp where emp.deptno not in (select deptid from dept)

查询结果：

SQL> select emp.* from emp where emp.deptno not in (select deptid from dept);

     EMPID EMPNAME                                      DEPTNO
---------- ---------------------------------------- ----------
         6 李四                                              6
         7 王五                                              7

已用时间:  00: 00: 00.00

EXISTS反连接：

SQL：

select emp.* from emp where not exists  (select NULL from dept where dept.deptid=emp.deptno)

查询结果：

SQL> select emp.* from emp where not exists  (select NULL from dept where dept.deptid=emp.deptno);

     EMPID EMPNAME                                      DEPTNO
---------- ---------------------------------------- ----------
         6 李四                                              6
         7 王五                                              7

已用时间:  00: 00: 00.00

参考网页：https://docs.oracle.com/cd/E11882_01/server.112/e41084/queries006.htm#SQLRF30046

实验环境：

#	类别	版本
1	操作系统	Win10
2	数据库	Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
3	硬件环境	T440p
4	内存	8G

--2020年2月6日--