Mysql 左连接、右连接与全连接的区别

共计 1870 个字符，预计需要花费 5 分钟才能阅读完成。

创建表并插入数据

1. 创建 employees 表

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

2. 创建 departments 表

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

3. 插入示例数据

INSERT INTO employees (employee_id, name, department_id) VALUES
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', NULL),
(4, 'David', 3);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Finance'),
(4, 'Engineering');

测试

1. LEFT JOIN

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

结果：

+-------------+---------+--------------+----------------+
| employee_id | name    | department_id| department_name|
+-------------+---------+--------------+----------------+
| 1           | Alice   | 1            | HR             |
| 2           | Bob     | 2            | Finance        |
| 3           | Charlie | NULL         | NULL           |
| 4           | David   | 3            | NULL           |
+-------------+---------+--------------+----------------+

2. RIGHT JOIN

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

结果：

+-------------+---------+--------------+----------------+
| employee_id | name    | department_id| department_name|
+-------------+---------+--------------+----------------+
| 1           | Alice   | 1            | HR             |
| 2           | Bob     | 2            | Finance        |
| NULL        | NULL    | NULL         | Engineering    |
+-------------+---------+--------------+----------------+

3. FULL JOIN（通过 UNION 实现）

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

结果：

+-------------+---------+--------------+----------------+
| employee_id | name    | department_id| department_name|
+-------------+---------+--------------+----------------+
| 1           | Alice   | 1            | HR             |
| 2           | Bob     | 2            | Finance        |
| 3           | Charlie | NULL         | NULL           |
| 4           | David   | 3            | NULL           |
| NULL        | NULL    | NULL         | Engineering    |
+-------------+---------+--------------+----------------+