如何理解sql的七种join

总览

常见的join可分为以下7种

image-20200217150110294

现在建立两张数据表，接下来将会对每个例子逐个讲解

建表

两张表如下：

员工表：emp_tbl:

create table emp_tbl
(
    emp_id int auto_increment,
    emp_name varchar(40) null,
    dep_id int not null,
    constraint emp_tbl_pk
        primary key (emp_id)
);

image-20200217150429312

其中，5号员工peter对应的部门id是6，在部门表中是没有对应的部门的。

部门表：dep_tbl:

create table dep_id
(
    dep_id int auto_increment,
    dep_name varchar(40) null,
    dep_addr varchar(40) null,
    constraint dep_id_pk
        primary key (dep_id)
);

image-20200217150528290

Left Join

如图：

image-20200217150841949

左边的集合全都有，而右边的集合只有一部分与A重合的存在

现在执行sql语句：

SELECT * FROM emp_tbl left join dep_tbl dt on emp_tbl.dep_id = dt.dep_id;

得到的结果是：

image-20200217151111809

即为：左表的全部都在，而且通过dep_id把右表存在的对应内容全部接上了，如果没有的话就是null

Right Join

image-20200217151212249

执行sql语句：

SELECT * FROM emp_tbl right join dep_tbl dt on emp_tbl.dep_id = dt.dep_id;

结果：

image-20200217151503768

与上面相反：保证了右边的全都有，且把左边匹配的写过来了，没有的仍然用null代替

Inner Join

image-20200217151555452

如图：意思就是取交集，就是要两边都有的东西，所以也就是不能有null出现

执行sql:

SELECT * FROM emp_tbl inner join dep_tbl dt on emp_tbl.dep_id = dt.dep_id;

所以结果就是两边都有的：

image-20200217151712456

Left Join且不含B的内容

如图：

image-20200217151833630

也就是说，我们获得的结果就是A中与B没有交集的部分，所以就是，join B表会得到null的内容

执行sql:

SELECT * FROM emp_tbl left join dep_tbl dt on emp_tbl.dep_id = dt.dep_id where dt.dep_id is null;

结果如下：

image-20200217152042116

Right Join且不含A的内容

同理，就是与上面的情况相反：

如图：

image-20200217152133805

执行sql:

SELECT * FROM emp_tbl ep right join dep_tbl on ep.dep_id = dep_tbl.dep_id where ep.dep_id is null;

得到结果：

image-20200217152337591

Full Join

就那个意思，两边的所有内容都要有，连得起的就连，连不起的就补充null

image-20200217152423901

然而，这里有个小坑：mysql语法不支持full outer join

所以我们用union来实现，sql语句如下：

SELECT * FROM emp_tbl et left join dep_tbl dt on et.dep_id = dt.dep_id
union
SELECT * FROM emp_tbl et right join dep_tbl dt on et.dep_id = dt.dep_id where et.dep_id is null;

其实这整个红色部分就等于：A和B的左连接 加上 A和B的右连接去公共部分

结果如下：

image-20200217152951657

全部都有

Full Join且不含交集

image-20200217153032674

还是用union来实现full outer join

SELECT * FROM emp_tbl et left join dep_tbl dt on et.dep_id = dt.dep_id where dt.dep_id is null
union
SELECT * FROM emp_tbl et right join dep_tbl dt on et.dep_id = dt.dep_id where et.dep_id is null;

结果如下：

image-20200217153141514