白铂溪 MySQL多表查询3

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

SELECT * from employees;
#角度3：内连接 vs 外连接

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

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

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


#SQL92外连接
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id(+);
#以上SQL92外连接的语法在mysql里是不能用的
#SQL99的分类有左外。右外
#先来看SQL99
#SQL99的内连接
#查询员工id和部门名
SELECT employee_id,department_name
FROM employees e INNER JOIN departments d #INNER可省略
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;


#左外链接

#查询所有员工id和部门名107
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 JOIN departments d
ON e.department_id = d.department_id;

SELECT 122-106;