m5k1umn 发表于 2024-7-31 09:03:54

MySQL 外连接、内连接与自连接的区别？


    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">引言：本文将深入探讨这些连接类型的概念、语法及其应用场景，<span style="color: black;">帮忙</span>读者全面理解<span style="color: black;">怎样</span>利用这些技术实现<span style="color: black;">繁杂</span>的数据<span style="color: black;">查找</span>和分析。在数据库<span style="color: black;">查找</span>中，连接操作使得<span style="color: black;">咱们</span><span style="color: black;">能够</span><span style="color: black;">按照</span>指定的<span style="color: black;">相关</span><span style="color: black;">要求</span>（join condition）联合两个或多个表中的数据，从而创建出一个新的结果集。连接操作<span style="color: black;">不仅</span>是将数据简单地拼接在<span style="color: black;">一块</span>，<span style="color: black;">更加是</span>一种强大的工具，能够在<span style="color: black;">区别</span>表之间<span style="color: black;">创立</span><span style="color: black;">繁杂</span>的关系，支持多种<span style="color: black;">查找</span><span style="color: black;">需要</span>，从简单的数据匹配到<span style="color: black;">繁杂</span>的多层级数据分析。</span></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">题目</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">MySQL 外连接、内连接与自连接的区别？</span></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">举荐</span>解析</h1>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">内连接（Inner Join）</h1>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">定义和基本语法</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">内连接是一种基本的 SQL 连接操作，它<span style="color: black;">按照</span>两个或多个表之间的<span style="color: black;">一起</span>列值进行匹配，并返回符合<span style="color: black;">要求</span>的行。内连接仅返回两个表中在连接<span style="color: black;">要求</span>上有匹配的行，其他不匹配的行将被排除在结果集之外。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">基本语法：</span></strong></p><span style="color: black;">SELECT</span> <span style="color: black;">columns</span>
    <span style="color: black;">FROM</span> table1
    <span style="color: black;">INNER</span> <span style="color: black;">JOIN</span> table2 <span style="color: black;">ON</span> table1.column = table2.column;<span style="color: black;"><span style="color: black;">table1</span></span><span style="color: black;"> 和 </span><span style="color: black;"><span style="color: black;">table2</span></span><span style="color: black;"> 是要连接的两个表。</span><span style="color: black;"><span style="color: black;">column</span></span><span style="color: black;">是用于连接两个表的列名或表达式。</span><span style="color: black;"><span style="color: black;">columns</span></span><span style="color: black;"> 是要<span style="color: black;">选取</span>的列名，<span style="color: black;">能够</span>是 </span><span style="color: black;"><span style="color: black;">table1.column, table2.column, ...</span></span><span style="color: black;"> <span style="color: black;">或</span> </span><span style="color: black;"><span style="color: black;">*</span></span><span style="color: black;"> <span style="color: black;">暗示</span>所有列。</span>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">内连接的特点</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1）</span><strong style="color: blue;"><span style="color: black;">返回匹配行</span></strong><span style="color: black;">：只返回两个表中连接列上有相同值的行。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2）</span><strong style="color: blue;"><span style="color: black;">结果集<span style="color: black;">体积</span></span></strong><span style="color: black;">：结果集<span style="color: black;">体积</span>取决于连接<span style="color: black;">要求</span>，不匹配的行将被过滤掉。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3）</span><strong style="color: blue;"><span style="color: black;">适用性</span></strong><span style="color: black;">：适用于需要从多个表中获取<span style="color: black;">关联</span>数据的场景，如联合订单和顾客信息等。</span></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">外连接</h1>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">左外连接（Left Outer Join）</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">左外连接从左表（左侧表）中<span style="color: black;">选择</span>所有的行，以及右表中与左表匹配的行。<span style="color: black;">倘若</span>右表中<span style="color: black;">无</span>匹配的行，则在结果集中用 NULL 值填充右表的列。</span></p><span style="color: black;">SELECT</span> <span style="color: black;">columns</span>
    <span style="color: black;">FROM</span> table1
    <span style="color: black;">LEFT</span> <span style="color: black;">JOIN</span> table2 <span style="color: black;">ON</span> table1.column = table2.column;<h1 style="color: black; text-align: left; margin-bottom: 10px;">右外连接（Right Outer Join）</h1><span style="color: black;">SELECT</span> <span style="color: black;">columns</span>
    <span style="color: black;">FROM</span> table1
    <span style="color: black;">RIGHT</span> <span style="color: black;">JOIN</span> table2 <span style="color: black;">ON</span> table1.column = table2.column;<h1 style="color: black; text-align: left; margin-bottom: 10px;">全外连接（Full Outer Join）</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">全外连接会返回左表和右表中的所有行，<span style="color: black;">倘若</span>某个表中<span style="color: black;">无</span>与另一个表匹配的行，则在结果集中用 NULL 值填充相应的列。</span></p><span style="color: black;">SELECT</span> <span style="color: black;">columns</span>
    <span style="color: black;">FROM</span> table1
    <span style="color: black;">FULL</span> <span style="color: black;">JOIN</span> table2 <span style="color: black;">ON</span> table1.column = table2.column;<h1 style="color: black; text-align: left; margin-bottom: 10px;">自连接</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">自连接（Self Join）<span style="color: black;">指的是</span> SQL <span style="color: black;">查找</span>中同一个表的<span style="color: black;">自己</span>连接。<span style="color: black;">一般</span>用于在单个表中比较<span style="color: black;">或</span>组合<span style="color: black;">区别</span>行的数据，<span style="color: black;">平常</span>于需要将表中的数据与其<span style="color: black;">自己</span>进行比较<span style="color: black;">或</span><span style="color: black;">相关</span>的<span style="color: black;">状况</span>。</span></p><span style="color: black;">SELECT</span> t1.column1, t1.column2, ...
    <span style="color: black;">FROM</span> <span style="color: black;">table</span> <span style="color: black;">AS</span> t1
    <span style="color: black;">JOIN</span> <span style="color: black;">table</span> <span style="color: black;">AS</span> t2 <span style="color: black;">ON</span> t1.some_column = t2.some_column;<h1 style="color: black; text-align: left; margin-bottom: 10px;">注意事项</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1）自连接可能会<span style="color: black;">引起</span><span style="color: black;">查找</span>性能下降，<span style="color: black;">尤其</span>是在表数据量<span style="color: black;">很强</span>时。<span style="color: black;">因此呢</span>，在<span style="color: black;">运用</span>自连接时，应<span style="color: black;">小心</span>设计和优化<span style="color: black;">查找</span>语句，<span style="color: black;">保证</span>能够<span style="color: black;">有效</span>执行。</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2）<span style="color: black;">运用</span>自连接时，要<span style="color: black;">保证</span>连接<span style="color: black;">要求</span>足够精确，避免<span style="color: black;">显现</span>意外的结果集，例如无限循环<span style="color: black;">或</span>无效的数据<span style="color: black;">相关</span>。</span></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">其他<span style="color: black;">弥补</span></h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">鱼聪明 AI 的回答：</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">鱼聪明 AI <span style="color: black;">位置</span>：</p>https://www.yucongming.com/
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">1. 内连接（Inner Join）</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">内连接会返回两个表中匹配的行，对性能的影响<span style="color: black;">一般</span>较小，<span style="color: black;">由于</span>它只返回匹配的数据行。内连接的性能<span style="color: black;">重点</span>受以下<span style="color: black;">原因</span>影响：</span></p><strong style="color: blue;"><span style="color: black;"><span style="color: black;">查找</span><span style="color: black;">要求</span>优化</span></strong><span style="color: black;">：<span style="color: black;">保证</span>连接<span style="color: black;">要求</span>是索引列，<span style="color: black;">或</span>有索引支持，<span style="color: black;">能够</span>加速连接过程。</span><strong style="color: blue;"><span style="color: black;">数据量</span></strong><span style="color: black;">：内连接不会返回不匹配的数据行，<span style="color: black;">因此呢</span>在数据量<span style="color: black;">很强</span>时，<span style="color: black;">一般</span>能够较快地执行。</span><strong style="color: blue;"><span style="color: black;">内存消耗</span></strong><span style="color: black;">：内连接只需存储和处理匹配的行，内存消耗<span style="color: black;">一般</span>较低。</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">最佳实践</span></strong><span style="color: black;">：</span></p><span style="color: black;"><span style="color: black;">保证</span>连接<span style="color: black;">要求</span><span style="color: black;">运用</span>索引，以避免全表扫描和性能下降。</span><span style="color: black;">避免不必要的连接，<span style="color: black;">保证</span>仅<span style="color: black;">查找</span>所需的数据，以提<span style="color: black;">有效</span>率。</span>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">2. 外连接（Outer Join）</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">外连接<span style="color: black;">包含</span>左外连接、右外连接和全外连接，其性能相比内连接可能会有所<span style="color: black;">区别</span>：</span></p><strong style="color: blue;"><span style="color: black;">左外连接和右外连接</span></strong><span style="color: black;">：性能<span style="color: black;">一般</span>类似于内连接，<span style="color: black;">然则</span><span style="color: black;">倘若</span>其中一个表的数据量非常大，<span style="color: black;">或</span><span style="color: black;">无</span>合适的索引支持，可能会<span style="color: black;">引起</span>性能下降。</span><strong style="color: blue;"><span style="color: black;">全外连接</span></strong><span style="color: black;">：<span style="color: black;">一般</span>性能最差，<span style="color: black;">由于</span>它需要返回两个表中的所有数据，<span style="color: black;">包含</span>不匹配的数据行，可能会<span style="color: black;">引起</span><span style="color: black;">很强</span>的内存消耗和更长的<span style="color: black;">查找</span>执行时间。</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">最佳实践</span></strong><span style="color: black;">：</span></p><span style="color: black;"><span style="color: black;">针对</span>外连接，<span style="color: black;">尽可能</span>限制返回的数据量，避免在大数据集上执行全外连接，<span style="color: black;">能够</span><span style="color: black;">经过</span>分页<span style="color: black;">或</span>其他手段<span style="color: black;">掌控</span>数据量。</span><span style="color: black;">优化连接<span style="color: black;">要求</span>和表索引，以减少连接操作的数据量和处理时间。</span>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">3. 自连接（Self Join）</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">自连接<span style="color: black;">触及</span>同一表的连接操作，其性能影响与内连接类似，取决于连接<span style="color: black;">要求</span>和表中数据的特性：</span></p><strong style="color: blue;"><span style="color: black;">数据量和索引</span></strong><span style="color: black;">：<span style="color: black;">一样</span>适用于内连接的最佳实践，<span style="color: black;">保证</span>连接<span style="color: black;">要求</span>优化并且索引适当。</span><strong style="color: blue;"><span style="color: black;">内存消耗</span></strong><span style="color: black;">：与内连接<span style="color: black;">类似</span>，自连接<span style="color: black;">一般</span>只需处理匹配的行，<span style="color: black;">因此呢</span>内存消耗相对较低。</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">最佳实践</span></strong><span style="color: black;">：</span></p><span style="color: black;">避免无效的自连接，<span style="color: black;">保证</span>连接<span style="color: black;">要求</span>能够精确地找到需要比较的数据行。</span><span style="color: black;">如同其他连接类型<span style="color: black;">同样</span>，<span style="color: black;">保证</span>性能优化和索引<span style="color: black;">运用</span>。</span>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">总结</h1><strong style="color: blue;"><span style="color: black;">索引的重要性</span></strong><span style="color: black;">：<span style="color: black;">针对</span>任何类型的连接操作，优化连接<span style="color: black;">要求</span>并且<span style="color: black;">保证</span>合适的索引<span style="color: black;">运用</span>是<span style="color: black;">加强</span>性能的关键。</span><strong style="color: blue;"><span style="color: black;">数据量和内存消耗</span></strong><span style="color: black;">：外连接和全外连接可能会引入更大的内存消耗和较长的<span style="color: black;">查找</span>时间，<span style="color: black;">尤其</span>是在大数据集上。</span><strong style="color: blue;"><span style="color: black;"><span style="color: black;">查找</span>设计</span></strong><span style="color: black;">：<span style="color: black;">恰当</span>设计<span style="color: black;">查找</span>，只返回必要的数据，避免不必要的连接操作和数据传输，有助于<span style="color: black;">加强</span>性能。</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">综上所述，理解<span style="color: black;">区别</span>连接类型对数据库性能的影响，并采取相应的最佳实践，<span style="color: black;">能够</span><span style="color: black;">帮忙</span>优化<span style="color: black;">查找</span>并<span style="color: black;">提高</span>数据库的整体性能。</span></p>
    <h1 style="color: black; text-align: left; margin-bottom: 10px;">欢迎交流</h1>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">本<span style="color: black;">文案</span><span style="color: black;">重点</span>介绍外连接、内连接、自连接三者的<span style="color: black;">基本</span>用法、优缺点和<span style="color: black;">运用</span>场景，<span style="color: black;">区别</span>连接的<span style="color: black;">查找</span>性能是<span style="color: black;">区别</span>的，<span style="color: black;">按照</span><span style="color: black;">实质</span>场景进行优化，在文末还有三个关于数据库连接的问题，欢迎小伙伴在评论区进行留言！近期</span><strong style="color: blue;"><span style="color: black;">面试鸭小程序</span></strong><span style="color: black;">已全面上线，想要刷题的小伙伴<span style="color: black;">能够</span>积极参与！</span></p>
    <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/b1dcef9936874c7494e127b48d17afcd~noop.image?_iz=58558&amp;from=article.pc_detail&amp;lk3s=953192f4&amp;x-expires=1722992242&amp;x-signature=nvSH3XAm33pp0WWM3QbtAKkw8z8%3D" style="width: 50%; margin-bottom: 20px;"></div>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1）<span style="color: black;">怎样</span>确定<span style="color: black;">是不是</span>需要<span style="color: black;">运用</span>外连接而不是内连接？</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">2）<span style="color: black;">怎样</span>优化自连接的性能？</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">3）数据库连接在并发环境中的影响是什么？</span></p>




页: [1]
查看完整版本: MySQL 外连接、内连接与自连接的区别？