[打卡]1 多表查询

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;
#查询员工id和部门名

SELECT * FROM employees;

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

#咱们之前写的都是内连接

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

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

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

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

#外连接涉及两个语法规范：一个是SQL92,一个是SQL99

#先来看SQL92

#SQL92内连接 见上

#SQL92外连接

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id(+);

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

#先来看SQL99

#SQL99的内连接

#查询员工id和部门名

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

#也可以在join前加个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;

#SQL99的外连接

#左外连接

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

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;

#右外连接
SELECT employee_id,department_name
FROM employees e RIGHT  join departments d
on e.department_id=d.department_id;

SELECT 122-106;