MySQL的多表查询（尚硅谷宋老师（康师傅）笔记）

一、笛卡尔积的错误

笛卡尔积的错误的理解：每个行都匹配了一遍。在X,Y集合中分别取两个数据，X的所有数据都会依次和Y中的每一个记录匹配一遍。若X又m个数据，Y有n个数据，则匹配后一共有n*m种结果。

SELECT employee_id,department_name
FROM `employees`,departments;#一共有2889个数据（107*27）

SELECT department_name
FROM departments;    #27个

SELECT employee_id
FROM `employees`;    #107个

2.错误的原因：缺少多表连接条件

3.正确方式：需要连接条件

SELECT employee_id,department_name
FROM `employees`,departments
WHERE employees.`department_id`=departments.department_id;

注意连接条件！（此处连接条件的内容是一致的）

4.从优化的角度：多表查询时，每个字段前指明其所在表

5.可以给表取别名

SELECT emp.employee_id,dep.department_name
FROM `employees` emp,departments dep
WHERE emp.`department_id`=dep.department_id;

注：一旦起了别名，南无在SELECT FROM里必须只使用别名，而不能在使用表的原名。

6.若有n个表，则至少需要n-1个连接条件

练习

SELECT employee_id,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

7.多表查询分类

角度一：

等值连接   VS   非等值连接

1.以上例子都是等值连接。

2.非等值链接

角度二：

自连接   VS   非自连接

1.自连接：练习：员工id，员工姓名及其管理者的id 和姓名

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id`=mgr.`employee_id`;

2.第一部分例子都是非自连接。

角度三：内连接   VS   外连接

1.内连接：合并同一列的两个以上的表的行，结果集中不包含一个表与另一个表的不匹配的行

2.外连接：合并同一列的两个以上的表的行，结果除了集中不包含一个表与另一个表的不匹配的行之外，还查询到了左表（左外连接）或右表（右外连接）不匹配/ 不满足条件的行

        2.1分类：

        左外连接，右外连接，满外连接（左右全都有）

练习：查询所有员工的姓，部门编号

SELECT emp.employee_id,emp.last_name
FROM employees emp ，departments d
WHERE emp.department_id=d.department_id;

SQL92语法实现内连接，见上

SQL92语法实现外连接（右外连接）：使用 +（MySQL不支持此处的+）

SELECT emp.employee_id,emp.last_name
FROM employees emp ，departments d
WHERE emp.department_id=d.department_id（+）;

SQL99语法使用JOIN。。。ON的方式实现多表查询（外连接）

例子： 

SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`
JOIN locations l
ON d.`location_id`=l.`location_id`;

SQL99语法实现外连接（左外连接例子）OUTER可省略（左外：LEFT，右外：RIGHT，满外：FULL）但MySQL不支持满外连接Oracle支持

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

&&& ps：UNION的使用

语法格式：

SELECT

1.UNION ：会执行去重操作（效率低)

2.UNION ALL：不会去重（效率高，建议用）

3.七种join的实现

 左上图：（左外连接

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

右上图：（右外连接

SELECT last_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;

中间图：（内连接

SELECT last_name
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`;

左中图：

SELECT last_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.department_id IS NULL;

右中图：

SELECT last_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.department_id IS NULL;

左下图：满外连接

左上图 UNION ALL 右中图

SELECT last_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
UNION ALL
SELECT last_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.department_id IS NULL;

右下图

SELECT last_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.department_id IS NULL
UNION ALL
SELECT last_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.department_id IS NULL;

三、自然连接

1.它会自动帮你查询两张连接表中所有的相同字段

NATURAL JOIN 

不建议使用,它会比较所有的相同字段，限制条件更多，不灵活

四、

USING 查询条件时两个表的字段名字一样时可以用USING()代替，比如：using（department_id）

五、小节

表的约束条件：WHERE 、ON、 USING（）