刘世鑫 多表查询

SELECT employee_id,department_name
from employees e,departments d
WHERE e.department_id=d.department_id;

SELECT * from employees;
SELECT * from departments;
#角度三：内连接VS外连接
#SQL92外连接

SELECT employee_id,department_name
from employees e,departments d
WHERE e.department_id=d.department_id(+);
#以上SQL92外连接的语法在mysqul里是不能用的
#SQL99的分类有左外、右外、满外
#先看SQL99

#SQL99的内连接
#查询员工id和部门名
SELECT employee_id,department_name
from employees e join departments d
on e.department_id=d.department_id;
#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 OUTER JOIN departments d
on e.department_id=d.department_id;


SELECT employee_id,department_name
from employees e FULL OUTER JOIN departments d
on e.department_id=d.department_id;