SQL语句之内外连接inner join、full join、left join、right join-天涯论坛

m5k1umn 发表于 2024-8-4 10:32:40

SQL语句之内外连接inner join、full join、left join、right join

<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">1，Join</span></strong><span style="color: black;">：SQL<span style="color: black;">查找</span>中语句中重要的操作之一，</span><span style="color: black;">用于把来自两个或多个表结合起来，生成新的关系表。</span><span style="color: black;">常用的Join类型有：内连接（Inner Join）和外连接（outer join），外连接又分为：左连接（Left Join）、右连接（Right Join）和全连接（Full Join）。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">2，实验环境数据</strong>：table1和table2两个表，<span style="color: black;">要求</span>是字段table1_id和table2_id相等。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/c721a471bfac4d0b826b7342bca4b9bb~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=fLKTTiLJ90Fisic%2B56eHPYPNKaA%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">table1</p>
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/437cae02607a4261b627e7337ad2c413~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=du9oReeucio1Ant5euE3yDvV%2F34%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">table2</p>
</div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">3，内连接inner join</strong>：即交集，table1和table2中都有的数据。<span style="color: black;">要求</span>是：table1_id和table2_id字段相等的数据。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select table1.*, table2.* from table1 inner join table2 on table1.table1_id=table2.table2_id</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/6455dd0fb1414fae86b0f84e5cc51988~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=XZ618nI80qy9iv%2Fu2Zqz8Lc8GnI%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">inner join</p>
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/3804e5d5d7804960b0f05fcf19153b91~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=CWTpX0du6BThZIo5K7etpoEYG%2F8%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">inner join</p>
</div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">4，左连接left join</strong>：<span style="color: black;">返回左表的所有行，且右表与左表完全匹配的右表行，其它匹配不上的为NULL</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select table1.*, table2.* from table1 left join table2 on table1.table1_id=table2.table2_id</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/73824a89f1d64ae1bf5bf42726203e1d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=b6hcVn88l3JBe3IWc2BlcQQzsu8%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">left join</p>
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/d530e18c01434a4889502559ef7356d9~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=%2F6cllExY4JwECSg41p6h%2FQWKuCI%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">left join</p>
</div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">5，<strong style="color: blue;">右连接right join</strong>：<span style="color: black;">返回右表的所有行，且右表与左表完全匹配的左表行，其它匹配不上的为NULL</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select table1.*, table2.* from table1 right join table2 on table1.table1_id=table2.table2_id</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/f3b777c3b61241ddb66dbf58ae0150a9~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=dczM%2BMwx6P0MRjdc4Iqk8n2QH%2BY%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">right join</p>
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/23a072801ad94c2b975dea0aa8ccc4c4~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=96xY1lntMvjJvKJRju7UsMehMaM%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">right join</p>
</div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">5，<strong style="color: blue;">全连接full join</strong>：返回<span style="color: black;">两个表各自的匹配或匹配不上的所有行，匹配不上的为NULL</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select table1.*, table2.* from table1 full join table2 on table1.table1_id=table2.table2_id</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p9-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/5844d7b1ff624ea5854181b9f103f00b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=5pBdfj5bzb7sjOf6XwzXo3n59oc%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">full join</p>
</div>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/ea1bc4f8a95e43609a7948e160497131~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339544&x-signature=b%2B9vOpo0CPfDrr2lRLcnDJ0KeNg%3D" style="width: 50%; margin-bottom: 20px;">
   <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">full join</p>
</div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">备注：</strong>外连接的“OUTER”关键字<span style="color: black;">能够</span>省略不写（如left outer join = left join）；内连接只会返回满足连接<span style="color: black;">要求</span>的记录，而外连接则是内连接的扩展，它不仅会返回满足<span style="color: black;">要求</span>的记录，<span style="color: black;">况且</span>还会返回不满足连接<span style="color: black;">要求</span>的记录。</p>

流星的美 发表于 2024-8-24 18:41:35

“NB”（牛×的缩写，表示叹为观止）‌

听听海 发表于 2024-9-8 03:50:32

这夸赞甜到心里，让我感觉温暖无比。

wrjc1hod 发表于 2024-11-10 16:27:49

感谢你的精彩评论，为我的思绪打开了新的窗口。

页: [1]

天涯论坛's Archiver

SQL语句之内外连接inner join、full join、left join、right join