Oracle SQLでunion all で横持ちデータを縦持ちにするときの注意点

前提

Oracleのデータベースを使用。

問題

union allでデータを縦持ちにしようとしたときに、
全件検索では問題なかったがwhere句をつけて思った通りの結果が返ってこなかった。
例えば、以下のようなテーブル「好きな果物TOP3」があるとする。

id	fruit1	fruit2	fruit3
1	りんご	すいか	なし
2	ばなな	ぶどう	もも

このデータをidが1のデータのみ抜き出して以下のように縦持ちにしたい。
id=1の好きな果物TOP3として見やすくなる。

ranking	fruit
1	りんご
2	すいか
3	なし

やりかた

まずは全件検索を考えてみると以下のようなクエリになる。
ここではidが1と2のデータが両方検索される。

select id, fruit1 from 好きな果物TOP3 union all
select id, fruit2 from 好きな果物TOP3 union all
select id, fruit3 from 好きな果物TOP3;

続いて、id=1のみに絞る場合だが、以下のやり方だと失敗する。
私もこのやり方を間違えた。

select id, fruit1 from 好きな果物TOP3 union all
select id, fruit2 from 好きな果物TOP3 union all
select id, fruit3 from 好きな果物TOP3
where id = 1
order by id;

※便宜上order by idをつけているが、id=1のデータしか出さないなら本来意味はない。

この時表示されるのは、以下のようなデータになる。

id	fruit
1	りんご
1	すいか
1	なし
2	ばなな
2	ぶどう

これは、where句が効いているのが最後のfruit3のみになっていて、ほかのfruit1,2では全件を表示しているためである。
そのため、お題のような結果を表示したい場合は以下のような2通りのクエリにする。

select '1' as ranking, fruit1 as fruit from 好きな果物TOP3
    where id = 1 union all
select '2', fruit2 from 好きな果物TOP3
    where id = 1 union all
select '3', fruit3 from 好きな果物TOP3
    where id = 1
order by ranking;

select * from (
    select id, '1' as ranking, fruit1 as fruit from 好きな果物TOP3 union all
    select id, '2', fruit2 from 好きな果物TOP3 union all
    select id, '3', fruit3 from 好きな果物TOP3
) sub
where sub.id = 1
order by sub.ranking;

まず、1つ目のクエリはそれぞれ結合前にwhere句でid=1のデータだけに絞ってから結合している。
2つ目のクエリについては、一度サブクエリ内でテーブルの全件を結合した後にwhere句が来ている。
どちらでも同じ結果が表示される。
また、見栄えの問題だが最初の結合するselectの列名で表示されるので列別名をつけておくと表示がきれいになる。

ちなみに、Oracleの場合はsubという別名の前にasをつけることができない。
PostgreSQLだとasはつけてもつけなくてもいいが、エイリアスをつけるのは必須らしい。
あと、今回みたいな単純なクエリの場合はsubがなくても問題はない(Chat-GPT先生もそう言っているので信じたい)

おまけ：どっちが早い？

Chat-GPT先生に聞いてみた。
多くの場合で差は出ないらしい。
最後にwhereをつける場合でも各selectのところにwhereを入れられるので結果として、正しいクエリ1つ目と同じように動くらしい。(オプティマイザの述語プッシュダウン??)
あと、縦持ちにするやり方としてUNPIVOTが正攻法だそうです。