mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

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

前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。比如:员工表和部门表,这两个表依靠“部门编号”进行关联。

一个错误

#案例:查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;

这是多表查询菜鸟经常犯的错误和误解的地方,如果employees有3条数据,departements有3条数据,最后查出来的是3*3的条数据

我们把上述多表查询中出现的问题称为:笛卡尔积的错误。

笛卡尔积的错误。

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

SQL92中,笛卡尔积也称为交叉连接,英文是 CROSS JOIN。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

阿里开发规范:

【强制】对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名(或表名)进行限定。

说明:对多表进行查询记录、更新记录、删除记录时,如果对操作列没有限定表的别名(或表名),并且操作列在多个表中存在时,就会抛异常。

正例:select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

反例:在某业务中,由于多表关联查询语句没有加表的别名(或表名)的限制,正常运行两年后,最近在 某个表中增加一个同名字段,在预发布环境做数据库变更后,线上查询语句出现出1052 异常:Column 'name' in field list is ambiguous。

自连接

当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接,外连接等查询。

查询出last_name为 ‘Chen’ 的员工的 manager 的信息。
employees员工表中只有manager的id

SELECT m.*
FROM employees e,employees m
WHERE e.manager_id=m.employee_id AND e.last_name="chen"

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

内连接(INNER JOIN)的实现

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

外连接(OUTER JOIN)的实现

左外连接(LEFT OUTER JOIN)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

右外连接(RIGHT OUTER JOIN)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

. UNION的使用

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

#中图:内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上图:左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图:右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图:A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
#右中图:B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
#左下图:满外连接# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作,效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右下图#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ (B - A∩B)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department

mysql多表查询(一口气解决掉:自连接 左右连接 满连接等)

在正式开始讲连接表的种类时,我们首先需要知道 SQL 存在不同版本的标准规范,因为不同规范下的表连接操作是有区别的。SQL 有两个主要的标准,分别是 SQL92 和 SQL99。92 和 99 代表了标准提出的时间,SQL92 就是 92 年提出的标准规范。

当然除了 SQL92 和 SQL99 以外,还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011 和 SQL:2016 等其他的标准。这么多标准,到底该学习哪个呢?

实际上最重要的 SQL 标准就是 SQL92 和 SQL99。一般来说 SQL92 的形式更简单,但是写的 SQL 语句会比较长,可读性较差。

而 SQL99 相比于 SQL92 来说,语法更加复杂,但可读性更强。我们从这两个标准发布的页数也能看出,SQL92 的标准有 500 页,而 SQL99 标准超过了1000 页。

实际上从 SQL99 之后,很少有人能掌握所有内容,因为确实太多了。就好比我们使用Windows、Linux 和 Office 的时候,很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能,满足日常工作的需求即可

。SQL92 和 SQL99 是经典的 SQL 标准,也分别叫做 SQL-2 和 SQL-3 标准。也正是在这两个标准发布之后,SQL 影响力越来越大,甚至超越了数据库领域。现如今 SQL 已经不仅仅是数据库领域的主流语言,还是信息