SQL最强大的功能之一就是能在数据检索查询的执行中连接(JOIN
)表。连接是利用SQL的SELECT
能执行的最重要的操作,很好地理解连接及其语法是学习SQL的一个极为重要的组成部分。
SQL查询的基本原理
- 单表查询:
根据WHERE
条件过滤表中的记录,然后根据SELECT
指定的列返回查询结果。 - 两表连接查询:
使用ON
条件对两表进行连接形成一张虚拟结果集;然后根据WHERE
条件过滤结果集中的记录,再根据SELECT
指定的列返回查询结果。 - 多表连接查询:
先对第一个和第二个表按照两表连接查询,然后用用连接后的虚拟结果集和第三个表做连接查询,以此类推,直到所有的表都连接上为止,最终形成一张虚拟结果集,然后根据WHERE
条件过滤虚拟结果集中的记录,再根据SELECT
指定的列返回查询结果。
多表连接的结果通过三个属性决定
- 方向性:在外连接中写在前边的表为左表、写在后边的表为右表。
- 主附关系:主表要出所有的数据范围,附表与主表无匹配项时标记为
NULL
,内连接时无主附表之分。 - 对应关系:关键字段中有重复值的表为多表,没有重复值的表为一表。
表对应关系
一对一关系
在一对一关系中,A 表中的一行最多只能匹配于 B 表中的一行,反之亦然。如果相关列都是主键或都具有唯一约束,则可以创建一对一关系。
这种关系并不常见,因为一般来说,按照这种方式相关的信息都在一个表中。可以利用一对一关系来:
- 分割具有多列的表。
- 由于安全原因而隔离表的一部分。
- 保存临时的数据,并且可以毫不费力地通过删除该表而删除这些数据。
- 保存只适用于主表的子集的信息。
一对多关系
一对多关系是最普通的一种关系。在这种关系中,A 表中的一行可以匹配 B 表中的多行,但是 B 表中的一行只能匹配 A 表中的一行。例如,部门表和 人员表之间具有一对多关系:每个部门有很多员工,但是每个员工只属于一个部门。
只有当一个相关列是一个主键或具有唯一约束时,才能创建一对多关系。
多对多关系
在多对多关系中,A 表中的一行可以匹配 B 表中的多行,反之亦然。要创建这种关系,需要定义第三个表,称为结合表,它的主键由 A 表和 B 表的外部键组成。
表连接方式
不同的数据分析工具 | 支持的表连接方式 |
---|---|
Oracle/ sql server/ Tableau/ Python | 内连接(inner join)、左连接(left join)、右连接(right join)、全连接(full join) |
MySQL | 内连接(inner join)、左连接(left join)、右连接(right join) |
Power BI | 内连接、左连接、右连接、全连接、左反连接、右反连接 |
示例表格
员工表 emp
empno | ename | job | mgr | hiretime | sal | <
---|