今回の課題
下記の2種類のテーブルを使って、
titleテーブルのtitle_name
に対応するseries_name
を抽出できるようにしたい。
(業務で行った処理を分かりやすいテーブルに書き換えて記載していく。)
【titleテーブル】
title_id | title_name |
---|---|
1 | トイ・ストーリー |
2 | トイ・ストーリー2 |
3 | トイ・ストーリー3 |
4 | バイオハザード |
5 | バイオハザード2 |
6 | バイオハザード3 |
7 | バイオハザード4 |
8 | バイオハザード5 |
9 | バイオハザード6 |
【seriesテーブル】
series_id | title_name | series_name |
---|---|---|
1 | トイ・ストーリー | トイ・ストーリー |
1 | トイ・ストーリー2 | トイ・ストーリー |
1 | トイ・ストーリー3 | トイ・ストーリー |
2 | バイオハザード | バイオハザード |
2 | バイオハザード2 | バイオハザード |
【欲しいデータ】
title_name | series_name |
---|---|
トイ・ストーリー | トイ・ストーリー |
トイ・ストーリー2 | トイ・ストーリー |
トイ・ストーリー3 | トイ・ストーリー |
バイオハザード | バイオハザード |
バイオハザード2 | バイオハザード |
バイオハザード3 | バイオハザード |
バイオハザード4 | バイオハザード |
バイオハザード5 | バイオハザード |
バイオハザード6 | バイオハザード |
失敗例
最初2つのテーブルを左外部結合や完全外部結合で結合していた。
select
t.title_name
, s.series_name
from
`title` as t
left join `series` as s
on t.title_name = s.title_name
出力結果
title_name | series_name |
---|---|
トイ・ストーリー | トイ・ストーリー |
トイ・ストーリー2 | トイ・ストーリー |
トイ・ストーリー3 | トイ・ストーリー |
バイオハザード | バイオハザード |
バイオハザード2 | バイオハザード |
バイオハザード3 | null |
バイオハザード4 | null |
バイオハザード5 | null |
バイオハザード6 | null |
このように、完全外部結合や左外部結合を行うとnull
が複数発生してしまうので使えない。
成功例
/* seriesをユニークな値に絞り込んでおく */
with series_mst as (
select
distinct series_name
from
`series`
)
/* series_mstとtitleテーブルをcross joinする&余計な組み合わせをWHERE句で除去する */
select
t.title_name
, s.series_name
from
`title` as t
cross join series_mst as s
where
t.title_name like "%"||s.series_name||"%" -- title_nameの中にseries_nameの文字列が入っているものの組み合わせに絞り込む(cross joinによってtitle×seriesのすべての組み合わせが生成されているから)
出力結果
title_name | series_name |
---|---|
トイ・ストーリー | トイ・ストーリー |
トイ・ストーリー2 | トイ・ストーリー |
トイ・ストーリー3 | トイ・ストーリー |
バイオハザード | バイオハザード |
バイオハザード2 | バイオハザード |
バイオハザード3 | バイオハザード |
バイオハザード4 | バイオハザード |
バイオハザード5 | バイオハザード |
バイオハザード6 | バイオハザード |
cross joinを使えば、外部結合の時のNULLを取り除いてデータを抽出できた。