MYSQL-内外连接

目录

一、先搞懂：什么是表连接？

二、用得最多的：内连接（Inner Join）

核心逻辑：只留 “两边都有” 的数据

语法格式

案例：查 “SMITH” 的姓名 + 部门名

三、“不能丢数据” 的外连接

1. 左外连接（Left Join）：保左表全数据

语法

案例：查所有学生的成绩（没成绩也要显示学生）

2. 右外连接（Right Join）：保右表全数据

语法

案例：查所有成绩（没对应学生也要显示）

练习：查 “部门 + 员工”（含无员工的部门）

四、总结：内连 vs 外连怎么选？

---

作为 SQL 新手，表连接绝对是绕不开的核心技能 —— 毕竟实际业务里很少只用一张表查数据。今天就用大白话聊聊内连接、左外连接、右外连接这仨 “好兄弟”，附代码案例，看完就能上手！

一、先搞懂：什么是表连接？

简单说，表连接就是把多张表 “拼” 在一起查数据。比如员工表（EMP）存了员工姓名，部门表（DEPT）存了部门名称，要查 “员工 + 所属部门”，就得把这俩表连起来。

二、用得最多的：内连接（Inner Join）

核心逻辑：只留 “两边都有” 的数据

内连接会筛选出两张表中匹配连接条件的记录，相当于取 “交集”。（其实咱们之前用where写的多表查询，本质就是内连接！）

语法格式

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

案例：查 “SMITH” 的姓名 + 部门名

建表+数据插入：

CREATE TABLE dept (
    deptno INT NOT NULL COMMENT '部门编号',
    dname VARCHAR(50) COMMENT '部门名称',
    loc VARCHAR(50) COMMENT '部门所在城市',
    PRIMARY KEY (deptno)  -- 主键：部门编号唯一标识部门
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入部门数据，对应表中展示的部门信息
INSERT INTO dept (deptno, dname, loc)
VALUES
(10, 'ACCOUNTING', 'NEW YORK'),  -- 会计部，纽约
(20, 'RESEARCH', 'DALLAS'),     -- 研发部，达拉斯
(30, 'SALES', 'CHICAGO'),       -- 销售部，芝加哥
(40, 'OPERATIONS', 'BOSTON');   -- 运营部，波士顿

CREATE TABLE emp (
    empno INT NOT NULL COMMENT '员工编号',
    ename VARCHAR(50) COMMENT '员工姓名',
    job VARCHAR(50) COMMENT '职位',
    mgr INT COMMENT '上级领导的员工编号（关联emp.empno）',
    hiredate DATETIME COMMENT '入职日期',
    sal DECIMAL(10,2) COMMENT '月薪',
    comm DECIMAL(10,2) COMMENT '奖金（提成）',
    deptno INT COMMENT '所属部门编号（关联dept.deptno）',
    PRIMARY KEY (empno),  -- 主键：员工编号唯一标识员工
    FOREIGN KEY (deptno) REFERENCES dept(deptno),  -- 外键：关联部门表
    FOREIGN KEY (mgr) REFERENCES emp(empno)  -- 外键：关联自身（上级领导）
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 第一步：插入无上级的员工（管理层/独立员工，避免mgr外键校验失败）
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10),  -- 董事长，无上级
(7902, 'FORD', 'ANALYST', NULL, '1981-12-03 00:00:00', 3000.00, NULL, 20);     -- 分析师，先插入（后续作为SCOTT的上级）

-- 第二步：插入有上级的员工（上级已存在）
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30),   -- 上级KING（7839）
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10),   -- 上级KING（7839）
(7788, 'SCOTT', 'ANALYST', 7902, '1987-04-19 00:00:00', 3000.00, NULL, 20),   -- 上级FORD（7902）
(7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20),   -- 上级KING（7839）
(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30), -- 上级BLAKE（7698）
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30),  -- 上级BLAKE（7698）
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30),-- 上级BLAKE（7698）
(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30),  -- 上级BLAKE（7698）
(7876, 'ADAMS', 'CLERK', 7788, '1987-05-23 00:00:00', 1100.00, NULL, 20),      -- 上级SCOTT（7788）
(7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, NULL, 30),        -- 上级BLAKE（7698）
(7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.00, NULL, 10),      -- 上级CLARK（7782）
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, NULL, 20);        -- 上级FORD（7902）

比如员工表 EMP 和部门表 DEPT，用deptno（部门编号）连接：

-- 老式where写法（本质是内连）
select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and ename='SMITH';

-- 标准内连接写法
select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and ename='SMITH';

三、“不能丢数据” 的外连接

内连接会丢 “单边无匹配” 的数据，而外连接能保留某一张表的全部记录，另一张表无匹配则显示 NULL。

1. 左外连接（Left Join）：保左表全数据

核心：左边表的记录全部显示，右边表无匹配则补 NULL。

语法

select 字段 from 左表 left join 右表 on 连接条件;

案例：查所有学生的成绩（没成绩也要显示学生）

比如学生表 stu、成绩表 exam：

-- 先建表插数据
create table stu (id int, name varchar(30));
insert into stu values(1,'jack'),(2,'tom'),(3,'kitty'),(4,'nono');

create table exam (id int, grade int);
insert into exam values(1,56),(2,76),(11,8);

-- 查所有学生的成绩（没成绩的学生也显示）
select * from stu left join exam on stu.id=exam.id;

结果里，nono（id=4）没成绩，exam 的字段会显示 NULL。

2. 右外连接（Right Join）：保右表全数据

核心：右边表的记录全部显示，左边表无匹配则补 NULL。

语法

select 字段 from 左表 right join 右表 on 连接条件;

案例：查所有成绩（没对应学生也要显示）

还是用 stu 和 exam 表，要保留 exam 的所有成绩（比如 id=11 的成绩）：

select * from stu right join exam on stu.id=exam.id;

结果里，exam 中 id=11 的成绩会显示，stu 的字段补 NULL。

练习：查 “部门 + 员工”（含无员工的部门）

可以用左连（部门表放左边）:

-- 方法1：左连（dept是左表，保留所有部门）
select d.dname, e.* from dept d left join emp e on d.deptno=e.deptno;

也可以用右连（部门表放右边）：

-- 方法2：右连（dept是右表，保留所有部门）
select d.dname, e.* from emp e right join dept d on d.deptno=e.deptno;

四、总结：内连 vs 外连怎么选？

连接类型	核心特点	适用场景
内连接	只取两表匹配的数据	查 “双方都存在” 的关联信息
左外连接	保留左表全部数据	查 “主表 + 关联表”（主表必显示）
右外连接	保留右表全部数据	查 “关联表 + 主表”（关联表必显示）