MySql的各种语句（最详细）-4（多表查询）

#多表查询

#出现了笛卡尔积的错误
SELECT employee_id,last_name,department_name
FROM employees,departments; #2889

SELECT 2889 / 107
FROM DUAL; # 27

SELECT department_id
FROM departments; #27

#正确的：
SELECT employee_id,last_name,department_name
FROM employees,departments
#多表的连接条件
WHERE employees.`department_id` = departments.`department_id`;

#如果查询的字段在多个表中出现，则必须显式的指明此字段所属的表
#sql优化
SELECT employees.employee_id,employees.last_name,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#表的别名,可以在select、where中使用。
SELECT e.employee_id,e.last_name,d.department_name,e.department_id
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;

#三个表的连接练习：
SELECT employee_id,last_name,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个连接条件。

#################################
/*
多表查询的总结：分类

等值连接 vs  非等值连接
自连接   vs  非自连接
内连接   vs  外连接

*/
#非等值连接
SELECT employee_id,last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

#自连接
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`;

#内连接:合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
SELECT e.employee_id,e.last_name,d.department_name,e.department_id
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`; #106行

#左外连接：除了查询到两个表满足连接条件的行以外，还返回左表中不满足条件的行
#右外连接：除了查询到两个表满足连接条件的行以外，还返回右表中不满足条件的行

#字眼：查询所有的。。。。
#sql 99语法中提到了如何使用左外连接、右外连接
#sql 99语法实现内连接：
SELECT e.employee_id,e.last_name,d.department_name,e.department_id
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

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

#sql 99语法实现左外连接：
SELECT e.employee_id,e.last_name,d.department_name,e.department_id
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

#sql 99语法实现右外连接：
SELECT e.employee_id,e.last_name,d.department_name,d.department_id
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 满外连接:mysql不支持full join 
SELECT e.employee_id,e.last_name,d.department_name,d.department_id
FROM employees e FULL JOIN departments d
ON e.`department_id` = d.`department_id`;