[打卡]赵梓焓 多表查询

-- 查询表结构
DESC employees;
DESC departments;
-- 注意写表别名和连接条件 ‘e’和‘d’,'where'
SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;
# 内连接与外连接
SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;

#SQL92内连接:见上

#SQL92外连接：使用+

#报错：mysql不支持SQL92外连接

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

#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;

#加上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外连接

#1左外连接

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

#2右外连接

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