たまたま、業務をこなしていた際、困った事態が発生し、苦肉の策を練っていたときに発見したものです。
テーブルの結合条件といえば、どんな式を描くでしょうか？それはだいたい以下のような式だと思います。
SELECT * FROM TABLE1 t1 JOIN TABLE2 t2 ON t1.base_no = t2.base_no
このようにt1.base_noとt2.base_noが一致しているテーブルに対し、カラムを返す処理を行う、これがだいたい基本的な結合条件だと思います。
…ところが、次のような記述でもテーブル結合が可能だということがわかりました。
IN演算子でテーブルを結合
実はこの結合条件は、IN演算子にも対応しているようです。IN演算子といえば、通常はWHERE句の後に、いずれかの番号やコードを満たす条件を抽出するときに記述するもので、以下のように用いるのがセオリーです。
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 |
さて、ここでチームごとに人気投票に選ばれたメンバーを取得したい場合、次のような結合を用いると不都合が生じます。
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演算子を結合条件に用います。
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関数を用いることで、メンバーの重複をなくしています。
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で集約すると、メンバーごとの得票数を取得することもできます。
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演算子を用いることで、個別のマスタ情報に対し、複数が関与する処理用トランザクションを作成し、その紐付け情報を取得したい場合に役立ちます。
得票数（ゼロカウント有）を取得する
ゼロカウントの得票数（得票数なし）も取得するには少しテクニックが必要（そのまま外部結合しようとしても結合条件が特殊なので、うまくいきません）で、自己結合と外部結合を用います。
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文も同じ結果を返します。
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
ちなみに等価演算子だけでなく、不等価演算子も結合条件に用いることができます。日付や年齢、記録などで振り分けて紐づけたい場合に活用します。