SQLのテーブル結合条件はINも使える

たまたま、業務をこなしていた際、困った事態が発生し、苦肉の策を練っていたときに発見したものです。

テーブルの結合条件といえば、どんな式を描くでしょうか？それはだいたい以下のような式だと思います。

```sql:sql
SELECT * FROM TABLE1 t1 JOIN TABLE2 t2 ON t1.base_no = t2.base_no
```

このようにt1.base_noとt2.base_noが一致しているテーブルに対し、カラムを返す処理を行う、これがだいたい基本的な結合条件だと思います。

…ところが、次のような記述でもテーブル結合が可能だということがわかりました。

## IN演算子でテーブルを結合
実はこの結合条件は、IN演算子にも対応しているようです。IN演算子といえば、通常はWHERE句の後に、いずれかの番号やコードを満たす条件を抽出するときに記述するもので、以下のように用いるのがセオリーです。

```sql:sql
SELECT * FROM TABLE3 WHERE product_no IN(1,3,6)
```

このようにproduct_noの値が1,3,6のものが抽出されます。ところが**結合条件のON句にもIN演算子を用いることができる**ようで、次のようなケースで重宝します。

## 実践例
サンプルの要件定義は以下のようになっています(業務上の要件とは違います)。

- テーブルには**ST_MEMBER**という静的トランザクションテーブルと**TRN_VOTE**という動的トランザクションテーブルがある（厳密にはMST_TEAMというチームマスタも存在するが、ここでは取り扱わない）。
- TRN_VOTEは個人一人あたり3人まで人気投票を行うためのメンバー番号を格納する（全部選択しなくてもよい。またMST_USERという投票参加者マスタも存在するが、ここでは取り扱わない）。
- ST_MEMBERはチーム番号とメンバー番号を格納する。

**ST_MEMBER(メンバーテーブル)**

| team_no | member_no | member_nm |
|:-:|:-:|:-:|
| 1  | 11  | 花巻  |
| 1  | 12  | 小野川  |
| 1  | 13  | 飯坂  |
| 2  | 21  | 月岡  |
| 2  | 22  | 山中  |
| 2  | 23  | 熱海  |
| 3  |  31 | 榊原  |
| 3  |  32 | 有馬  |
| 3  |  33 | 勝浦  |

**TRN_VOTE(投票テーブル。favは投票順位)**

| user_no | fav1 | fav2 | fav3 |
|:-:|:-:|:-:|:-:|
| 1  | 11  | 21  |  31 |
| 2  | 22  | 13  |   |
| 3  | 13  |   |   |

さて、ここでチームごとに人気投票に選ばれたメンバーを取得したい場合、次のような結合を用いると不都合が生じます。

```sql:sql
SELECT
 v.user_no as user_no,
 m.member_nm as member_nm
FROM
 TRN_VOTE v
JOIN
 ST_MEMBER m
ON
 m.member_no = v.fav1
ORDER BY 
 v.user_no
```

これだと検索結果は以下のようになり、参加者による1位だけのメンバーしか取得できません。

| user_no | member_nm |
|:-:|:-:|
| 1  | 花巻  |
| 2  | 山中  |
| 3  | 飯坂  |

そこで、主題のようにIN演算子を結合条件に用います。

```sql:sql
SELECT
 v.user_no as user_no,
 m.member_nm as member_nm
FROM
 TRN_VOTE v
JOIN
 ST_MEMBER m
ON
 m.member_no  IN(v.fav1,v.fav2,v.fav3)
ORDER BY 
 v.user_no
```

検索結果は以下のようになり、参加者による人気投票で選出されたメンバー全部を取得することができます。

| user_no | member_nm |
|:-:|:-:|
| 1  | 花巻  |
| 1  | 月岡  |
| 1  | 榊原  |
| 2  | 飯坂  |
| 2  | 山中  |
| 3  | 飯坂  |

# 応用

## グループごとの人数を取得する
結合条件をteam_noで集約し、カウント条件をmember_noにすることで、グループごとの選出人数を取得することができます。DISTINCT関数を用いることで、メンバーの重複をなくしています。

```sql:sql
SELECT
 m.team_no as team_no, 
 COUNT(DISTINCT(m.member_no)) as member_cnt
FROM
 TRN_VOTE v
JOIN
 ST_MEMBER m
ON
 m.member_no IN(v.fav1,v.fav2,v.fav3)
GROUP BY 
 m.team_no
ORDER BY 
 member_cnt desc
```

このように重複なしで表示されます。チーム1は11と13、チーム2は21と22、チーム3は31がエントリーされたので、以下の結果となります。

| team_no | member_cnt |
|:-:|:-:|
| 1  | 2  |
| 2  | 2  |
| 3  | 1  |

## 得票数を取得する
結合条件のmember_noで集約すると、メンバーごとの得票数を取得することもできます。

```sql:sql
SELECT
 COUNT(m.member_no) as cnt,
 m.member_nm as member_nm
FROM
 TRN_VOTE v
JOIN
 ST_MEMBER m
ON
 m.member_no IN(v.fav1,v.fav2,v.fav3)
GROUP BY 
 m.member_no
ORDER BY 
 cnt desc
```

このようにCOUNT関数を用いることで、得票の多い順に並べることもできます。

| cnt | member_nm |
|:-:|:-:|
| 2  | 飯坂  |
| 1  | 榊原 |
| 1  | 花巻  |
| 1  | 月岡  |
| 1  | 山中  |

このように結合条件にIN演算子を用いることで、個別のマスタ情報に対し、複数が関与する処理用トランザクションを作成し、その紐付け情報を取得したい場合に役立ちます。

## 得票数（ゼロカウント有）を取得する
ゼロカウントの得票数（得票数なし）も取得するには少しテクニックが必要（そのまま外部結合しようとしても結合条件が特殊なので、うまくいきません）で、**自己結合**と**外部結合**を用います。

```sql:sql
SELECT
 COUNT(m1.member_no) as cnt,
 m2.member_nm as member_nm
FROM
 TRN_VOTE v
JOIN
 ST_MEMBER m1
ON
 m1.member_no IN(v.fav1,v.fav2,v.fav3) 
RIGHT JOIN
 ST_MEMBER m2
ON
 m1.team_no = m2.team_no and m1.member_no = m2.member_no
GROUP BY 
 m2.member_no
ORDER BY 
 cnt desc,m2.team_no,m2.member_no
```

このように片方のメンバーテーブル(m1)だけ投票テーブルに紐付けておいて、もう一つのメンバーテーブル(m2）を**右外部結合**にしておきます。そうすることで、`得票数に紐付いているメンバー情報だけを取得している`メンバーテーブル（m1）と`全メンバーを取得している`メンバーテーブル（m2）が紐付き、得票数なしのメンバーもカウントされることになります。

| cnt | member_nm |
|:-:|:-:|
| 2  | 飯坂  |
| 1  | 榊原 |
| 1  | 月岡  |
| 1  | 山中  |
| 1  | 榊原  |
| 0  | 小野川  |
| 0  | 熱海  |
| 0  | 有馬  |
| 0  | 勝浦  |

## 原理
複数の結合条件はANDだけを用いるものと錯覚しがちですが、実は**OR演算子も結合条件に利用できる**ことは公式マニュアルに記載があります。IN演算子はこのOR演算子の一種なので、同じように応用できるということです。なので、得票数を取得したい場合は、以下のようなSQL文も同じ結果を返します。

```sql:sql
select 
 count(*) as cnt,
 t.member_nm as member_nm
FROM
 trn_vote v
JOIN
 mst_team t
on
 t.member_no = v.fav1 OR t.member_no = v.fav2 OR t.member_no = v.fav3
group by 
 t.member_no
order by 
 cnt desc
```

ちなみに等価演算子だけでなく、不等価演算子も結合条件に用いることができます。日付や年齢、記録などで振り分けて紐づけたい場合に活用します。