【MySQL基础】左右连接实战：掌握数据关联的完整视图

时间：2025-04-10 14:47:58

1 左右连接基础概念

左连接(left join)和右连接(right join)是MySQL中两种重要的表连接方式，它们与内连接不同，能够保留不匹配的记录，为我们提供更完整的数据视图。
核心区别：
  • left join：保留左表所有记录，右表无匹配则显示null
  • right join：保留右表所有记录，左表无匹配则显示null
  • inner join：：只返回匹配成功的记录（不保留任何表的全部记录）

2 创建测试数据

-- 创建部门表
drop table if exists departments;
create table departments (
    dept_id int primary key,
    dept_name varchar(50) not null
) default charset=utf8mb4;
commit;

-- 创建员工表
drop table if exists employees;
create table employees (
    emp_id int primary key,
    emp_name varchar(50) not null,
    dept_id int,
    salary decimal(10,2),
    constraint fk_dept foreign key (dept_id) references departments(dept_id)
) default charset=utf8mb4;
commit;

-- 插入测试数据
insert into departments values 
(10, '研发部'),
(20, '市场部'),
(30, '财务部'),
(40, '人事部');
insert into employees values 
(101, '张三', 10, 12000),
(102, '李四', 20, 9000),
(103, '王五', 10, 15000),
(104, '赵六', null, 8000),
(105, '钱七', 50, 10000);  
commit;

3 左连接(left join)实战

3.1 基本左连接查询

-- 查询所有员工及其部门信息（包括没有部门的员工）
select e.emp_name, ifnull(d.dept_name, '无部门') as dept_name, e.salary
from employees e
left join departments d on e.dept_id = d.dept_id;
  • 查询结果
mysql> select e.emp_name, ifnull(d.dept_name, '无部门') as dept_name, e.salary
    -> from employees e
    -> left join departments d on e.dept_id = d.dept_id;
+----------+-----------+----------+
| emp_name | dept_name | salary   |
+----------+-----------+----------+
| 张三     | 研发部    | 12000.00 |
| 王五     | 研发部    | 15000.00 |
| 李四     | 市场部    |  9000.00 |
| 赵六     | 无部门    |  8000.00 |
| 钱七     | 无部门    | 10000.00 |
+----------+-----------+----------+
5 rows in set (0.04 sec)

mysql>

3.2 查找没有部门的员工

-- 使用left join的特殊用法
select e.emp_id, e.emp_name
from employees e
left join departments d on e.dept_id = d.dept_id
where d.dept_id is null;
  • 查询结果
mysql> select e.emp_id, e.emp_name
    -> from employees e
    -> left join departments d on e.dept_id = d.dept_id
    -> where d.dept_id is null;
+--------+----------+
| emp_id | emp_name |
+--------+----------+
|    104 | 赵六     |
|    105 | 钱七     |
+--------+----------+
2 rows in set (0.00 sec)

mysql>

4 右连接(right join)实战

4.1 基本右连接查询

-- 查询所有部门及其员工信息（包括没有员工的部门）
select d.dept_name, ifnull(e.emp_name, '无员工') as emp_name
from employees e
right join departments d on e.dept_id = d.dept_id;
  • 查询结果
mysql> select d.dept_name, ifnull(e.emp_name, '无员工') as emp_name
    -> from employees e
    -> right join departments d on e.dept_id = d.dept_id;
+-----------+-----------+
| dept_name | emp_name  |
+-----------+-----------+
| 研发部    | 张三      |
| 研发部    | 王五      |
| 市场部    | 李四      |
| 财务部    | 无员工    |
| 人事部    | 无员工    |
+-----------+-----------+
5 rows in set (0.00 sec)

mysql>

4.2 查找没有员工的部门

-- 使用right join的特殊用法
select d.dept_id, d.dept_name
from employees e
right join departments d on e.dept_id = d.dept_id
where e.emp_id is null;
  • 查询结果
mysql> select d.dept_id, d.dept_name
    -> from employees e
    -> right join departments d on e.dept_id = d.dept_id
    -> where e.emp_id is null;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|      30 | 财务部    |
|      40 | 人事部    |
+---------+-----------+
2 rows in set (0.00 sec)

mysql>

5 左右连接对比演示

-- 左连接结果
select '左连接' as join_type, e.emp_name, d.dept_name
from employees e
left join departments d on e.dept_id = d.dept_id;

-- 右连接结果
select '右连接' as join_type, e.emp_name, d.dept_name
from employees e
right join departments d on e.dept_id = d.dept_id;

-- 内连接结果（对比用）
select '内连接' as join_type, e.emp_name, d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id;
  • 查询结果
mysql> -- 左连接结果
mysql> select '左连接' as join_type, e.emp_name, d.dept_name
    -> from employees e
    -> left join departments d on e.dept_id = d.dept_id;
+-----------+----------+-----------+
| join_type | emp_name | dept_name |
+-----------+----------+-----------+
| 左连接    | 张三     | 研发部    |
| 左连接    | 王五     | 研发部    |
| 左连接    | 李四     | 市场部    |
| 左连接    | 赵六     | NULL      |
| 左连接    | 钱七     | NULL      |
+-----------+----------+-----------+
5 rows in set (0.00 sec)

mysql> 
mysql> -- 右连接结果
mysql> select '右连接' as join_type, e.emp_name, d.dept_name
    -> from employees e
    -> right join departments d on e.dept_id = d.dept_id;
+-----------+----------+-----------+
| join_type | emp_name | dept_name |
+-----------+----------+-----------+
| 右连接    | 张三     | 研发部    |
| 右连接    | 王五     | 研发部    |
| 右连接    | 李四     | 市场部    |
| 右连接    | NULL     | 财务部    |
| 右连接    | NULL     | 人事部    |
+-----------+----------+-----------+
5 rows in set (0.00 sec)

mysql> 
mysql> -- 内连接结果（对比用）
mysql> select '内连接' as join_type, e.emp_name, d.dept_name
    -> from employees e
    -> inner join departments d on e.dept_id = d.dept_id;
+-----------+----------+-----------+
| join_type | emp_name | dept_name |
+-----------+----------+-----------+
| 内连接    | 张三     | 研发部    |
| 内连接    | 王五     | 研发部    |
| 内连接    | 李四     | 市场部    |
+-----------+----------+-----------+
3 rows in set (0.01 sec)

mysql>

7 性能优化建议

7.1 索引优化

-- 为连接字段创建索引
alter table employees add index idx_dept (dept_id);
alter table departments add index idx_dept (dept_id);

7.2 查询改写技巧

-- 某些情况下，left join和right join可以相互转换
select e.emp_name, d.dept_name
from departments d
left join employees e on d.dept_id = e.dept_id;

-- 等价于
select e.emp_name, d.dept_name
from employees e
right join departments d on e.dept_id = d.dept_id;

7.3 避免过度使用

  • 当只需要匹配记录时，优先使用inner join
  • 大数据表连接时，注意结果集大小

8 总结

记住关键原则：left join保留左表全部记录，right join保留右表全部记录。根据业务需求选择合适的连接方式，可以大大提高查询的灵活性和数据分析的完整性。

相关文章