[打卡]mcg--多表查询（SQL92、99内连接、外连接）

# SQL有两个语法规范 SQL92 SQL99

#SQL92的内连接
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

SELECT * FROM employees;

# SQL92的外连接
#在MySQL中不能用
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);

# SQL99的内连接
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;

#添加一个查询信息
SELECT employee_id,department_name,city
FROM employees e JOIN departments d
on e.department_id = d.department_id
JOIN locations l
ON l.location_id=d.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;