[打卡]瓜瓜 外连接00

SELECT employee_id,department_name
from employees e,departments d
where e.department_id=d.department_id;-- （106条数据）

-- 角度3：内连接 vs 外连接
-- 如果想要所有的都查出来，就叫做外连接
-- 如果我们要做外连接，涉及到两个语法规范，SQL92(形式简单可读性差)和SQL99(复杂，可读性强)
-- 外连接的分类：左外连接，右外连接，满外连接

-- 查询所有员工id和部门的名字 106+1=107
-- SQL92
-- SQL92内连接：见上
-- SQL92外连接：
-- 报错，MySQL不支持SQL92的外连接

SELECT employee_id,department_name
from employees e,departments d
where e.department_id=d.department_id(+);

-- SQL99的内连接：
-- 查询员工ID和部门名字
SELECT employee_id,department_name
from employees e JOIN departments d
ON e.department_id=d.department_id;

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

-- OUTER 可以省略

SELECT employee_id,department_name
FROM employees e left JOIN departments d
ON e.department_id = d.department_id;

-- 右外连接(OUTER 可以省略)

SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;