详解区分内连接、左连接、外连接、union、union all等sql连接
<div style="color: black; text-align: left; margin-bottom: 10px;">
<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>的笔记，<span style="color: black;">大众</span>都<span style="color: black;">晓得</span>SQL的Join语法有<span style="color: black;">非常多</span>inner的，有outer的，有left的，有时候，<span style="color: black;">针对</span>Select出来的结果集是什么样子有点不是很清楚，今天用图来区别一下sql的连接方式：<strong style="color: blue;">inner join、left join、right join、full outer join、union、union all。</strong></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;">咱们</span>有两张表。Table A 是左边的表。Table B 是右边的表。其各有四条记录，其中有两条记录name是相同的，如下所示：</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/6f52362df779446282d22c60702345e2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=S49CRlWS9izXo8tD%2B0bMeaDmMaI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">下面演示下<strong style="color: blue;">inner join、left join、right join、full outer join、union、union all之间的区别：</strong></p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><strong style="color: blue;">1、INNER JOIN</strong></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">SELECT * FROM TableA <strong style="color: blue;">INNER JOIN </strong>TableB ON TableA.name = TableB.name</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/3fbeaf420a9c47a2971a69f1e1bb5393~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=dfdQPzH1ceThYgz43bausuHH%2F44%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><strong style="color: blue;">2.FULL JOIN</strong></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">(1)SELECT * FROM TableA <strong style="color: blue;">FULL OUTER JOIN </strong>TableB ON TableA.name = TableB.name</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/bb87df7486e84602a87385ead93cb759~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=jXVsER0XG7I5QMgGdLzJZ2BOPOo%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;">重视</span>：这个在获取到数据进行展示的时候，要<span style="color: black;">重视</span>NULL的判断。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">(2)SELECT * FROM TableA <strong style="color: blue;">FULL OUTER JOIN </strong>TableB ON TableA.name = TableB.name where TableA.id is null or TableB.id is null</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/615c7ebcccdf4fe7b9f18d3795357aa3~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=zmbzN87rxUixnFQ98BeP5tO0wrw%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;">同期</span><span style="color: black;">运用</span>ISNULL 的判断，<span style="color: black;">能够</span>取得A和B两者<span style="color: black;">无</span>交集的数据集。这个很好用，<span style="color: black;">能够</span>用来对生产<span style="color: black;">或</span>测试上的数据进行补数据的操作。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">3.LEFTJOIN</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">(1)select * from TableA LEFT OUTER JOIN TableB on TableA.name=TableB.name</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/f8e6cb80dc7742bc8b1efa3aa4d38132~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=mv7%2BW%2ByQAmZHNuQZ2lQpfyGLghQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">(2)select * from TableA LEFT OUTER JOIN TableB on TableA.name=TableB.name where TableB.id is null</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/282c7fbb46b9442794e235683f5ca3e2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=639pw6NRAbsOkZBAG2NJg4SOlDY%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><strong style="color: blue;">4.RIGHT JOIN</strong></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">RIGHT OUTERJOIN 是后面的表为<span style="color: black;">基本</span>，与LEFT OUTER JOIN用法类似。<span style="color: black;">这儿</span>不介绍了。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><strong style="color: blue;">5.UNION </strong>与<strong style="color: blue;"> UNION ALL</strong></h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">UNION 操作符用于合并两个或多个 SELECT 语句的结果集。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">重视</span>：UNION 内部的 SELECT 语句<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>，每条 SELECT 语句中的列的<span style="color: black;">次序</span><span style="color: black;">必要</span>相同。UNION 只<span style="color: black;">选择</span>记录，而UNION ALL会列出所有记录。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">(1)SELECT name FROM TableA <strong style="color: blue;">UNION </strong>SELECT name FROM TableB</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/6276abefe751489ebd89eb26a70099c1~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=36Hs7OjTAejHj2%2F9dOMT8dcIkVE%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;">选择</span><span style="color: black;">区别</span>值</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">(2)SELECT name FROM TableA <strong style="color: blue;">UNION ALL</strong>SELECT name FROM TableB</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/8a375fef28284bfc954f45a682cece21~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=nupY%2Bl%2BgA%2Fy6WK3C59IHmApGS%2Bg%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;">所有</span>列出来</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">(3)SELECT * FROM TableA <strong style="color: blue;">UNION </strong>SELECT * FROM TableB</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/a234300f7dcc42cf981b39e368d28fed~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=2ruAjV3PT%2BvSSaVS6qMluM7ZOKA%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;">因为</span> id 1 Pirate 与 id 2 Pirate 并不相同，不合并。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：“交差集” cross join, 这种Join<span style="color: black;">无</span>办法用文式图<span style="color: black;">暗示</span>，<span style="color: black;">由于</span>其<span style="color: black;">便是</span>把表A和表B的数据进行一个N*M的组合，即笛卡尔积。表达式如下：SELECT * FROM TableA CROSS JOIN TableB</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">这个笛卡尔乘积会产生 4 x 4 = 16 条记录，<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>嵌套的select语句，一般系统都会产生笛卡尔乘积然再做过滤。这是<span style="color: black;">针对</span>性能<span style="color: black;">来讲</span>是非常危险的，尤其是表很大的时候。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/8b84ba8752284604b9f291c93aadb6f8~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=zdS6Yfbn0ryvobpxDSnyyW8dIJs%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">关于sql几种连接方式：inner join、left join、right join、full outer join、union、union all就介绍到这了，<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>，例如A表是数量很少，B表是几十万条数据,用left join显然不合适,得用inner join减少查出来的记录数。</strong></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>关于DBA方面内容，感兴趣的<span style="color: black;">伴侣</span><span style="color: black;">能够</span>关注下！！</strong></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/9c4188d6d37a44e884f10ceef8903c28~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723338441&x-signature=GCGKbg9qkYekg398ElrIEbVP37M%3D" style="width: 50%; margin-bottom: 20px;"></div>
</div>
交流如星光璀璨，点亮思想夜空。 感谢您的精彩评论，为我带来了新的思考角度。 谷歌外链发布 http://www.fok120.com/ 谷歌外链发布 http://www.fok120.com/
页:
[1]