[大数据]mysql多表查询

                    **mysql多表查询**

#多表的查询如何实现
SELECT employee_id,department_name
FROM employees,departments
#查询出来了2889条记录

SELECT * FROM employees; #查询出107条记录

SELECT 2889/107
FROM DUAL; #结果是27

SELECT * FROM departments; #可以查询到有27个部门

#多表查询的正确方式：要有多表的连接条件
SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_idAND d.location_id=l.location_id;

#非等值连接的例子
SELECT *
FROM job_grades;

#查询员工姓名以及其管理者的姓名
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;

#练习：查询所有员工的last_name,department_name信息
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;
#SQL92语法实现内连接如上
#sql92实现外连接在mysql上不能跑，可以使用oracle来实现语句如下
#SQL92语法实现左外连接:右面要使用+ 如下
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id（+）;

#SQL92语法实现右外连接:左面要使用+ 如下
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id（+）=departments.department_id;

#sql99语法实现内连接
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id;

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;

SELECT last_name,department_name
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 last_name,department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id=d.department_id;

#右外连接
SELECT last_name,department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;

#各种连接
#内连接
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;

#只有左边:要用右边表的字段is null
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;

#只有右边：要用左边表的字段is null
SELECT employee_id,last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_idIS NULL;

#满外连接:左外连union all上只有右边
SELECT employee_id,last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
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_idIS NULL;

#满外连接:右外连 union all上只有左边
SELECT employee_id,last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id=d.department_id
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_idIS NULL;

#sqL 99新特性：自然连接（natural join） ，一般不用,其必须满足相同字段等值的内容才显示
SELECT last_name,department_name
FROM employees e
NATURAL JOIN departments d

#sqL 99新特性：using join
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id;