MySQL 表连接（内连接与外连接）

用户11445909

发布于 2025-03-28 11:46:14

4900

代码可运行

文章被收录于专栏：猫咪-9527猫咪-9527

运行总次数：0

代码可运行

1、表连接的核心概念

1.1 为什么需要表连接？

问题：两个表的数据需要关联查询（如学生表和成绩表）
笛卡尔积：未指定条件时，两个表的所有记录两两组合（效率低下，结果混乱）
解决方案：通过连接条件筛选有效组合

2、内连接（INNER JOIN）

2.1 核心概念

定义：仅返回两表中满足连接条件的记录

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

语法：

select 字段 from 表1 inner join 表2 on 表1.id = 表2.id [and 其他筛选条件];

等价写法：WHERE子句隐式内连接

SELECT 字段 FROM 表1, 表2 WHERE 表1.id = 表2.id [and 其他筛选条件];

2.2 案例：

查询员工及其部门名称

两者写法不同但是效果一致

方法一：

---隐式内连接写法
select *from emp,dept where emp.deptno=dept.deptno;

方法二：

---标准内连接写法
select *from dept inner join emp on emp.deptno=dept.deptno;

3、外连接（OUTER JOIN）

外连接分为左外连接和右外连接

3.1 左外连接（LEFT JOIN）

核心概念

保留规则：左表（第一个表）的所有记录，右表无匹配时用NULL填充

语法：

select 字段 from 表1 left join 表2 on 链接条件 [and 其他筛选条件];

案例：查询所有雇员及工资

-- 创建表
CREATE TABLE employees (emp_id INT, emp_name VARCHAR(30));
INSERT INTO employees VALUES (1, 'Alice'), 
(2, 'Bob'), (3, 'Charlie'), (4, 'David');

CREATE TABLE salaries (emp_id INT, salary INT);
INSERT INTO salaries VALUES (1, 5000), (2, 6000), (5, 7000);

-- 左外连接查询
select *from employees s1 left join salaries s2 on s1.emp_id=s2.emp_id;

结果：

3.2 右外连接（RIGHT JOIN）

核心概念

保留规则：右表（第二个表）的所有记录，左表无匹配时用NULL填充

语法：

select 字段 from 左表 right join 右表 on 链接条件 [and 其他筛选条件];

案例：查询所有工资，即使无对应雇员

select *from employees s1 right join salaries s2 
on s1.emp_id=s2.emp_id;

结果：

3.3 左外与右外的等价性

-- 左外连接（保留左表）
SELECT * FROM A LEFT JOIN B ON A.id = B.id;

-- 等价于右外连接（保留右表）
SELECT * FROM B RIGHT JOIN A ON A.id = B.id;

建议：优先使用左外连接，逻辑更直观。

4、注意事项

4.1 ON与WHERE子句的区别

ON：用于指定连接条件，影响匹配逻辑。
on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

WHERE：用于过滤已连接后的结果，会过滤掉外连接中的NULL值。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉，on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。