[打卡]MySQL 多表查询

#SQL92内连接
SELECT e.employee_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id;

SELECT * FROM employees;

# 角度3:内连接 vs 外连接

#可能有员工没有部门，也可能有部门但没有员工

#如果想把除了内连接的显示出来之外，还想把其他内容显示出来即外连接

#外连接分类：左外链接、右外连接、满外链接

#查询所有员工id和部门名

#外连接涉及两个语法规范：1.SQL92 2.SQL99

#SQL92外连接
SELECT e.employee_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id(+);

#以上SQL92外连接的语法在MySQL里是不能用的

#SQL99的内连接

#查询员工id和部门名

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

#也可以在jion前加一个INNER，可省略

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

#查询员工id、部门名和city

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;

#SQL的外连接

# 左外连接

#查询所有员工id和部门名

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

#右外连接

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