SQLの基本（結合編）

# SQLの結合
SQLの結合というと「INNER JOIN」と「LEFT JOIN」ばかり使っていますが、「FULL JOIN」を使う機会があったのでEXISTSと合わせて図で表してみました。


<table>
<caption>table_a</caption>
<thead>
<tr><th>code</th><th>*</th></tr>
</thead>
<tr><td>101</td><td>…</td></tr>
<tr><td>102</td><td>…</td></tr>
<tr><td>103</td><td>…</td></tr>
<tr><td>201</td><td>…</td></tr>
<tr><td>202</td><td>…</td></tr>
<tr><td>203</td><td>…</td></tr>
</table>
　
<table>
<caption>table_b</caption>
<thead>
<tr><th>code</th><th>*</th></tr>
</thead>
<tr><td>201</td><td>…</td></tr>
<tr><td>202</td><td>…</td></tr>
<tr><td>203</td><td>…</td></tr>
<tr><td>204</td><td>…</td></tr>
<tr><td>205</td><td>…</td></tr>
<tr><td>206</td><td>…</td></tr>
</table>

# 内部結合
### INNER JOIN
```sql
SELECT * 
  FROM table_a
 INNER JOIN table_b
         ON table_b.code = table_a.code
```
![INNER_JOIN.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/f9e52d8f-aaa9-2184-131d-e6e55d6942d2.png)

<table>
<thead>
<tr><th>table_a.code</th><th>table_a.*</th><th>table_b.code</th><th>table_b.*</th></tr>
</thead>
<tr><td>201</td><td>…</td><td>201</td><td>…</td></tr>
<tr><td>202</td><td>…</td><td>202</td><td>…</td></tr>
<tr><td>203</td><td>…</td><td>203</td><td>…</td></tr>
</table>

# 外部結合
### LEFT JOIN
```sql
SELECT * 
  FROM table_a
  LEFT JOIN table_b
         ON table_b.code = table_a.code
```
![LEFT_JOIN.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/110b130b-fefa-1d6b-40e8-4bba64a176ee.png)

<table>
<thead>
<tr><th>table_a.code</th><th>table_a.*</th><th>table_b.code</th><th>table_b.*</th></tr>
</thead>
<tr><td>101</td><td>…</td><td>NULL</td><td>NULL</td></tr>
<tr><td>102</td><td>…</td><td>NULL</td><td>NULL</td></tr>
<tr><td>103</td><td>…</td><td>NULL</td><td>NULL</td></tr>
<tr><td>201</td><td>…</td><td>201</td><td>…</td></tr>
<tr><td>202</td><td>…</td><td>202</td><td>…</td></tr>
<tr><td>203</td><td>…</td><td>203</td><td>…</td></tr>
</table>

### RIGHT JOIN
```sql
SELECT * 
  FROM table_a
 RIGHT JOIN table_b
         ON table_b.code = table_a.code
```

![RIGHT_JOIN.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/8505acf8-ae29-3ac8-f958-7c80790bb322.png)


<table>
<thead>
<tr><th>table_a.code</th><th>table_a.*</th><th>table_b.code</th><th>table_b.*</th></tr>
</thead>
<tr><td>201</td><td>…</td><td>201</td><td>…</td></tr>
<tr><td>202</td><td>…</td><td>202</td><td>…</td></tr>
<tr><td>203</td><td>…</td><td>203</td><td>…</td></tr>
<tr><td>NULL</td><td>NULL</td><td>204</td><td>…</td></tr>
<tr><td>NULL</td><td>NULL</td><td>205</td><td>…</td></tr>
<tr><td>NULL</td><td>NULL</td><td>206</td><td>…</td></tr>
</table>

### FULL JOIN
```sql
SELECT * 
  FROM table_a
  FULL JOIN table_b
         ON table_b.code = table_a.code
```
![FULL_JOIN.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/8aabdfd7-1064-95a9-6e14-5a25d6b482db.png)

<table>
<thead>
<tr><th>table_a.code</th><th>table_a.*</th><th>table_b.code</th><th>table_b.*</th></tr>
</thead>
<tr><td>101</td><td>…</td><td>NULL</td><td>NULL</td></tr>
<tr><td>102</td><td>…</td><td>NULL</td><td>NULL</td></tr>
<tr><td>103</td><td>…</td><td>NULL</td><td>NULL</td></tr>
<tr><td>201</td><td>…</td><td>201</td><td>…</td></tr>
<tr><td>202</td><td>…</td><td>202</td><td>…</td></tr>
<tr><td>203</td><td>…</td><td>203</td><td>…</td></tr>
<tr><td>NULL</td><td>NULL</td><td>204</td><td>…</td></tr>
<tr><td>NULL</td><td>NULL</td><td>205</td><td>…</td></tr>
<tr><td>NULL</td><td>NULL</td><td>206</td><td>…</td></tr>
</table>

# 存在チェック
### EXISTS
```sql
SELECT * 
  FROM table_a
 WHERE EXISTS(SELECT 1 FROM table_b WHERE table_b.code = table_a.code)
```
![EXISTS.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/d676e7e6-ce8b-a9c4-3702-2aa54790a388.png)

<table>
<thead>
<tr><th>table_a.code</th><th>table_a.*</th></tr>
</thead>
<tr><td>201</td><td>…</td></tr>
<tr><td>202</td><td>…</td></tr>
<tr><td>203</td><td>…</td></tr>
</table>

### NOT EXISTS
```sql
SELECT * 
  FROM table_a
 WHERE NOT EXISTS(SELECT 1 FROM table_b WHERE table_b.code = table_a.code)
```
![NOT_EXISTS.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3779420/3ecfdc4e-00ed-2160-62ba-ac046cb77f2d.png)

<table>
<thead>
<tr><th>table_a.code</th><th>table_a.*</th></tr>
</thead>
<tr><td>101</td><td>…</td></tr>
<tr><td>102</td><td>…</td></tr>
<tr><td>103</td><td>…</td></tr>
</table>