一文搞定MySQL多表查询中的表连接(join)

SQL最强大的功能之一就是能在数据检索查询的执行中连接(JOIN)表。连接是利用SQL的SELECT能执行的最重要的操作，很好地理解连接及其语法是学习SQL的一个极为重要的组成部分。

SQL查询的基本原理

• 单表查询： 根据WHERE条件过滤表中的记录，然后根据SELECT指定的列返回查询结果。
• 两表连接查询： 使用ON条件对两表进行连接形成一张虚拟结果集；然后根据WHERE条件过滤结果集中的记录，再根据SELECT指定的列返回查询结果。
• 多表连接查询： 先对第一个和第二个表按照两表连接查询，然后用用连接后的虚拟结果集和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一张虚拟结果集，然后根据WHERE条件过滤虚拟结果集中的记录，再根据SELECT指定的列返回查询结果。

多表连接的结果通过三个属性决定

• 方向性：在外连接中写在前边的表为左表、写在后边的表为右表。
• 主附关系：主表要出所有的数据范围，附表与主表无匹配项时标记为NULL，内连接时无主附表之分。
• 对应关系：关键字段中有重复值的表为多表，没有重复值的表为一表。

表对应关系

一对一关系

在一对一关系中，A 表中的一行最多只能匹配于 B 表中的一行，反之亦然。如果相关列都是主键或都具有唯一约束，则可以创建一对一关系。

这种关系并不常见，因为一般来说，按照这种方式相关的信息都在一个表中。可以利用一对一关系来：

• 分割具有多列的表。
• 由于安全原因而隔离表的一部分。
• 保存临时的数据，并且可以毫不费力地通过删除该表而删除这些数据。
• 保存只适用于主表的子集的信息。

一对多关系

一对多关系是最普通的一种关系。在这种关系中，A 表中的一行可以匹配 B 表中的多行，但是 B 表中的一行只能匹配 A 表中的一行。例如，部门表和 人员表之间具有一对多关系：每个部门有很多员工，但是每个员工只属于一个部门。

只有当一个相关列是一个主键或具有唯一约束时，才能创建一对多关系。

多对多关系

在多对多关系中，A 表中的一行可以匹配 B 表中的多行，反之亦然。要创建这种关系，需要定义第三个表，称为结合表，它的主键由 A 表和 B 表的外部键组成。

表连接方式

示例表格

员工表 emp

部门表 dept

工资等级表 salgrade

内连接 (inner join)

按照连接条件，返回两张表中满足条件的记录。

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分以下几种：

• 等值连接： 在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
• 不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
• 自然连接： 在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重列。
• 自连接： 自连接通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。
• 笛卡尔积连接： 两张表中的每一条记录进行笛卡尔积组合，然后根据WHERE条件过滤虚拟结果集中的记录。

SELECT <字段名1>[,<字段名2>...,<字段名n>] 
FROM <表名1> 
[INNER] JOIN <表名2> 
ON <连接条件>；

1、等值连接

查询每位员工的 ename, job, hiretime, sal, dname

SELECT ename,job,hiretime,sal,dname
FROM emp 
INNER JOIN dept
ON emp.deptno=dept.deptno;

结果

2、不等连接

查询在工资等级范围内的每位员工的 ename, job, hiretime, sal

SELECT ename,job,hiretime,sal
FROM emp 
INNER JOIN salgrade
ON sal BETWEEN losal AND hisal;

结果

3、自然连接

无论何时对表进行连接，应该至少有一个列出现在不止一个表中（被连接的列）。标准的连接（前一章中介绍的内部连接）返回所有数据，甚至相同的列多次出现。自然连接排除多次出现，使每个列只返回一次。

以下例子将会自动排除相同的列'deptno'

SELECT emp.*, dept.dname
FROM emp
JOIN dept
WHERE ename = 'FORD';

结果

4、自连接

通过设置表别名，将一张表虚拟成多张表。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

使用表别名的主要原因之一是能在单条 SELECT 语句中不止一次引用相同的表。

当两张表格中没有相同的列时，则为笛卡尔积连接。

查询所有管理者姓名及其下属员工姓名

SELECT 员工表.ename AS 员工姓名, 领导表.ename AS 领导姓名
FROM emp AS 员工表
INNER JOIN emp AS 领导表
ON 员工表.mgr=领导表.empno;

表限定符.：如果表1和表2中的字段名相同，则必须使用表限定符.指定引用的是哪个表中的字段。

结果

5、笛卡尔积连接

笛卡儿积（cartesianproduct） 由没有联结条件的表关系返回的结果为笛卡儿积。 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT <字段名1>[,<字段名2>...,<字段名n>] 
FROM <表名1>,<表名2> 
WHERE <筛选条件>;

查询所有管理者姓名及其下属员工姓名

SELECT 领导表.ename AS 领导姓名, 员工表.ename AS 员工姓名
FROM emp AS 领导表, emp AS 员工表 
WHERE 领导表.empno = 员工表.mgr;

结果

左连接

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

左连接是按照连接条件，返回两张表中满足条件的记录，以及左表中的所有记录，右表匹配不到显示为NULL。

SELECT <字段名1>[,<字段名2>...,<字段名n>] 
FROM <表名1> 
LEFT JOIN <表名2> 
ON <连接条件>；

查询每位员工的 ename,dname,sal

SELECT ename,dname,sal
FROM emp 
LEFT JOIN dept
ON emp.deptno=dept.deptno;

结果

右连接

按照连接条件，返回两张表中满足条件的记录，以及右表中的所有记录，左表匹配不到显示为NULL

SELECT <字段名1>[,<字段名2>...,<字段名n>] 
FROM <表名1> 
RIGHT JOIN <表名2> 
ON <连接条件>；

查询每个部门的员工数（没有员工的部门，员工数统计为0）

SELECT dept.deptno,dname,COUNT(empno) AS empnum
FROM emp
RIGHT JOIN dept
ON dept.deptno=emp.deptno
GROUP BY dept.deptno;

结果

WHERE 子句的重要性在一条SELECT 语句中联结几个表时，相应的关系是在运行中构造的。在联结两个表时，实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE 子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

一表作为主表可以保证维度的完整性，多表作为主表可以保证度量的准确性。在没有明确表示需要保证维度完整性的情况下，优先保证度量的准确性，所以将度量值所在的表作为主表。度量字段通常存在于多表中，因此通常情况下可以将多表作为主表进行外连接。