[打卡]MySQL的内外链接

查询员工ID和部门ID

-- SQL92 内连接

SELECT e.employee_id,d.department_id

FROM employees e, departments d

WHERE e.department_id=d.department_id;

SELECT * FROM employees;

-- 角度3：内连接VS外连接

-- 之前写的都是内连接

-- 可能有员工每部门，也可能由部门没员工

-- 如果想把除了内连接

-- 外连接设计两个语法：一是SQL92，SQL99

-- SQL92外连接

SELECT e.employee_id,d.department_id

FROM employees e, departments d

WHERE e.department_id=d.department_id(+);

-- 以上SQL92外连接的语法在MySQL内不能使用

-- 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和部门名

SELECT employee_id,department_name

FROM employees e LEFT OUTER JOIN departments d

ON e.department_id=d.department_id;

-- 右外连接

-- 查询所有员工ID和部门名

SELECT employee_id,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id=d.department_id;

SELECT 122-106;