后端文档教程【免费下载链接】system-design-101Explain complex systems using visuals and simple terms. Help you prepare for system design interviews.项目地址https://gitcode.com/GitHub_Trending/sy/system-design-101点击查看免费下载SQL JOIN 是把两张或多张表按连接条件组合成一张结果集的核心操作也是关系型数据库面试中最高频的基础考点之一。本指南以仓库文档 data/guides/how-do-sql-joins-work.md 为主体系统讲解 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 四种连接类型的语义与差异并补充 CROSS JOIN、SELF JOIN 等扩展类型、可运行示例、NULL 行为对照表以及 JOIN 在数据库中的执行流程与常见算法。读完本文你将能在实际查询中准确选择 JOIN 类型、读懂结果集中的 NULL 来源并说清 JOIN 与子查询、执行计划之间的关系。什么是 SQL JOIN在关系型数据库中数据被规范化到多张表中以减少冗余而 JOIN 就是重新把分散的数据组合起来的机制。它通过连接条件通常是两张表的某一列相等把行与行匹配起来形成一张包含多表字段的结果集。JOIN 的通用语法如下SELECT 列列表 FROM 左表 JOIN类型 右表 ON 连接条件;其中ON子句声明连接条件最常见的是等值条件例如A.user_id B.user_id也可以使用不等值条件如A.price B.price或复合条件。理解 JOIN 的关键在于记住保留哪一侧的所有行以及不匹配时以 NULL 填充这两条规则四种基本类型正是围绕这两条规则的四种组合。四种核心 JOIN 类型详解仓库文档通过一张对比示意图点明The diagram above shows how 4 types of SQL joins work in detail.上方示意图详细展示了 4 种 SQL JOIN 的工作方式。下面以两张示例表逐步推演每种连接的结果。先建立两张演示表customers客户与orders订单通过customer_id关联-- customers 表 | customer_id | name | |-------------|--------| | 1 | Alice | | 2 | Bob | | 3 | Carol | -- orders 表 | order_id | customer_id | amount | |----------|-------------|--------| | 101 | 1 | 200 | | 102 | 1 | 350 | | 103 | 2 | 120 |注意Carolcustomer_id 3没有任何订单订单 103属于 Bob。下面四种 JOIN 将围绕这两条不匹配记录展开对比。INNER JOIN只保留两边都匹配的行原文档定义Returns matching rows in both tables.返回两表中互相匹配的行。SELECT c.name, o.order_id, o.amount FROM customers c INNER JOIN orders o ON c.customer_id o.customer_id;结果| name | order_id | amount | |-------|----------|--------| | Alice | 101 | 200 | | Alice | 102 | 350 | | Bob | 103 | 120 |Carol没有订单orders侧没有匹配行因此整行被丢弃没有订单能匹配Carol因此orders侧也无孤儿行输出结果行数 匹配对数此处 3 行与on条件以外的任何顺序无关。INNER JOIN 是最常用的 JOIN 类型它只关心两边都有的数据天然过滤掉无关联的记录适合统计有交易的用户、有评论的文章等交集场景。JOIN关键字省略修饰词时默认就是INNER JOIN。LEFT JOIN左表全保留右表只取匹配行原文档定义Returns all records from the left table, and the matching records from the right table.返回左表的全部记录以及右表中的匹配记录。SELECT c.name, o.order_id, o.amount FROM customers c LEFT JOIN orders o ON c.customer_id o.customer_id;结果| name | order_id | amount | |-------|----------|--------| | Alice | 101 | 200 | | Alice | 102 | 350 | | Bob | 103 | 120 | | Carol | NULL | NULL |要点左表customers的每一行都必然出现在结果中Carol在右表没有匹配订单此时右表的所有列以 NULL 填充一个常见技巧是配合WHERE 右表主键 IS NULL筛选出只存在于左表的行例如找出从未下过单的客户SELECT c.name FROM customers c LEFT JOIN orders o ON c.customer_id o.customer_id WHERE o.order_id IS NULL; -- 结果CarolLEFT JOIN 是最常用的主表 可选补充信息模式查询时保留主体如用户、文章、商品附属信息如订单、评论、库存有则带上无则以 NULL 表示。RIGHT JOIN右表全保留左表只取匹配行原文档定义Returns all records from the right table, and the matching records from the left table.返回右表的全部记录以及左表中的匹配记录。SELECT c.name, o.order_id, o.amount FROM customers c RIGHT JOIN orders o ON c.customer_id o.customer_id;结果| name | order_id | amount | |-------|----------|--------| | Alice | 101 | 200 | | Alice | 102 | 350 | | Bob | 103 | 120 |在本例中右表orders的每一行都能在左表customers找到客户因此结果与 INNER JOIN 一致如果存在找不到客户的孤儿订单则左表列会以 NULL 填充订单仍会出现在结果中。要点RIGHT JOIN 与 LEFT JOIN 是镜像对称关系A LEFT JOIN B等价于B RIGHT JOIN A只是表顺序对调实践中 RIGHT JOIN 使用频率远低于 LEFT JOIN因为多数开发者习惯把主体表写在前面、用 LEFT JOIN 表达同样的语义。面试中常被追问的一点正是RIGHT JOIN 与 LEFT JOIN 的关系。FULL OUTER JOIN两边都全保留原文档定义Returns all records where there is a match in either left or right table.返回左表或右表中任意一侧存在匹配的所有记录。SELECT c.name, o.order_id, o.amount FROM customers c FULL OUTER JOIN orders o ON c.customer_id o.customer_id;结果| name | order_id | amount | |-------|----------|--------| | Alice | 101 | 200 | | Alice | 102 | 350 | | Bob | 103 | 120 | | Carol | NULL | NULL |FULL OUTER JOIN 是LEFT RIGHT的并集左表独有的行、右表独有的行、两边匹配的行全部保留任何一侧缺少匹配时对应列填充 NULL。它常用于数据对账、全量差异对比等需要看到两侧各自独有的数据的场景例如找出无订单的客户和无客户的孤儿订单两部分数据。SELECT c.name, o.order_id FROM customers c FULL OUTER JOIN orders o ON c.customer_id o.customer_id WHERE c.customer_id IS NULL OR o.order_id IS NULL; -- 两侧独有的记录需要留意的是并非所有数据库都原生支持FULL OUTER JOIN例如部分版本的 MySQL 不支持可改用LEFT JOIN ... UNION ... RIGHT JOIN模拟。四种 JOIN 速查对照表JOIN 类型左表行为右表行为不匹配行处理典型用途INNER JOIN仅保留匹配行仅保留匹配行两侧不匹配行都丢弃取交集有交易的用户、有关联的数据LEFT JOIN全部保留仅保留匹配行右表列填充 NULL主体表 可选附属信息RIGHT JOIN仅保留匹配行全部保留左表列填充 NULL与 LEFT JOIN 镜像等价使用较少FULL OUTER JOIN全部保留全部保留缺失侧填充 NULL全量对比、数据对账选型口诀只要一侧全部行就用 LEFT/RIGHT只要交集用 INNER两侧都要看全用 FULL OUTER。更多 JOIN 变体CROSS JOIN 与 SELF JOIN除四种核心类型外面试与日常开发还常涉及两种变体CROSS JOIN交叉连接不带ON条件返回两表的笛卡尔积即左表每行 × 右表每行。行数 左表行数 × 右表行数数据量大时结果爆炸一般仅用于生成排列组合如矩阵、日历、测试数据或配合WHERE实现特定过滤。SELF JOIN自连接同一张表与自身连接必须为表起不同的别名才能区分两侧。典型场景包括查询员工及其直属上级employee e1 JOIN employee e2 ON e1.manager_id e2.employee_id、查找互为好友的用户对等层级关系与配对查询。自连接实际使用的仍是上述四种 JOIN 类型之一只是两侧是同一张表。此外还有NATURAL JOIN按两表同名列自动连接隐式条件容易产生歧义不建议在正式代码中使用和USING (col)按指定列连接要求两侧列名一致等语法糖掌握前四种语义即可覆盖绝大多数场景。JOIN 在数据库内部如何执行理解了语义层之后再来看物理层。仓库文档 data/guides/how-is-a-sql-statement-executed-in-the-database.md 给出了 SQL 在数据库中的典型执行链路语句经传输层到达命令解析器语法/语义分析生成查询树交给优化器生成执行计划再由执行器按计划取数最终通过访问方法从存储引擎读取数据。JOIN 正是在优化器生成执行计划 执行器执行这两步中落地的解析器确认 JOIN 语法与表、列存在性优化器基于统计信息行数、索引选择性决定连接顺序与连接算法生成执行计划执行器按照计划实际完成行匹配与结果集构造。仓库另一篇文档 data/guides/visualizing-a-sql-query.md 也印证了同一流程SQL 先被解析校验再转换为关系代数等内部表示优化器利用索引信息生成执行计划最后由执行器执行并返回结果。这说明语义相同的 JOIN 语句在物理层可能对应完全不同的执行计划这也正是面试官考察为什么同样的 JOIN 有时快有时慢的落点。在物理执行层面主流数据库通常在三类连接算法中择一Nested Loop Join嵌套循环外层表逐行扫描内层表按连接条件查找匹配若内层表连接列有索引则退化为索引查找适合小表驱动大表Hash Join哈希连接对一侧表按连接列建哈希表另一侧表逐行探测适合等值连接且无索引的大表场景Merge Join归并连接两侧表先按连接列排序再像归并排序一样双指针推进匹配适合连接列已有序如索引序的场景。以上算法为各数据库优化器常见实现策略具体选择与代价估算依数据库版本和统计信息而定。面试作答时能说清优化器负责选算法、执行器负责跑计划即可立于不败之地。面试高频追问与避坑要点该文档归类于 data/categories/technical-interviews.md技术面试分类以下是在此基础上值得准备的追问LEFT JOIN 后为什么会出现 NULL因为右表没有匹配行时右表列以 NULL 填充——NULL 是无匹配的语义标记而非数据本身为空。INNER JOIN 与 LEFT JOIN 结果何时相同当右表每一行都能在左表匹配、且左表行数等于匹配对数不存在左表独有的行时二者等价如上面 RIGHT JOIN 示例所示。RIGHT JOIN 何时有用当从右表视角出发、保留右表全量表达更自然时如以订单为主体的补全查询或者需要模拟 FULL OUTER JOIN 的某一半时。JOIN 与子查询Subquery如何取舍多数情况下 JOIN 可读性更好且优化器更容易生成高效计划但存在只取附属表是否存在等场景时EXISTS子查询可能更直观。二者结果等价与否取决于是否产生行数放大一对多导致重复行这是常被忽略的坑。多表 JOIN 注意点先通过等值条件限定连接列警惕一对多连接导致的结果行数膨胀ON条件中的过滤作用于连接阶段而WHERE中的过滤作用于连接之后二者对 LEFT JOIN 的结果语义不同。延伸阅读本仓库还提供了与 SQL 执行相关的两篇配套文档可作为理解 JOIN 上下文的延伸材料data/guides/how-is-a-sql-statement-executed-in-the-database.mdSQL 语句从传输层、解析器、优化器到执行器的完整链路data/guides/visualizing-a-sql-query.mdSQL 查询的逻辑执行顺序与内部表示转换。结合本文的四种 JOIN 语义、NULL 行为与执行原理你已具备在面试中从语法 → 语义 → 物理执行三层完整作答的能力。赞分享后端文档教程【免费下载链接】system-design-101Explain complex systems using visuals and simple terms. Help you prepare for system design interviews.项目地址https://gitcode.com/GitHub_Trending/sy/system-design-101点击查看免费下载相关推荐如何三步装好 Mac Mouse Fix让普通鼠标好用的完整安装指南如何三步装好 Mac Mouse Fix让普通鼠标好用的完整安装指南 侧键除了右键别无作用滚轮一顿一顿还方向反了Mac Mouse Fix 是一款 Mac桌面应用系统编程Apache Spark SQL JOIN 语法详解从七种基本连接到 ASOF 与 NEAREST BY 排名连接Apache Spark SQL JOIN 语法详解从七种基本连接到 ASOF 与 NEAREST BY 排名连接 JOIN 是 Spark SQL 中把两个大数据数据分析批处理流处理机器学习图计算Apache Spark SQL ASOF JOIN 完整实战指南MATCH_CONDITION 语法、匹配语义与底层执行原理Apache Spark SQL ASOF JOIN 完整实战指南MATCH_CONDITION 语法、匹配语义与底层执行原理 本篇指南围绕 Apache S大数据数据分析批处理流处理机器学习图计算创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考