MySQL数据库，多表查询

多表查询，也称为关联查询，指两个或多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一，一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

若有以下两张数据库的表：

表emp：

表dept：

比如：以deptno为关联字段，进行多表查询，查询emp中的员工姓名、薪资和dept中的所在地。

SELECT ename , sal  , loc

FROM emp , dept

WHERE emp.deptno = dept.deptno;

 

WHERE语句作为两个表的连接条件，即员工的emp表中的记录只会和dept表中和emp的deptno记录相同的进行匹配。如果没有WHERE语句做连接条件，emp中的记录会和的deptno的每一条记录都匹配，就会出现不应该的重复记录，比如以上代码若没有WHERE语句，会产生60条（15（emp中的记录数） * 4（deptno中的记录数））记录，此错误被称为笛卡尔积的错误。

笛卡尔积错误的出现原因：

• 省略多个表的连接条件（或关联条件）

• 连接条件无效

• 所有表中的所有行相互连接（即上文说的相互匹配）

注：

• 如果查询语句中出现了多个表中相同名的字段，为防止冲突，需指明此字段所在的表（表名.字段）。

• 从SQL优化的角度，建议多表查询时，每个字段前指明其所在的表。

• 由于有时表名过长，指明字段的所在表时，会使可读性变差，可以使用给表起别名的方式解决。但是，给表起别名后，使用表名时，就不能使用表的原名，只能使用表的别名。

• 如果有n个表要多表查询，则至少要n - 1个连接条件，防止出现笛卡尔积的错误。

多表查询的分类：

角度一：等值连接、非等值连接

等值连接：用表之间的字段值相等作为连接条件，比如上文的多表查询：

SELECT ename , sal  , loc

FROM emp , dept

WHERE emp.deptno = dept.deptno;

非等值连接：并不是以用表之间的字段值相等作为连接条件，可能是表的字段以某个区间作为连接条件

比如：要查询员工的薪资等级，以下为薪资等级的表：

表job_grades：

SELECT e.ename , e.sal , j.grade_level

FROM emp e , job_grades j

WHERE e.sal > j.low_sal AND e.sal <= j.hig_sal

员工的emp表的薪资与job_grades表的薪资等级的low_sal到hig_sal的区间做匹配，能保证一一对应，不会出现不应该的重复。

角度二：自连接、非自连接

自连接：一个表用自己的字段和自己的另一个字段的值相等作为连接条件，即自己的字段和自己的另外一个字段做匹配。

比如，查询emp表中员工和员工的管理者的id和姓名：

SELECT e1.ename , e1.sal , e1.mgr , e2.ename

FROM emp e1 , emp e2

WHERE e1.mgr = e2.empno;

自连接的实质上还是多表查询，只不过是多个同样的表。

非自连接即未使用自连接。

角度三：内连接、外连接

内连接：合并具有具有同一列的俩个以上的表的行，结果集中不包含结果集中不包含一个表与另一个表中不匹配的行。

外连接：两个表在连接过程中除了返回满足连接条件的行之外还返回左/右表中不满足条件（即不匹配）的行（未匹配到的字段值的结果返回的是NULL），这种连接称为左/右外连接。如果左表和右表都有不匹配的行，并都返回其不匹配的行（未匹配到的字段值的结果返回的是NULL），这种连接称为满外连接。

如果是左外连接，则连接条件中左边的表称为主表，右边的表称为从表。

如果是右外连接，则连接条件中右边的表称为主表，左边的表称为从表。

SQL92：使用(+)实现外连接。在SQL92中，采用(+)代表从表所在的位置，即表名后标明了(+)的代表此表是从表。但是，MySQL中不支持此SQL92中的语法，在一些其他的DBMS中比如oracle中支持。

JOIN ON

SQL99语法中使用JOIN ……ON的方式实现多表查询的问题。

SQL99中JOIN ……ON的方式处理内连接：

SELECT e1.ename , e1.sal , e1.mgr , e2.ename

FROM emp e1 JOIN emp e2

ON e1.mgr = e2.empno;

JOIN 表

ON 连接条件

JION……

ON……

……

在FROM语句中，用JOIN表示表与表之间的连接（内连接使用的其实是INNER JOIN，写为JOIN即默认为INNER JOIN），连接条件写在ON后，如果有很多个表连接，采用多个JOIN …… ON …… 的方式。

SQL99中处理外连接：

查询所有的员工的姓名、薪资、管理者、管理者姓名（左外连接）

SELECT e1.ename , e1.sal , e1.mgr , e2.ename

FROM emp e1 LEFT OUTER JOIN emp e2

ON e1.mgr = e2.empno;

在JOIN前加上 LEFT OUTER 即表明使用左外连接，OUTER可以省略，表示为LEFT JOIN。

右外连接即将LEFT替换为RIGHT。

满外连接：

即FULL OUTER JOIN，但是MySQL中不支持此SQL99中的语法，在一些其他的DBMS中比如oracle中支持。

UNION的使用：

合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

UNION操作符：UNION操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符：返回两个查询的结果集中的并集。对于两个结果集的重复部分，不去除重复部分。

注：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

SQL99中七种JOIN的操作：

由于MySQL不支持FULL OUTER JOIN的方式，所以左下图与右下图要使用UNION的方式

满外连接（左下图）

方式①：左上图UNION ALL右中图：

SELECT (字段列表)

FROM 表A

LEFT JOIN 表B

ON A.key = b.key

UNION ALL

SELECT (字段列表)

FROM 表A

LEFT JOIN 表B

ON A.key = b.key

WHERE A.key = NULL;

方式②：左中图 UNION ALL 右上图：

SELECT (字段列表)

FROM 表A

LEFT JOIN 表B

ON A.key = b.key

WHERE B.key = NULL

UNION ALL

SELECT (字段列表)

FROM 表A

RIGHT  JOIN 表B

ON A.key = b.key；

右下图：

左中图 UNION ALL 右中图

SELECT (字段列表)

FROM 表A

LEFT JOIN 表B

ON A.key = b.key

WHERE B.key = NULL

UNION ALL

SELECT (字段列表)

FROM 表A

LEFT JOIN 表B

ON A.key = b.key

WHERE A.key = NULL

自然连接：

自然连接是SQL99的新特性，用NATURAL JOIN表示自然连接，使用自然连接之后，可以自动查询两张连接表中所有的相同的字段，然后进行等值连接。

如：若emp表中和dep表中都有字段dep_id 和 mgr_id，要输出员工的相应的部门和管理者的信息（即emp表要和dep表的mgr_id字段和dep_id字段都匹配。）

SELECT e.emp_id , e.emp_name , e.dep_id , d.dep_name

FROM emp e JOIN dep d

ON e.dep_id = d.dep_id AND e.mgr_id = d.mgr_id; 

可以使用自然连接，写作：

SELECT e.emp_id , e.emp_name , e.dep_id , d.dep_name

FROM emp e NATURAL JOIN dep d;

USING连接：

例：

SELECT ename , sal  , loc

FROM emp JOIN dept

ON emp.deptno = dept.deptno;

等同于

SELECT ename , sal  , loc

FROM emp , dept

USING (deptno);

USING指定了具体的相同的字段名称，需要在USING后面的括号中填入要做相同匹配的字段。要求两个关联字段名称一致，而且只能做等值匹配。