跳到主要内容

JOIN 连接查询

问题​

SQL 中 JOIN 怎么用？INNER JOIN、LEFT JOIN、RIGHT JOIN 有什么区别？

答案​

JOIN（连接） 是 SQL 中最重要的操作之一，用来将多张表的数据关联起来查询。在实际工作中，数据通常分散在不同的表中（用户表、订单表、商品表等），需要用 JOIN 把它们连接起来才能得到完整的信息。

准备示例数据​

为了方便理解，我们用两张简单的表来演示：

employees 员工表：

idnamedept_id
1张三10
2李四20
3王五10
4赵六NULL

departments 部门表：

dept_iddept_name
10技术部
20市场部
30财务部

注意：赵六没有部门（dept_id 为 NULL），财务部没有员工。

四种 JOIN 类型​

INNER JOIN（内连接）​

只返回两表中都有匹配的行。 没有匹配的行被丢弃。

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

结果：

namedept_name
张三技术部
李四市场部
王五技术部
  • 赵六（dept_id=NULL）没出现 → 因为没有匹配的部门
  • 财务部没出现 → 因为没有员工属于它

LEFT JOIN（左连接）​

返回左表的所有行。 如果右表没有匹配的，对应列填 NULL。

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

结果：

namedept_name
张三技术部
李四市场部
王五技术部
赵六NULL
  • 赵六出现了，但部门是 NULL → 左表所有行都保留
  • 财务部仍然没出现 → 因为没有员工引用它
LEFT JOIN 是数据分析中最常用的 JOIN

原因：通常我们以"主表"（如用户表）为基准，希望保留所有主表记录，即使关联表中没有对应数据。

RIGHT JOIN（右连接）​

返回右表的所有行。 如果左表没有匹配的，对应列填 NULL。

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

结果：

namedept_name
张三技术部
王五技术部
李四市场部
NULL财务部
  • 财务部出现了，但员工名是 NULL → 右表所有行都保留
  • 赵六没出现 → 因为 dept_id=NULL 不匹配任何部门
实际上 RIGHT JOIN 很少使用

把 LEFT JOIN 的左右表交换一下就等价于 RIGHT JOIN，所以大多数人习惯只用 LEFT JOIN。

FULL OUTER JOIN（全外连接）​

返回两张表的所有行。 没有匹配的列填 NULL。

-- PostgreSQL 支持 FULL OUTER JOIN
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

结果：

namedept_name
张三技术部
王五技术部
李四市场部
赵六NULL
NULL财务部

所有人和所有部门都出现了，没有匹配的地方填 NULL。

MySQL 不支持 FULL OUTER JOIN

MySQL 没有 FULL OUTER JOIN 语法，可以用 LEFT JOIN UNION RIGHT JOIN 模拟：

SELECT e.name, d.dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id;

JOIN 类型一图总结​

用集合的角度理解 JOIN：

JOIN 类型左表不匹配的行右表不匹配的行
INNER JOIN❌ 丢弃❌ 丢弃
LEFT JOIN✅ 保留（右侧填 NULL）❌ 丢弃
RIGHT JOIN❌ 丢弃✅ 保留（左侧填 NULL）
FULL JOIN✅ 保留✅ 保留

CROSS JOIN（交叉连接）​

返回两表的笛卡尔积——左表的每一行与右表的每一行组合。

-- 4 行 × 3 行 = 12 行
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;
小心笛卡尔积！

如果两张表各有 10000 行，CROSS JOIN 的结果是 1 亿行！除非你确实需要所有组合（如生成日期×商品的完整矩阵），否则避免使用。

忘记写 ON 条件时，JOIN 也会退化为笛卡尔积：

-- ❌ 忘记写 ON 条件，变成了笛卡尔积！
SELECT * FROM employees e JOIN departments d;

自连接（Self Join）​

表自己和自己连接，常见于层级关系（如经理-下属）：

-- 假设 employees 表有 manager_id 字段
-- 查询每个员工及其经理的名字
SELECT 
    e.name AS 员工,
    m.name AS 经理
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

多表 JOIN​

实际工作中经常需要连接 3 张以上的表：

-- 查询：订单号、用户名、商品名、数量
SELECT 
    o.id AS order_id,
    u.name AS user_name,
    p.name AS product_name,
    oi.quantity
FROM orders o
JOIN users u ON o.user_id = u.id              -- 关联用户
JOIN order_items oi ON o.id = oi.order_id     -- 关联订单项
JOIN products p ON oi.product_id = p.id;      -- 关联商品
多表 JOIN 的阅读技巧

从左到右读：从 orders 出发 → 关联用户 → 关联订单项 → 关联商品。每一个 JOIN 都在扩展数据的维度。

USING 与 NATURAL JOIN​

当两张表中存在同名列时，可以用更简洁的写法替代 ON。

USING 语法（简化 ON 条件）​

当连接键在两张表中列名相同时，USING(列名) 比 ON a.列名 = b.列名 更简洁，且结果集中该列只出现一次：

-- 普通写法
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- 使用 USING 的简化写法（两表都有 dept_id 列）
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d USING (dept_id);

USING 写法在多键连接时同样适用：

-- 多列连接
SELECT *
FROM order_items oi
JOIN returns r USING (order_id, product_id);
USING 与 ON 的结果差异

使用 ON 时，两张表的连接键列都会出现在 SELECT * 的结果中（如 e.dept_id 和 d.dept_id）；使用 USING 时，该列只出现一次，结果更干净。

NATURAL JOIN（谨慎使用）​

NATURAL JOIN 会自动匹配两张表中所有同名列作为连接条件，无需显式指定：

-- NATURAL JOIN 自动找到同名列（dept_id）进行连接
SELECT name, dept_name
FROM employees
NATURAL JOIN departments;
为什么要谨慎使用 NATURAL JOIN？

NATURAL JOIN 存在几个严重隐患：

  1. 隐式依赖表结构：如果某天给表新增了一个碰巧同名的列（如两张表都加了 updated_at），连接条件会悄悄改变，导致查询结果错误且极难排查
  2. 可读性差：读代码的人必须去查表结构才能知道实际是在按哪些列连接
  3. 不同数据库行为有差异：部分数据库或 ORM 框架对 NATURAL JOIN 的支持不一致

结论：生产环境中始终使用显式的 ON 条件，避免使用 NATURAL JOIN。

JOIN 的执行原理（了解）​

数据库执行 JOIN 时主要有三种算法：

算法原理适用场景
Nested Loop双重循环：对外表的每一行，扫描内表匹配小数据量、有索引
Hash Join构建哈希表，快速匹配大表等值连接、无索引
Sort Merge先排序后合并大表、已排序数据

优化器会自动选择，但你可以通过在连接键上建索引来提升性能。

集合操作：UNION / INTERSECT / EXCEPT​

JOIN 是横向扩展（增加列），而集合操作是纵向合并（增加行）。它们合并的是两个查询的结果集，要求两个查询的列数相同、对应列类型兼容。

UNION（合并去重）​

合并两个查询的结果，自动去除重复行：

-- 查询所有出现在 employees 或 managers 表中的员工名（去重）
SELECT name FROM employees
UNION
SELECT name FROM managers;

UNION 会对结果进行去重，因此有额外的排序/哈希开销。如果确定两个结果集没有重复，优先使用 UNION ALL。

UNION ALL（合并保留重复）​

合并两个查询的结果，保留所有行包括重复的：

-- 合并 2023 年和 2024 年的订单日志（允许同一用户出现多次）
SELECT user_id, order_date, amount FROM orders_2023
UNION ALL
SELECT user_id, order_date, amount FROM orders_2024
ORDER BY order_date;
UNION ALL 比 UNION 性能更好

UNION 需要额外做去重（相当于 DISTINCT），UNION ALL 直接追加结果。如果业务上允许重复（如日志合并），始终使用 UNION ALL。

INTERSECT（交集）​

只返回同时出现在两个查询结果中的行：

-- 查找既购买了商品 A 又购买了商品 B 的用户
SELECT user_id FROM orders WHERE product_id = 'A'
INTERSECT
SELECT user_id FROM orders WHERE product_id = 'B';
MySQL 不支持 INTERSECT

MySQL 8.0.31 之前不支持 INTERSECT，可以用 INNER JOIN 替代：

SELECT DISTINCT a.user_id
FROM orders a
JOIN orders b ON a.user_id = b.user_id
WHERE a.product_id = 'A' AND b.product_id = 'B';

EXCEPT / MINUS（差集）​

只返回在第一个查询结果中有、但第二个查询结果中没有的行：

-- 查找注册了但从未下过单的用户（PostgreSQL / SQL Server 用 EXCEPT）
SELECT user_id FROM users
EXCEPT
SELECT DISTINCT user_id FROM orders;

-- Oracle 使用 MINUS
SELECT user_id FROM users
MINUS
SELECT DISTINCT user_id FROM orders;
MySQL 不支持 EXCEPT，用 LEFT JOIN + IS NULL 替代
-- 效果等同于 EXCEPT
SELECT u.user_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.user_id IS NULL;
操作作用是否去重MySQL 支持
UNION合并两个结果集✅ 去重✅
UNION ALL合并两个结果集❌ 不去重✅
INTERSECT取两个结果集的交集✅ 去重MySQL 8.0.31+
EXCEPT / MINUS取第一个结果集中有、第二个没有的行✅ 去重❌（用 LEFT JOIN 替代）

数据分析实战场景​

场景 1：用户留存分析​

-- 计算次日留存率
-- 找到第 1 天登录且第 2 天也登录的用户比例
SELECT 
    a.login_date,
    COUNT(DISTINCT a.user_id) AS day0_users,
    COUNT(DISTINCT b.user_id) AS day1_retained,
    ROUND(COUNT(DISTINCT b.user_id) * 100.0 / COUNT(DISTINCT a.user_id), 2) AS retention_rate
FROM user_logins a
LEFT JOIN user_logins b 
    ON a.user_id = b.user_id 
    AND b.login_date = DATE_ADD(a.login_date, INTERVAL 1 DAY)
GROUP BY a.login_date;

场景 2：订单漏斗分析​

-- 电商漏斗：浏览 → 加购 → 下单 → 支付
SELECT 
    COUNT(DISTINCT v.user_id) AS view_users,
    COUNT(DISTINCT c.user_id) AS cart_users,
    COUNT(DISTINCT o.user_id) AS order_users,
    COUNT(DISTINCT p.user_id) AS paid_users
FROM page_views v
LEFT JOIN cart_items c ON v.user_id = c.user_id AND v.product_id = c.product_id
LEFT JOIN orders o ON v.user_id = o.user_id
LEFT JOIN payments p ON o.id = p.order_id AND p.status = 'success'
WHERE v.event_date = '2024-01-15';

常见面试问题​

Q1: INNER JOIN 和 LEFT JOIN 的区别？什么时候用哪个？​

答案：

  • INNER JOIN：只返回两表都匹配的行。适合"两边都必须有数据"的场景
  • LEFT JOIN：返回左表所有行 + 右表匹配的行（不匹配填 NULL）。适合"以左表为主，右表可能没数据"的场景

选择建议：

场景推荐
查询有订单的用户INNER JOIN
查询所有用户（包括没下单的）LEFT JOIN
统计用户订单数（含 0 单用户）LEFT JOIN + COUNT

Q2: 写一个 SQL：查出没有下过单的用户​

答案：

-- 方法 1：LEFT JOIN + IS NULL（最常用）
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 方法 2：NOT EXISTS（性能通常更好）
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 方法 3：NOT IN（注意 NULL 陷阱）
SELECT * FROM users 
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

LEFT JOIN + IS NULL 是面试中最常见的"查找不匹配记录"的模式。

Q3: ON 条件和 WHERE 条件有什么区别？​

答案：

  • ON 条件：定义表的连接规则，在 JOIN 时生效
  • WHERE 条件：对 JOIN 结果进行过滤，在连接之后生效

对于 INNER JOIN，效果一样。对于 LEFT JOIN，区别很大：

-- 查询所有用户和他们的 VIP 订单
-- ❌ 错误：WHERE 过滤会把没有 VIP 订单的用户也排除了
SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.type = 'VIP';  -- 变成了 INNER JOIN 的效果！

-- ✅ 正确：把条件放在 ON 中
SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.type = 'VIP';
-- 所有用户都保留，没有 VIP 订单的用户 o.id 显示 NULL

Q4: 怎么避免笛卡尔积（意外的大量重复行）？​

答案：

笛卡尔积通常是因为JOIN 条件不充分导致的：

  1. 忘记写 ON 条件：SELECT * FROM a JOIN b（缺少 ON）
  2. 关联键不唯一：如果一个用户有 3 个订单，一个订单有 4 个订单项，JOIN 后会产生 12 行

排查方法：

-- 先检查结果行数是否异常
SELECT COUNT(*) FROM table_a a JOIN table_b b ON a.id = b.a_id;

-- 检查关联键是否唯一
SELECT a_id, COUNT(*) FROM table_b GROUP BY a_id HAVING COUNT(*) > 1;

Q5: 面试手写题——查询每个部门薪资最高的员工​

答案：

-- 方法 1：子查询
SELECT e.*
FROM employees e
WHERE e.salary = (
    SELECT MAX(salary) FROM employees WHERE dept_id = e.dept_id
);

-- 方法 2：窗口函数（更推荐，见 SQL 分析进阶）
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 1;

Q6: JOIN 中 ON 条件有多个条件时，顺序有影响吗？​

答案：

对于结果的正确性，条件顺序没有影响（AND 是交换律，A AND B 等价于 B AND A）。但对于查询性能，顺序可能影响优化器的判断：

-- 下面两种写法结果完全相同
-- 写法 1
SELECT * FROM orders o
JOIN users u ON o.user_id = u.id AND o.status = 'paid' AND o.amount > 100;

-- 写法 2
SELECT * FROM orders o
JOIN users u ON o.amount > 100 AND o.status = 'paid' AND o.user_id = u.id;

实际建议：

  1. 把连接键（如 o.user_id = u.id）放在 ON 的第一个条件，语义最清晰
  2. 过滤条件（如 status = 'paid'）放 ON 还是 WHERE 取决于是否需要保留外连接的 NULL 行（见 Q3）
  3. 现代优化器（MySQL、PostgreSQL）会自动选择最优执行顺序，手动调整条件顺序通常收益有限；真正影响性能的是是否有合适的索引

Q7: 如何查找两个表中相同的记录？（INTERSECT vs INNER JOIN）​

答案：

有两种主要方式，结果等价但写法和语义不同：

-- 假设有 table_a 和 table_b，都有 email 列
-- 目标：找出两张表都存在的 email

-- 方法 1：INTERSECT（语义最清晰，PostgreSQL/SQL Server）
SELECT email FROM table_a
INTERSECT
SELECT email FROM table_b;

-- 方法 2：INNER JOIN（MySQL 通用写法）
SELECT DISTINCT a.email
FROM table_a a
JOIN table_b b ON a.email = b.email;

-- 方法 3：EXISTS（当 table_b 数据量很大时性能较好）
SELECT DISTINCT email FROM table_a
WHERE EXISTS (SELECT 1 FROM table_b WHERE table_b.email = table_a.email);
方法优点缺点
INTERSECT语义清晰、写法简洁MySQL 8.0.31 之前不支持
INNER JOIN + DISTINCT通用性最好、可扩展其他列写法略繁琐，需注意去重
EXISTS大表时性能好，找到即停止扫描只能返回主表的列

Q8: 面试题——查出每个部门人数最多和最少的岗位​

答案：

这道题考察分组聚合 + 排名的组合，推荐用窗口函数：

-- 假设 employees 表有 dept_id（部门）和 job_title（岗位）字段

-- 第一步：统计每个部门每个岗位的人数
WITH dept_job_count AS (
    SELECT
        dept_id,
        job_title,
        COUNT(*) AS cnt
    FROM employees
    GROUP BY dept_id, job_title
),
-- 第二步：在每个部门内对岗位人数排名（正序 = 最少，倒序 = 最多）
ranked AS (
    SELECT
        dept_id,
        job_title,
        cnt,
        RANK() OVER (PARTITION BY dept_id ORDER BY cnt DESC) AS rank_most,
        RANK() OVER (PARTITION BY dept_id ORDER BY cnt ASC)  AS rank_least
    FROM dept_job_count
)
-- 第三步：取人数最多（rank_most=1）或最少（rank_least=1）的岗位
SELECT
    dept_id,
    job_title,
    cnt,
    CASE
        WHEN rank_most = 1 AND rank_least = 1 THEN '最多且最少'
        WHEN rank_most = 1 THEN '人数最多'
        ELSE '人数最少'
    END AS category
FROM ranked
WHERE rank_most = 1 OR rank_least = 1
ORDER BY dept_id, cnt DESC;

注意事项：

  • 用 RANK() 而非 ROW_NUMBER()，是为了处理并列的情况（多个岗位人数相同时都应返回）
  • 若某部门只有一个岗位，它既是最多也是最少，用 CASE WHEN 合并展示
  • 若不支持窗口函数，可用子查询 + WHERE cnt = (SELECT MAX/MIN(cnt)...) 替代

相关链接​