SQL的各种连接Join案例详解
<p>最常见的 join 类型：sql inner join（简单的 join）、sql left join、sql right join、sql full join，其中前一种是内连接，后三种是外链接。</p>
<p>
假设我们有两张表，table a是上边的表，table b是下边的表。</p>
<p>
table a</p>
<p>
</p>
<table><tbody>
<tr>
<td>
id</td>
<td>
name</td>
</tr>
<tr>
<td>
1</td>
<td>
google</td>
</tr>
<tr>
<td>
2</td>
<td>
淘宝</td>
</tr>
<tr>
<td>
3</td>
<td>
微博</td>
</tr>
<tr>
<td>
4</td>
<td>
facebook</td>
</tr>
</tbody></table>
<p>
</p>
<p>
table b</p>
<p>
</p>
<table><tbody>
<tr>
<td width="127">
id</td>
<td width="523">
address</td>
</tr>
<tr>
<td width="127">
1</td>
<td width="523">
美国</td>
</tr>
<tr>
<td width="127">
5</td>
<td width="523">
中国</td>
</tr>
<tr>
<td width="127">
3</td>
<td width="523">
中国</td>
</tr>
<tr>
<td width="127">
6</td>
<td width="523">
美国</td>
</tr>
</tbody></table>
<p>
</p>
<p>
<strong>一、inner join</strong></p>
<p>
内连接是最常见的一种连接，只连接匹配的行。</p>
<p>
inner join语法</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_933223">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">column_name(s)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql keyword">table</code> <code class="sql plain">2</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">on</code>
</div>
<div class="line number5 index4 alt2">
<code class="sql keyword">table</code> <code class="sql plain">1.column_name=</code><code class="sql keyword">table</code> <code class="sql plain">2.column_name</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
注释：inner join与join是相同</p>
<p>
<img title="SQL的各种连接Join案例详解" alt="SQL的各种连接Join案例详解" src="https://zhuji.jb51.net/uploads/img/202305/9aea1a3bebc46c59a0f1749ec729fe99.jpg"><br>
inner join产生的结果集中，是1和2的交集。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_777537">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">a </code><code class="sql keyword">inner</code> <code class="sql color1">join</code> <code class="sql keyword">table</code> <code class="sql plain">b</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">on</code> <code class="sql keyword">table</code> <code class="sql plain">a.id=</code><code class="sql keyword">table</code> <code class="sql plain">b.id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
执行以上sql输出结果如下：</p>
<p>
</p>
<table><tbody>
<tr>
<td>
id</td>
<td>
name</td>
<td>
address</td>
</tr>
<tr>
<td>
1</td>
<td>
google</td>
<td>
美国</td>
</tr>
<tr>
<td>
3</td>
<td>
微博</td>
<td>
中国</td>
</tr>
</tbody></table>
<p>
</p>
<p>
<strong>二、left join</strong></p>
<p>
left join返回左表的全部行和右表满足on条件的行，如果左表的行在右表中没有匹配，那么这一行右表中对应数据用null代替。</p>
<p>
left join 语法</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_500310">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">column_name(s)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql color2">left</code> <code class="sql color1">join</code> <code class="sql keyword">table</code> <code class="sql plain">2</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">on</code> <code class="sql keyword">table</code> <code class="sql plain">1.column_name=</code><code class="sql keyword">table</code> <code class="sql plain">2.column_name</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
注释：在某些数据库中，left join 称为left outer join</p>
<p>
<img title="SQL的各种连接Join案例详解" alt="SQL的各种连接Join案例详解" src="https://zhuji.jb51.net/uploads/img/202305/2c45d852f4c03475ed3c76e8ca5c7d99.jpg"><br>
left join产生表1的完全集，而2表中匹配的则有值，没有匹配的则以null值取代。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_375968">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">a </code><code class="sql color2">left</code> <code class="sql color1">join</code> <code class="sql keyword">table</code> <code class="sql plain">b</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">on</code> <code class="sql keyword">table</code> <code class="sql plain">a.id=</code><code class="sql keyword">table</code> <code class="sql plain">b.id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
执行以上sql输出结果如下：</p>
<p>
</p>
<table><tbody>
<tr>
<td>
id</td>
<td>
name</td>
<td>
address</td>
</tr>
<tr>
<td>
1</td>
<td>
google</td>
<td>
美国</td>
</tr>
<tr>
<td>
2</td>
<td>
淘宝</td>
<td>
null</td>
</tr>
<tr>
<td>
3</td>
<td>
微博</td>
<td>
中国</td>
</tr>
<tr>
<td>
4</td>
<td>
facebook</td>
<td>
null</td>
</tr>
</tbody></table>
<p>
</p>
<p>
<strong>三、right join</strong></p>
<p>
right join返回右表的全部行和左表满足on条件的行，如果右表的行在左表中没有匹配，那么这一行左表中对应数据用null代替。</p>
<p>
right join语法</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_569875">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">column_name(s)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql color2">right</code> <code class="sql color1">join</code> <code class="sql keyword">table</code> <code class="sql plain">2</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">on</code> <code class="sql keyword">table</code> <code class="sql plain">1.column_name=</code><code class="sql keyword">table</code> <code class="sql plain">2.column_name</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
注释：在某些数据库中，right join 称为right outer join</p>
<p>
<img title="SQL的各种连接Join案例详解" alt="SQL的各种连接Join案例详解" src="https://zhuji.jb51.net/uploads/img/202305/515916d5ad4cff6d4ed7cde0f6eb47a9.jpg"><br>
right join产生表2的完全集，而1表中匹配的则有值，没有匹配的则以null值取代。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_452357">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">a </code><code class="sql color2">right</code> <code class="sql color1">join</code> <code class="sql keyword">table</code> <code class="sql plain">b</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">on</code> <code class="sql keyword">table</code> <code class="sql plain">a.id=</code><code class="sql keyword">table</code> <code class="sql plain">b.id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
执行以上sql输出结果如下：</p>
<p>
</p>
<table><tbody>
<tr>
<td>
id</td>
<td>
name</td>
<td>
address</td>
</tr>
<tr>
<td>
1</td>
<td>
google</td>
<td>
美国</td>
</tr>
<tr>
<td>
5</td>
<td>
null</td>
<td>
中国</td>
</tr>
<tr>
<td>
3</td>
<td>
微博</td>
<td>
中国</td>
</tr>
<tr>
<td>
6</td>
</tr>
</tbody></table>
<p>
</p>
<p>
<strong>四、full outer join</strong></p>
<p>
full join 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用null代替</p>
<p>
full outer join语法</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_252553">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">column_name(s)</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">1</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">full</code> <code class="sql color1">outer</code> <code class="sql color1">join</code> <code class="sql keyword">table</code> <code class="sql plain">2</code>
</div>
<div class="line number4 index3 alt1">
<code class="sql keyword">on</code> <code class="sql keyword">table</code> <code class="sql plain">1.column_name=</code><code class="sql keyword">table</code> <code class="sql plain">2.column_name</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<img title="SQL的各种连接Join案例详解" alt="SQL的各种连接Join案例详解" src="https://zhuji.jb51.net/uploads/img/202305/1d8d57a1630ac66b33bb4765b385d3e4.jpg"><br>
full outer join产生1和2的并集。但是需要注意的是，对于没有匹配的记录，则会以null做为值。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_309697">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql keyword">table</code> <code class="sql plain">a </code><code class="sql keyword">full</code> <code class="sql color1">outer</code> <code class="sql color1">join</code> <code class="sql keyword">table</code> <code class="sql plain">b</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">on</code> <code class="sql keyword">table</code> <code class="sql plain">a.id=</code><code class="sql keyword">table</code> <code class="sql plain">b.id</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
执行以上sql输出结果如下：</p>
<p>
</p>
<table><tbody>
<tr>
<td>
id</td>
<td>
name</td>
<td>
address</td>
</tr>
<tr>
<td>
1</td>
<td>
google</td>
<td>
美国</td>
</tr>
<tr>
<td>
2</td>
<td>
淘宝</td>
<td>
null</td>
</tr>
<tr>
<td>
3</td>
<td>
微博</td>
<td>
中国</td>
</tr>
<tr>
<td>
4</td>
<td>
facebook</td>
<td>
null</td>
</tr>
<tr>
<td>
5</td>
<td>
null</td>
<td>
中国</td>
</tr>
<tr>
<td>
6</td>
<td>
null</td>
<td>
美国</td>
</tr>
</tbody></table>
<p>
</p>
<p>
到此这篇关于sql的各种连接join案例详解的文章就介绍到这了,更多相关sql join内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！</p>
<p>
原文链接：https://www.cnblogs.com/reaptomorrow-flydream/p/8145610.html</p>
頁:
[1]