多表查询

SELECT employee_id,department_name
# 多表查询的表 （没有顺序限制）
FROM  employees,departments 
# 两表连接的条件 不加条件会造成/连接条件无 -> 笛卡尔积错误
WHERE employees.department_id = departments.department_id;

# 如果查询中出现了多个表中都存在的字段，则一定要指明该字段所存在的表(一个表中特有的字段可以不添加)
# 从SQL优化的角度，建议每一个字段都指明所在的表
SELECT last_name,first_name,employees.department_id,department_name
FROM  employees,departments
WHERE employees.department_id = departments.department_id;

# 给表起别名，在SELECT WHERE 中使用  注意：一旦起了别名，在WHERE SELECT中必须使用别名，不能使用原名
SELECT emp.last_name,emp.first_name,emp.department_id,dpt.department_name
FROM  employees emp ,departments dpt
WHERE emp.department_id = dpt.department_id;

#level up
SELECT e.last_name,e.department_id,d.department_name,city
FROM  employees e ,departments d,locations l
WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id;
#当有n个表的时候 至少 要有n-1个条件 否则会出现笛卡尔积错误

# 多表查询的分类
# point 1.等值连接与非等值连接
#非等值连接
SELECT e.last_name,e.salary,j.grade_level
FROM  employees e ,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

SELECT e.last_name,e.salary,j.grade_level
FROM  employees e ,job_grades j
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;

# point 2.自连接与非自连接
# 自连接  涉及自己表内部的关系作为连接条件
SELECT e1.employee_id,e1.last_name,e2.employee_id manageId,e2.last_name manageNmae
FROM  employees e1 ,employees e2
WHERE e1.manager_id = e2.employee_id;

# point 3.内连接与外连接
# 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表不匹配的行。
# 外连接的分类：左外连接、右外连接、满外连接
# 左外连接
#SQL92 内连接 同上
#SQL92 外连接 MySQL中不支持  + (用于数据少的表上)
SELECT last_name,first_name,employees.department_id,department_name
FROM  employees,departments
WHERE employees.department_id = departments.department_id(+);
#SQL99 JOIN ON 实现多表查询
#SQL99 内连接 INNER JOIN
SELECT e1.employee_id,e1.last_name,e2.employee_id manageId,e2.last_name manageNmae
FROM  employees e1 JOIN employees e2
ON e1.manager_id = e2.employee_id;
# level up
SELECT e.last_name,d.department_name,l.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 外连接
# 左外：让左边的表的未匹配数据一并返回
SELECT e.last_name,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
# 右外：让右边的表的未匹配数据一并返回
SELECT e.last_name,d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;

# 满外连接 全连接
SELECT e.last_name,d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
# MySQL不支持FULL OUTER 这里使用UNION来解决
#UNION ALL 不去重，效率高
SELECT e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
UNION ALL
SELECT e.last_name,d.department_name
FROM employees e  LEFT JOIN departments d
ON e.department_id = d.department_id;


#UNION 去重，效率低
SELECT e.last_name,d.department_name
FROM employees e  RIGHT JOIN departments d
ON e.department_id = d.department_id
UNION
SELECT e.last_name,d.department_name
FROM employees e  LEFT JOIN departments d
ON e.department_id = d.department_id;


#UNION 
#左排除连接
SELECT e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
#右排除连接
SELECT e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
#外部排除连接
SELECT e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
UNION ALL
SELECT e.last_name,d.department_name
FROM employees e  LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

#补充：UNION 
/*
UNION :合并查询结果 并去重 
UNION ALL:合并查询结果 不去重
利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
优化建议：尽量用UNION ALL 优化SQL占用资源更少 
*/

# SQL99新特性 补充
-- NATURAL JOIN自然连接：它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。(强制等值连接所有相同字段)
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
-- 等价于
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

-- USEING  相当于是等值条件的简单写法 两个表中的相同字段进行等值连接的时候就可以使用
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
-- 等价于
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
/*
注意：
我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。
*/

笛卡尔积错误

SQL JIONS的7种方式