[mysql] 6.24 懒得写的多表查询

这里要说明一个概念：笛卡尔积错误

出现场景：

SELECT * FROM table1 CROSS JOIN table2;
SELECT * FROM table1 JOIN table2;
SELECT * FROM table1 , table2;

两个表之间 没有任何连接关系 / 连接关系无效 时，进行的交叉连接。

笛卡尔积是a,b 两集合 ：<x,y>(x∈a,y∈b) 。

这种交叉连接的数据量非常大而且往往没有现实应用意义。

两个表在连接时要确定好连接关系。

————————————————————————————

不想写什么。这里纯模板的东西，随便一搜便是了，不再赘述。

所以举一些例子算了。

首先是JOIN ON 语句的 应用场景。

分为以下几个情况：

#中图：内连接 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 ）

#右下图
 #左中图 + 右中图 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_id` 
WHERE e.`department_id` IS NULL

                                                                                                    ————以上摘自尚硅谷，侵删。

————————————————————————————————

UNION ALL 和 UNION

UNION ALL 返回两个查询结果集合的 并集，且保留重复数据。

UNION 不保留，是去重筛选，也就是DISTINCT版

#执行 UNION ALL 语句时所需要的资源比 UNION 语句少。

#同样场景， 尽量使用 UNION ALL 语句，以提高效率。

————————————————————————————————

配合比较重要的JOIN ON 语句，他的应用场景有时可以用WHERE直接筛选 来替代，有时只能用它自己。那么下面我们来区分一下这两种场景，即“可以”用它 和 “只能”用它的时候。 （你知道我在说什么对吧）

1. 显示 所有 员工的姓名，部门号和部门名称

2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id

看下这两个问题。他们解决过程中的关键因素 是 “所有” 这个条件。

问题1中的“所有”意味着我要考虑 “部分条件满足，其余条件空缺” 的 数据。

问题2则没有这种要求，我只要90号部门满足所有条件的数据。

所以看解法：

问题1：（只能借助join on 来收集多余的数据）

SELECT last_name, e.department_id, department_name 
FROM employees e 
LEFT OUTER JOIN departments d 
ON e.`department_id` = d.`department_id`;

问题2：（两种方法皆可）

SELECT job_id, location_id 
FROM employees e, departments d 
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;

或

SELECT job_id, location_id 
FROM employees e JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE e.`department_id` = 90;

为了深刻理解，我们看下问题1 强行使用 WHERE语句 和 作为正解的JOIN ON 的区别在哪里：

JOIN ON 形式结果：

WHERE 形式结果：

 

 看到没！

 结果表示的106和107，即我们刚才谈到的那个“部分条件满足，其余条件空缺”，就是两者之   间的最大区别，即对数据的“所有”的要求

————————————————————————————————————————

这里 的 WHERE NOT EXIST用法比较有趣。