【MYSQL】七种 SQL JOINS 的实现

1. 内连接 (INNER JOIN) 中图

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

• 说明：返回两个表中匹配的记录

2. 左外连接 (LEFT JOIN) 左上图

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

• 说明：返回 employees 表中的所有记录，以及 departments 表中匹配的记录。如果 departments 表中没有匹配的记录，则返回 NULL。

3. 右外连接 (RIGHT JOIN) 右上图

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

• 说明：返回 departments 表中的所有记录，以及 employees 表中匹配的记录。如果 employees 表中没有匹配的记录，则返回 NULL。

4. 左外连接 - 无匹配记录 (LEFT JOIN with WHERE) 左中图

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

• 说明：返回 employees 表中没有匹配 departments 表的记录。

5. 右外连接 - 无匹配记录 (RIGHT JOIN with WHERE) 右中图

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

• 说明：返回 departments 表中没有匹配 employees 表的记录。

6. 全外连接 (FULL OUTER JOIN) 左下图

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION ALL
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

• 说明：返回两个表中所有的记录，包括不匹配的记录。由于 SQL 标准不支持 FULL OUTER JOIN，这里使用 UNION ALL 来模拟。

7. 全外连接 - 无匹配记录 (FULL OUTER JOIN with WHERE) 右下图

SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

• 说明：返回两个表中没有匹配的记录。同样使用 UNION ALL 来模拟 FULL OUTER JOIN 的效果。