目标：搞懂笛卡尔积、内连接、左连接、右连接、全连接、自连接、子查询关联，分清每种连接适用场景，避开新手高频坑。 环境：通用SQL语法（MySQL为主，会标注Oracle/SQLServer差异）

前置准备：先建测试表（直接复制执行）

三张业务常用表，贯穿下面所有案例

-- 用户表 user
CREATE TABLE `user` (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL COMMENT '用户名',
  age INT COMMENT '年龄'
);

-- 订单表 orders（外键user_id关联用户）
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单号',
  user_id INT COMMENT '下单用户id',
  order_name VARCHAR(100) COMMENT '商品名称',
  money DECIMAL(10,2) COMMENT '订单金额'
);

-- 商品表 goods
CREATE TABLE goods (
  goods_id INT PRIMARY KEY AUTO_INCREMENT,
  goods_name VARCHAR(100) COMMENT '商品名',
  price DECIMAL(10,2)
);

-- 测试数据
INSERT INTO `user`(username,age) VALUES
('张三',22),('李四',25),('王五',30);

INSERT INTO orders(user_id,order_name,money) VALUES
(1,'手机',3999),
(1,'耳机',299),
(2,'键盘',199),
(4,'手表',1299); -- user_id=4 用户不存在

INSERT INTO goods(goods_name,price) VALUES
('手机',3999),
('耳机',299),
('鼠标',89);

数据说明：

1. 用户：张三(1)、李四(2)、王五(3)

2. 订单：张三2条订单，李四1条，还有一条无效订单user_id=4

3. 王五【没有任何订单】

一、最基础概念：笛卡尔积（所有连接的底层原理）

1. 什么是笛卡尔积

两张表不加条件直接FROM A,B，表A每一行 × 表B每一行，产生大量无效数据。

-- 笛卡尔积，禁止生产环境直接使用！
SELECT * FROM `user`, orders;

user共3行，orders共4行 → 结果 3*4=12条记录，绝大多数是乱匹配。

✅ 结论：多表查询必须加关联条件！关联条件语法两种，效果等价：

-- 老式逗号写法（不推荐，可读性差）
SELECT * FROM `user`,orders WHERE user.id = orders.user_id;

-- JOIN标准写法【推荐，行业规范】
SELECT * FROM `user`
JOIN orders ON `user`.id = orders.user_id;

二、五大连接类型（核心重点）

1. INNER JOIN 内连接 ✅【最常用】

定义：

只返回两张表能够匹配上的数据，两边同时存在才显示。

应用场景：

查询「有订单的用户」、订单+对应的用户信息；只想要匹配成功的数据。

-- 内连接：查询有订单的用户+订单信息
SELECT u.id,u.username,o.order_id,o.order_name,o.money
FROM `user` u
INNER JOIN orders o 
ON u.id = o.user_id;

查询结果：

张三、李四的数据出现； 王五（无订单）、user_id=4的订单（无对应用户）全部消失。

简写：JOIN 默认就是 INNER JOIN，可以省略 INNER

2. LEFT JOIN 左连接（左外连接）⭐【日常开发高频】

定义：

保留左表所有数据，右表匹配不到的地方自动填充 NULL

应用场景：

查询全部用户，不管有没有下单；有订单展示订单，没有订单显示null业务场景举例：

1. 用户列表，附带用户最近下单记录

2. 商品列表左关联库存，无库存显示null

SELECT u.id,u.username,o.order_id,o.order_name
FROM `user` u
LEFT JOIN orders o 
ON u.id = o.user_id;

结果特点：

左表所有用户：张三、李四、王五全部展示； 王五没有订单，订单字段全部为 NULL； user_id=4那条无效订单依然不会出现（它在右表）。

⚠️【新手超级大坑】左连接条件不要写到WHERE！ 错误示范：

-- ❌错误！WHERE过滤会把NULL数据干掉，变成等价内连接
SELECT * FROM `user` u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.money > 100;

正确规则：

• 关联条件写 ON

• 对右表的过滤条件写 ON，不要写WHERE

正确写法：

-- ✅只关联金额大于100的订单，用户全部保留
SELECT * FROM `user` u
LEFT JOIN orders o 
  ON u.id = o.user_id AND o.money > 100;

3. RIGHT JOIN 右连接（右外连接）

定义：

保留右表全部数据，左表匹配不上填充NULL

场景：全部订单，匹配对应的用户（不存在的订单也展示）

SELECT u.username,o.*
FROM `user` u
RIGHT JOIN orders o 
ON u.id = o.user_id;

结果：4条订单全部展示，user_id=4那条订单用户名是NULL。

开发技巧：几乎不用！右连接都可以改成左连接，统一左连接更容易维护。

4. FULL JOIN 全外连接

定义：左表全部 + 右表全部，匹配不上都显示NULL

⚠️ MySQL 不支持 FULL JOIN！Oracle、SQL Server支持 MySQL实现全连接方案：左连接 UNION 右连接

-- MySQL模拟全连接
SELECT * FROM `user` u LEFT JOIN orders o ON u.id=o.user_id
UNION
SELECT * FROM `user` u RIGHT JOIN orders o ON u.id=o.user_id;

使用场景极少：需要同时展示无订单用户 + 无归属用户的脏订单

5. SELF JOIN 自连接（一张表自己连自己）

定义：同一张表，起两个别名，自己关联自己

经典业务场景：

1. 部门表：员工和直属上级（上级id=员工id）

2. 分类表：一级分类、二级分类上下级关联

新建测试表

CREATE TABLE emp(
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(20),
  leader_id INT -- 上级员工id
);
INSERT INTO emp VALUES
(1,'董事长',NULL),
(2,'经理',1),
(3,'员工A',2),
(4,'员工B',2);

需求：查询每个员工和他的领导名字

SELECT e.emp_name 员工, l.emp_name 领导
FROM emp e
LEFT JOIN emp l ON e.leader_id = l.emp_id;

三、多表连接（三张及以上表联查实战）

需求：查询下单用户、订单信息、对应商品信息user ↔ orders ↔ goods

SELECT 
  u.username,
  o.order_id,
  o.order_name,
  g.goods_name,
  g.price
FROM `user` u
INNER JOIN orders o ON u.id = o.user_id
LEFT JOIN goods g ON o.order_name = g.goods_name;

多表连接顺序经验

1. 主表放最左边

2. 确定主表之后，依次LEFT JOIN附属表

3. 尽量少用连续INNER JOIN，避免数据意外丢失

真实业务场景示例

场景：后台展示【所有用户】，带出订单信息、订单商品 要求：用户必须全部展示（左连接），订单匹配商品

SELECT
  u.id,
  u.username,
  o.order_id,
  g.goods_name
FROM `user` u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN goods g ON o.order_name = g.goods_name;

四、关联查询另外两种写法（关联子查询 / EXISTS）

很多新手分不清：JOIN联查 VS IN子查询

1. IN 子查询（适合简单场景，大数据性能较差）

-- 查询下过订单的用户
SELECT * FROM `user` 
WHERE id IN (SELECT DISTINCT user_id FROM orders);

2. EXISTS（推荐！大数据量效率高于IN）

原理：存在匹配数据即返回，找到一条立刻停止

SELECT * FROM `user` u
WHERE EXISTS (
  SELECT 1 FROM orders o 
  WHERE o.user_id = u.id
);

✅适用场景：判断主表记录是否存在关联数据

五、一张表看懂所有连接区别

六、新手高频踩坑清单（必看）

坑1：忘记写ON条件，产生笛卡尔积，数据库卡死

大表笛卡尔积直接打爆CPU！禁止漏写关联条件

坑2：左连接，右表过滤条件写WHERE

会把NULL记录过滤，左连接失效，变成内连接 ✅规则：右表筛选条件放在ON，主表筛选放WHERE

坑3：多表字段重名，不加表别名

报错 Column 'id' in field list is ambiguous解决方案：所有字段带上别名 u.id o.order_id

坑4：大量使用 SELECT *

多表联查字段冗余、传输量大，生产环境明确写出需要的字段

坑5：分不清什么时候用LEFT/INNER

简单判断口诀：

我要不要保留主表全部数据？ 需要全部展示 → LEFT JOIN 只想要两边都有的数据 → INNER JOIN

七、综合实战练习题（自己动手巩固）

基于上面测试表完成练习

1. 查询所有用户，显示用户名、订单号、订单金额，没有订单显示null

2. 查询所有订单，展示下单用户名，没有用户的订单也要展示

3. 查询购买了手机的用户姓名（内连接实现）

4. 使用自连接查询员工名称和上级领导名称

如果你需要，我可以把上面4道练习题附带标准参考答案+详细执行结果，同时再补充联查索引优化方案（怎么建索引提升多表join速度）。