SQL 连接查询用法总结核心多表通过关联字段拼接数据分为内连接、左连接、右连接、全连接、交叉连接一、基础语法通用格式SELECT字段FROM表1连接类型JOIN表2ON表1.关联字段表2.关联字段;二、五类连接详解1. 内连接 INNER JOIN只返回两表匹配成功的数据交集部分SELECT*FROMuseruINNERJOINorderoONu.ido.user_id;简写可省略INNER直接写JOIN2. 左连接 LEFT JOIN以左表为基准左表数据全部保留右表匹配不上显示null-- 查询所有用户及对应订单无订单也保留用户SELECT*FROMuseruLEFTJOINorderoONu.ido.user_id;3. 右连接 RIGHT JOIN以右表为基准右表数据全部保留左表匹配不上显示nullSELECT*FROMuseruRIGHTJOINorderoONu.ido.user_id;4. 全连接 FULL JOIN取两表所有数据匹配不上字段补空SELECT*FROMuseruFULLJOINorderoONu.ido.user_id;注MySQL不原生支持可用左右连接合并实现5. 交叉连接 CROSS JOIN笛卡尔积两表行数相乘组合极少主动使用SELECT*FROMuserCROSSJOINgoods;三、关键区分速记连接类型数据范围使用场景INNER JOIN两边匹配数据只查有关联的有效数据LEFT JOIN左表全部匹配右表主表数据必展示附属信息可选RIGHT JOIN右表全部匹配左表极少用可互换表改用左连接FULL JOIN两表全部数据汇总全部台账数据四、常用拓展写法多表连续连接SELECT*FROMuseruLEFTJOINorderoONu.ido.user_idLEFTJOINpay pONo.idp.order_id;连接后加筛选条件ON连接前过滤关联规则WHERE连接结果后整体筛选SELECT*FROMuseruLEFTJOINorderoONu.ido.user_idWHEREo.create_time2025-01-01;五、实操要点关联字段一般为主键、外键保证对应关系字段重名需加表别名区分优先左连接可读性更高日常业务最常用内连接自动过滤无效无关联数据六、例题学生表: StudentsColumn NameTypestudent_idintstudent_namevarchar在 SQL 中主键为 student_id学生ID。该表内的每一行都记录有学校一名学生的信息。科目表: SubjectsColumn NameTypesubject_namevarchar在 SQL 中主键为 subject_name科目名称。每一行记录学校的一门科目名称。考试表: ExaminationsColumn NameTypestudent_idintsubject_namevarchar这个表可能包含重复数据换句话说在 SQL 中这个表没有主键。学生表里的一个学生修读科目表里的每一门科目。这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。查询出每个学生参加每一门科目测试的次数结果按 student_id 和 subject_name 排序。查询结构格式如下所示。示例 1输入Students table:student_idstudent_name1Alice2Bob13John6AlexSubjects table:subject_nameMathPhysicsProgrammingExaminations table:student_idsubject_name1Math1Physics1Programming2Programming1Physics1Math13Math13Programming13Physics2Math1Math输出student_idstudent_namesubject_nameattended_exams1AliceMath31AlicePhysics21AliceProgramming12BobMath12BobPhysics02BobProgramming16AlexMath06AlexPhysics06AlexProgramming013JohnMath113JohnPhysics113JohnProgramming1解释结果表需包含所有学生和所有科目即便测试次数为0Alice 参加了 3 次数学测试, 2 次物理测试以及 1 次编程测试Bob 参加了 1 次数学测试, 1 次编程测试没有参加物理测试Alex 啥测试都没参加John 参加了数学、物理、编程测试各 1 次。题目分析 左外连接完整解法这道题是经典的多表左连接 分组统计考题核心难点在于必须让每个学生 × 每门科目都出现一行哪怕一次考试都没参加解题思路生成「所有学生 × 所有科目」的完整组合学生表所有学生科目表所有科目用笛卡尔积CROSS JOIN生成每个学生对应每一门科目→ 这是保证「不漏学生、不漏科目」的关键左连接考试表统计参加次数用LEFT JOIN把考试表关联上去没参加考试的次数记为0分组 排序按student_id, subject_name分组统计次数最后按要求排序最终 SQL 代码SELECTs.student_id,s.student_name,sub.subject_name,COUNT(e.student_id)ASattended_exams-- 统计考试次数FROMStudents sCROSSJOINSubjects sub-- 生成 每个学生 × 每门科目LEFTJOINExaminations e-- 左连接考试表不丢组合ONs.student_ide.student_idANDsub.subject_namee.subject_nameGROUPBYs.student_id,s.student_name,sub.subject_nameORDERBYs.student_id,sub.subject_name;关键步骤解释CROSS JOIN 作用Students CROSS JOIN Subjects直接生成所有学生 × 所有科目的完整组合哪怕学生没考过某科目这一行依然存在LEFT JOIN 为什么必须这么写LEFTJOINExaminations eONs.student_ide.student_idANDsub.subject_namee.subject_name以「学生科目」为主表左边完整保留右边匹配不到考试记录 → 显示 NULLCOUNT(e.student_id)会自动把 NULL 记为 0统计次数COUNT(e.student_id)参加过 → 计数 1没参加 → 计 0为什么不能直接用考试表因为考试表里缺少没参加考试的学生/科目组合直接统计会漏数据必须先生成完整组合再左连接考试表。
SQL 连接查询用法总结
SQL 连接查询用法总结核心多表通过关联字段拼接数据分为内连接、左连接、右连接、全连接、交叉连接一、基础语法通用格式SELECT字段FROM表1连接类型JOIN表2ON表1.关联字段表2.关联字段;二、五类连接详解1. 内连接 INNER JOIN只返回两表匹配成功的数据交集部分SELECT*FROMuseruINNERJOINorderoONu.ido.user_id;简写可省略INNER直接写JOIN2. 左连接 LEFT JOIN以左表为基准左表数据全部保留右表匹配不上显示null-- 查询所有用户及对应订单无订单也保留用户SELECT*FROMuseruLEFTJOINorderoONu.ido.user_id;3. 右连接 RIGHT JOIN以右表为基准右表数据全部保留左表匹配不上显示nullSELECT*FROMuseruRIGHTJOINorderoONu.ido.user_id;4. 全连接 FULL JOIN取两表所有数据匹配不上字段补空SELECT*FROMuseruFULLJOINorderoONu.ido.user_id;注MySQL不原生支持可用左右连接合并实现5. 交叉连接 CROSS JOIN笛卡尔积两表行数相乘组合极少主动使用SELECT*FROMuserCROSSJOINgoods;三、关键区分速记连接类型数据范围使用场景INNER JOIN两边匹配数据只查有关联的有效数据LEFT JOIN左表全部匹配右表主表数据必展示附属信息可选RIGHT JOIN右表全部匹配左表极少用可互换表改用左连接FULL JOIN两表全部数据汇总全部台账数据四、常用拓展写法多表连续连接SELECT*FROMuseruLEFTJOINorderoONu.ido.user_idLEFTJOINpay pONo.idp.order_id;连接后加筛选条件ON连接前过滤关联规则WHERE连接结果后整体筛选SELECT*FROMuseruLEFTJOINorderoONu.ido.user_idWHEREo.create_time2025-01-01;五、实操要点关联字段一般为主键、外键保证对应关系字段重名需加表别名区分优先左连接可读性更高日常业务最常用内连接自动过滤无效无关联数据六、例题学生表: StudentsColumn NameTypestudent_idintstudent_namevarchar在 SQL 中主键为 student_id学生ID。该表内的每一行都记录有学校一名学生的信息。科目表: SubjectsColumn NameTypesubject_namevarchar在 SQL 中主键为 subject_name科目名称。每一行记录学校的一门科目名称。考试表: ExaminationsColumn NameTypestudent_idintsubject_namevarchar这个表可能包含重复数据换句话说在 SQL 中这个表没有主键。学生表里的一个学生修读科目表里的每一门科目。这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。查询出每个学生参加每一门科目测试的次数结果按 student_id 和 subject_name 排序。查询结构格式如下所示。示例 1输入Students table:student_idstudent_name1Alice2Bob13John6AlexSubjects table:subject_nameMathPhysicsProgrammingExaminations table:student_idsubject_name1Math1Physics1Programming2Programming1Physics1Math13Math13Programming13Physics2Math1Math输出student_idstudent_namesubject_nameattended_exams1AliceMath31AlicePhysics21AliceProgramming12BobMath12BobPhysics02BobProgramming16AlexMath06AlexPhysics06AlexProgramming013JohnMath113JohnPhysics113JohnProgramming1解释结果表需包含所有学生和所有科目即便测试次数为0Alice 参加了 3 次数学测试, 2 次物理测试以及 1 次编程测试Bob 参加了 1 次数学测试, 1 次编程测试没有参加物理测试Alex 啥测试都没参加John 参加了数学、物理、编程测试各 1 次。题目分析 左外连接完整解法这道题是经典的多表左连接 分组统计考题核心难点在于必须让每个学生 × 每门科目都出现一行哪怕一次考试都没参加解题思路生成「所有学生 × 所有科目」的完整组合学生表所有学生科目表所有科目用笛卡尔积CROSS JOIN生成每个学生对应每一门科目→ 这是保证「不漏学生、不漏科目」的关键左连接考试表统计参加次数用LEFT JOIN把考试表关联上去没参加考试的次数记为0分组 排序按student_id, subject_name分组统计次数最后按要求排序最终 SQL 代码SELECTs.student_id,s.student_name,sub.subject_name,COUNT(e.student_id)ASattended_exams-- 统计考试次数FROMStudents sCROSSJOINSubjects sub-- 生成 每个学生 × 每门科目LEFTJOINExaminations e-- 左连接考试表不丢组合ONs.student_ide.student_idANDsub.subject_namee.subject_nameGROUPBYs.student_id,s.student_name,sub.subject_nameORDERBYs.student_id,sub.subject_name;关键步骤解释CROSS JOIN 作用Students CROSS JOIN Subjects直接生成所有学生 × 所有科目的完整组合哪怕学生没考过某科目这一行依然存在LEFT JOIN 为什么必须这么写LEFTJOINExaminations eONs.student_ide.student_idANDsub.subject_namee.subject_name以「学生科目」为主表左边完整保留右边匹配不到考试记录 → 显示 NULLCOUNT(e.student_id)会自动把 NULL 记为 0统计次数COUNT(e.student_id)参加过 → 计数 1没参加 → 计 0为什么不能直接用考试表因为考试表里缺少没参加考试的学生/科目组合直接统计会漏数据必须先生成完整组合再左连接考试表。
