Mysql面试题：内连接、左连接、右连接的区别
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">阅读本文前，请您先点击上面的</span><span style="color: black;">“蓝色字体”</span><span style="color: black;">，再点击</span><span style="color: black;">“<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 style="color: black;">每日</span>都有进步了。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">内连接</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">结合两张表的记录，返回<span style="color: black;">关联</span>的<span style="color: black;">查找</span>结果，返回的是两个表的交集部分。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">关键字</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">INNER JOIN</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/IXO0OJ6qaw05wY8ibnoOX3LQCwQ31DjKrU52xicjpbiaCuO2fUOobgzPia7D4ve3M4Jia5PhQAGwPDUmkwgy2Nt74eA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">左连接</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">左连接<span style="color: black;">查找</span>，左表的信息<span style="color: black;">所有</span>展示出来，右表只会展示符合搜索<span style="color: black;">要求</span>的信息，不足的<span style="color: black;">地区</span>记为NULL</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">关键字</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">LEFT JOIN</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/IXO0OJ6qaw05wY8ibnoOX3LQCwQ31DjKr3mugnhegkNNY9icIrhsQiamJkylGoXfgZY7jHfSDtrE9dBYIfxLWiaYbA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">右连接</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">右连接<span style="color: black;">查找</span>，右表的信息<span style="color: black;">所有</span>展示出来，左表只会展示符合搜索<span style="color: black;">要求</span>的信息，不足的<span style="color: black;">地区</span>记为NULL</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">关键字</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">RIGHT JOIN</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">下面举个<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;">首要</span>创建两个新表,数据如下:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">student表数据</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/IXO0OJ6qaw05wY8ibnoOX3LQCwQ31DjKr32RDDCYwkOPyEMrao8jpFgColrDxLDj2smiaiaUfvQP0XupXSEjVJWEw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">score表数据</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/IXO0OJ6qaw05wY8ibnoOX3LQCwQ31DjKrFwVsSBL3ejslnCPIM8p69ZgoXJic31Yh62fibhbd4j6ibnIBVpxLKIo6A/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">能够</span>看到students表中stu_id为16048008的记录对应score表<span style="color: black;">无</span>数据;</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1. 当进行内连接时,系统会自动忽略两个表中对应不起来的数据:</p><span style="color: black;"><span style="color: black;">-- <span style="color: black;">表示</span>内连接所有数据:</span></span><span style="color: black;"><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> students st <span style="color: black;">INNER</span> <span style="color: black;">JOIN</span> score sc <span style="color: black;">ON</span> st.sid=sc.stu_id;</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">数据太多,只截图最末尾的:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/IXO0OJ6qaw05wY8ibnoOX3LQCwQ31DjKrbJtm9q09WznYeh3Mpz52VnIcJ3bKHImj8vmJbI5P6oVnVKIXb6SfUA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">能够</span>看到,数据只<span style="color: black;">表示</span>到16048007,16048008的并<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>联的数据.</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2. 左链接,<span style="color: black;">表示</span>关键词left左边表中的所有数据,右边表数据数据少了补NULL值,数据多了不<span style="color: black;">表示</span>;</p><span style="color: black;"><span style="color: black;">-- 左外链接 left</span></span><span style="color: black;"><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> students st <span style="color: black;">LEFT</span> <span style="color: black;">JOIN</span> score sc <span style="color: black;">ON</span> st.sid=sc.stu_id;</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/IXO0OJ6qaw05wY8ibnoOX3LQCwQ31DjKrzFY3UkIB6hGPQN6ovQSicHnwI3Dv9RTOnUkdRfiac9PWItXVK9mxuKsw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">可以看到,16048008在右边score表中<span style="color: black;">无</span>数据,系统用NUll补齐</p><span style="color: black;"><span style="color: black;">SELECT</span> * <span style="color: black;">FROM</span> score sc <span style="color: black;">LEFT</span> <span style="color: black;">JOIN</span> students st <span style="color: black;">ON</span> st.sid=sc.stu_id;</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/sz_mmbiz_png/IXO0OJ6qaw05wY8ibnoOX3LQCwQ31DjKrricW8qmBeh2KDRJYEtnl8El8y6WNwquTrEgiakgazAFfyN9LUso6FYrA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">上图是<span style="color: black;">咱们</span>将students表和score表换了一下位置的运行结果,<span style="color: black;">能够</span>看出,本来右边表16048008记录,<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>与左边表对应的数据,少了补NULl,多了删除;</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">同理,右链接是以右边为参照,左边少了补NULL,多了删除,<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;">能够</span>得出:</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">内连接,<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;">左链接,以左表为参照,<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;">右链接,以右表为参照<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;">--- THE END ---</span></p>
<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 style="color: black;">每日</span>都能学点新知识，请关注<span style="color: black;">咱们</span></span></span></p>
我赞同你的看法，你的智慧让人佩服，谢谢分享。 我完全同意你的观点，说得太对了。 楼主果然英明！不得不赞美你一下！
页:
[1]