共计 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 |
+-------------+---------+--------------+----------------+
AD:【腾讯云服务器大降价】2核4G 222元/3年 1核2G 38元/年
正文完