今回の課題

下記の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	バイオハザード

失敗例

最初２つのテーブルを左外部結合や完全外部結合で結合していた。

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を取り除いてデータを抽出できた。