Mysql：常见的7种join

常见的七种join

现在假设有A表即employees表，B表departments表

join1

上图使用左外连接即可做到，sql：

SELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id

join2

上图使用右外连接即可做到，sql：

SELECT *
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id

join3

上图使用内连接即可做到，sql：

SELECT *
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id

join4

sql如下，记得是B表的部门ID为空，而不是A表。

SELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id is null

join5

sql如下，记得是A表的部门ID为空，而不是B表。

SELECT *
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id is null

join6

其实这个就是满外连接，但很可惜mysql并不支持，需要我们自己手动去拼接。可以采用join1 + join5 来实现；sql如下：

SELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION ALL
SELECT *
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id is null

join7

join4 + join5拼接即可：

SELECT *
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id is null
UNION ALL
SELECT *
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id is null