当前位置:   article > 正文

SQL基础理论篇(六):多表的连接方式_sql表连接

sql表连接

简介

SQL92中提供了5类连接方式,分别是笛卡尔积、等值连接、非等值连接、外连接(左连接、右连接、全外连接(full outer join、全连接))和自连接。

SQL99标准中基本一致,分别是交叉连接、自然连接、外连接(左外连接、右外连接和全外连接)和自连接,并补充了on和using这两种操作符来指定我们需要的连接条件。

笛卡尔积

笛卡尔积是一个数学运算。假设有A和B两个集合,则这两个集合的笛卡尔积就是它俩的所有可能组合。

select * from A, B;
  • 1

假设A表有m行数据,B表有n行数据,那么最终的结果集里就会有m*n行,即所有的组合情况,这就是笛卡尔积。

笛卡尔积在SQL99中又称为交叉连接,即cross join。它的作用是可以把任意表做连接,即使这些表之间并不相关。

select * from A cross join B;
  • 1

但是事实上直接这样使用笛卡尔积是非常粗暴的,所以我们通常在使用连接的时候还是会加上筛选条件的,即where,这就诞生了下一步要说的等值连接

等值连接

等值连接的话,就需要两张表有关联了,即需要两表有相同或相似的字段,可以用来做连接时的筛选。

select * from A, B where a.team_id = b.team_id;
  • 1

可以看到,似乎好像就是在笛卡尔积的基础上加入了筛选条件。

等值连接在SQL99中被称为自然连接,即natural join;

那么问题来了,等值连接运行时,会先产生笛卡尔积,然后再用where条件来过滤吗?

是的,等值连接其实是需要先做笛卡尔积,然后再根据where条件,选择出满足条件的记录组合。

非等值连接

多表关联时,如果连接条件是等号,就是等值连接;其他的运算符就是非等值连接。

select * from A, B where A.height between B.height_lowest and B.height_highest;
  • 1

外连接

外连接的一大特点是,除了可以查询满足条件的记录以外,也可以搭配null值检查来查询不满足条件的记录。

两张表分主表和从表。

SQL92中采用(+)代表从表所在位置,而且92中只有左外连接和右外连接,没有全外连接。

左外连接,左边的表是主表,显示左边的表的全部行,若右表没有匹配,则右表位置为null。

在这里插入图片描述

在这里插入图片描述

92标准下:

select * from A, B where A.team_id = B.team_id(+)
  • 1

相当于SQL99中:

select * from A left join B on A.team_id=B.team_id;
  • 1

右外连接,右边的表是主表,显示右边的表的全部行,若左表没有匹配上,则左表位置为null;

在这里插入图片描述

在这里插入图片描述

92标准下:

select * from A, B where A.team_id(+) = B.team_id
  • 1

相当于SQL99中:

select * from A right join B on A.team_id=B.team_id;
  • 1

92标准中不存在left join和right join。

全外连接:结合了left join和right join的结果,其实就是两表关联取并集。在99标准中的语法是A full join B或者是A full outer join B

在这里插入图片描述

在这里插入图片描述

这里可以考虑一下两表取并集之后去掉交集,如何使用full join来写

MySQL中未提供full join写法,但oracle提供。

内连接:两表关联取交集

在这里插入图片描述

在这里插入图片描述

自连接

指自己连接自己。查询时用到了自己的字段

select b.player_name from A a, A b where a.player_name='赫韦德斯' and a.height < b.height
  • 1

查看比赫韦德斯高的球员。

这个用join也能实现:

select b.player_name from A a
join A b
on a.player_name='赫韦德斯' and a.height < b.height
  • 1
  • 2
  • 3

其他

sql99中还提供了一种using连接,在进行连接时,可以用using指定数据表中的同名字段进行等值连接,比如:

SELECT player_id, team_id, player_name, height, team_name 
FROM player 
JOIN team USING(team_id)
  • 1
  • 2
  • 3

相当于:

SELECT player_id, player.team_id, player_name, height, team_name 
FROM player 
JOIN team 
ON player.team_id = team.team_id
  • 1
  • 2
  • 3
  • 4

按我理解,简化了连接而已。

SQL92与SQL99中连接的区别

SQL92中会把所有需要连接的表都放在from后,而SQL99是采用join的方式连接一张表,可以多次使用join连接不同的表,这样看起来层次性更强,可读性更强。

不同DBMS下使用连接的注意事项

  1. 部分DBMS虽然支持SQL99标准,但是并不支持里面的全外连接,比如说MySQL、Access、SQLite、MariaDB等,但是Oracle、DB2、SQL Server 是支持的;
  2. Oracle不支持表别名as,如果要写别名的话,直接写成player p即可;
  3. SQLite只支持左连接,不支持右连接;

另外,鼓励使用自连接而不是子查询。因为在许多 DBMS 的处理过程中,对于自连接的处理速度要比子查询快得多。你可以这样理解:子查询实际上是通过未知表进行查询后的条件判断,而自连接是通过已知的自身数据表进行条件判断,因此在大部分 DBMS 中都对自连接处理进行了优化。

参考文献

  1. 10丨常用的SQL标准有哪些,在SQL92中是如何使用连接的?
  2. 11丨SQL99是如何使用连接的,与SQL92的区别是什么?
  3. sql中的几种join 及 full join问题 写的很好很全面
  4. 一图看懂join、left join、right join、fulljoin间的区别
  5. SQL FULL JOIN 关键字
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/煮酒与君饮/article/detail/853329
推荐阅读
相关标签
  

闽ICP备14008679号