1、新建两张表
1.1 建表
DROP table if EXISTS A;
CREATE TABLE A(
id INT DEFAULT null ,
`key` VARCHAR(20) DEFAULT NULL
);
DROP table if EXISTS B;
CREATE TABLE B(
`key` VARCHAR(20) DEFAULT null ,
address VARCHAR(20) DEFAULT NULL
);
INSERT INTO A(id,`key`) VALUES
(1,'企业一'),
(2,'企业二'),
(3,'企业三'),
(4,'企业四'),
(5,'企业五'),
(6,'企业六'),
(7,null);
INSERT INTO B(`key`,address) VALUES
('企业一','北京'),
('企业二','上海'),
('企业三','深圳'),
('企业四','杭州'),
('企业七','新疆'),
('企业八','云南'),
(null,null);
表A 表B
注意:两个表中连接字段key都有故意为null的记录。
1.2 表的连接方式
从两个虚拟表可以看出,连接字段为两表的key字段。(注意:key字段中都有null,null与null之间不能连接。ps:个人理解)
1.2.1 用集合的图形表示法,分别表示两表中字段`key`中元素的集合。
注意:1、圆圈代表的是字段key中元素的集合,不是表数据的集合
2、不论是哪种连接方式,都是通过key字段中相同元素进行连接的
2. 内连接:inner join
2.1 连接后的终表中key字段(包括key,key1)中元素的集合
连接后,最终的表,其左边的部分中A.key(key)保留下的是两表key字段的交集,不包括null,其右边的部分中B.key(key(1))也保留下的是两表key字段的交集,不包括null。
终表中key(包括key,key1)字段包含元素的集合如下图所示(红色部分):
2.2 数据结果
筛选出两个表的key字段交集元素所对应的各表的数据。
SELECT *
FROM A
JOIN B
ON A.key=B.key
3. 左连接 :left join
3.1 连接后的终表中key字段(包括key,key1)中元素的集合
连接后,最终的表,主表部分保留了A.key字段的全部元素,从表部分的B.key中与之相同的元素会保留,不同的则不保留。
终表中key(包括key,key1)字段包含元素的集合如下图所示(红色部分):
3.2 数据结果
保留主表A中全部的数据,通过key字段进行连接,B表中与主表A对应key字段的元素相同的,产生连接,并将表B该条的记录连接在右边;对应的元素不相同的,不产生连接,用null补全
SELECT *
FROM A
LEFT JOIN B ON A.key=B.key
4. 右连接 :right join
4.1 连接后的终表中key字段(包括key,key1)中元素的集合
连接后,最终的表,保留主表部分的B.key字段的全部元素,从表部分的A.key中与之相同的元素会保留,不同的则不保留。
终表中key(key,key1)字段包含元素的集合如下图所示(红色部分):
4.2 数据结果
保留主表B中全部的数据,通过key字段进行连接,A表中与主表B对应key字段的元素相同的,产生连接,并将表A该条的记录连接在左边;与主表B对应key字段的元素不相同的,不产生连接,用null填充
SELECT *
FROM A
RIGHT JOIN B ON A.key=B.key
5. 条件左连接 :left join + where
5.1 连接并筛选后的终表中key字段(包括key,key1)中元素的集合
两表通过key字段连接筛选后,最终的表中,只保留主表部分A.key字段独有的元素,如下图所示(红色部分):
5.2 条件左连接数据结果
先通过左连接筛选出数据(保留主表A中全部的数据,通过key字段进行连接,B表中与主表A对应key字段的元素相同的,产生连接,并将表B该条的记录连接在后面;与主表A对应key字段的元素不相同的,不产生连接,用null补全。)接着通过where 筛选,A.key独有的(null也算是其独有)的
SELECT *
FROM A
LEFT JOIN B ON A.key=B.key
where B.key is null
如果想筛选A.key非空的数据。需增加where筛选条件如下:
SELECT *
FROM A
LEFT JOIN B ON A.key=B.key
where B.key is null and A.key is not null
6. 条件右连接 :right join + where
与第五小节类似
(1)连接筛选后最终的表中,key字段只保留主表部分B.key字段独有元素,图形表示如下(红色部分):
(2)条件右连接数据结果
SELECT *
FROM A
RIGHT JOIN B ON A.key=B.key
where A.key is null
7. 全外连接 :full outer join
7.1 连接后终表中key字段(包括key,key1)中元素的集合
通过key字段进行连接,最终的表保留两个表中key字段所有的元素的,如下图所示(红色部分):
7.2 数据结果
MYSQL不支持全外连接,所以通过union 将右连接和左连接的结果合并去重。最终两表的全部记录都保留,key元素相同的发生连接,不同的相互用null补全。
SELECT *
FROM A
LEFT JOIN B ON A.key=B.key
UNION
SELECT *
FROM A
RIGHT JOIN B ON A.key=B.key
8. 条件全外连接 :full outer join + where
8.1 连接并筛选后终表中key字段(包括key,key1)中元素的集合
连接后,最终的表中,key的元素由两个表中key字段的全部元素减去共有元素组成,如下图所示(红色部分):
8.2 数据结果
MYSQL不支持全外连接,所以通过union 将条件右连接和条件左连接的结果合并去重。最终的数据保留两表中key独有的元素(包括null)所对应的每条记录。
SELECT *
FROM A
LEFT JOIN B ON A.key=B.key
WHERE B.key is null
UNION
SELECT *
FROM A
RIGHT JOIN B ON A.key=B.key
WHERE A.key is null