MySQL之表连接方式(内连接与外连接)

目录

• 1、表连接的核心概念
• 1.1 为什么需要表连接？
• 2、内连接（INNER JOIN）
• 2.1 核心概念
• 2.2 案例
• 3、外连接（OUTER JOIN）
• 3.1 左外连接（LEFT JOIN）
• 3.2 右外连接（RIGHT JOIN）
• 3.3 左外与右外的等价性
• 4、注意事项
• 4.1 ON与WHERE子句的区别
• 4.2 NULL值的处理
• 总结

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 employeespython 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：用于指定连接条件，影响www.devze.com匹配逻辑。
• on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

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

4.2 NULL值的处理

ifnull()函数

外连接结果中的NULL表示无匹配记录，需在业务逻辑中处理（如显示“无成绩”）。

select s1.emp_id,emp_name,ifnull(salary,'无成绩')salary 
from employees s1 left join salaries s2 on s1.emp_id=s2.emp_id;

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。