CASE文をあるテーブルを参照しながら使用する方法とその注意点

導入

この記事はCASE文の条件をあるテーブルから引っ張ってきた際に陥ったミスについて記載しています。
同じミスに陥る人が多そうだと感じまとめました。
誰かの参考になれば幸いです。

環境

Bigquery

事象

「CASE文の中身が大量になってしまい、SQLの運用が難しくなっている。」
上記の問題を解決するために、CASE文の条件に必要なデータをスプレッドシートにまとめて、
それをBigquery上にインポートしてそこで作成されたテーブルをCASE文にしようするというの中で問題が発生しました。

問題

以下のテーブル(test)のb列の情報を使用してCASE文を作成
test

a	b
1	xxxxxx
2	yyyyyy
3	zzzzzz

sample

id	name
a1	xxxxxx
a2	yyyyyy
a3	zzzzzz

CASE文

SELECT
  CASE 
    WHEN sample.name = test.b THEN test.a
    ELSE "-"
  END AS name
from sample, test

上記のようにSQLを作成したところ、以下の結果が取得できると想定していた。

name
1
2
3

しかし実際は以下の通りの結果になる。

name
1
-
-
2
-
-
3
-
-

テーブルからCASE文の条件を引っ張ってくるときは、条件になるテーブルすべてに対してCASE文が適用されるため注意しなければ想定していない結果が返ってくることがある。

対策

2つ対策はあり、

• ハードコーディング
• where句での絞り込み

である。

ハードコーディング

以下のようにCASE文を作成することで対応可能

SELECT
  CASE 
    WHEN sample.name = "xxxxxx" THEN "1"
    WHEN sample.name = "yyyyyy" THEN "2"
    WHEN sample.name = "zzzzzz" THEN "3"
    ELSE "-"
  END AS name
from sample, test

where句での絞り込み

WITH result AS (
  SELECT
    CASE 
      WHEN sample.name = "xxxxxx" THEN "1"
      WHEN sample.name = "yyyyyy" THEN "2"
      WHEN sample.name = "zzzzzz" THEN "3"
      ELSE "-"
    END AS name
  from sample, test
)
SELECT
  name
FROM result
WHERE name != "-"

サブクエリを組み合わせたりして欲しい結果をwhere句で絞り込みしてあげるのも対策の一つ