蓝易云

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

647次阅读
没有评论

共计 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    |
+-------------+---------+--------------+----------------+

提醒:本文发布于164天前,文中所关联的信息可能已发生改变,请知悉!

AD:【腾讯云服务器大降价】2核4G 222元/3年 1核2G 38元/年
正文完
 0
阿蛮君
版权声明:本站原创文章,由 阿蛮君 于2024-07-01发表,共计1870字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)
Copyright © 2022-2024 阿蛮君博客 湘ICP备2023001393号
本网站由 亿信互联 提供云计算服务 | 蓝易云CDN 提供安全防护和加速服务
Powered by Wordpress  Theme by Puock