SQL99的7种连接查询总结---对其中图形表示法的理解

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字段中相同元素进行连接的

图1:连接字段的元素集合图

 

2. 内连接:inner join 

2.1  连接后的终表中key字段(包括key,key1)中元素的集合

连接后,最终的表,其左边的部分中A.key(key)保留下的是两表key字段的交集,不包括null,其右边的部分中B.key(key(1))也保留下的是两表key字段的交集,不包括null。

终表中key(包括key,key1)字段包含元素的集合如下图所示(红色部分):

图2:连接一

 

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:连接二

 

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:连接三

 

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:连接四

 

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字段独有元素,图形表示如下(红色部分):

图6:连接五

 (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:连接六

 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:连接七

 

 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

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值