SELECT結果がない場合も必ずダミーレコードを返す

4807 ワード
SQL BigQuery SQL テキストリンク

経緯

フィルタ条件によって1レコードも結果が取得されない場合でも、なにかしらダミーのレコードを返す必要があったので考えてみました。
本来ならアプリケーション側でなんとかするべきかもしれません。

クエリ

対象データとダミーデータを完全外部結合しておいて、対象データの件数が0であればダミーデータ、そうでなければ対象データを取得します。
完全外部結合によって空行が出現してしまう場合があるので、最後に除外します。

with
  t as (
    -- テスト用対象データ
    select
      *
    from
      unnest(
        array<struct<col1 string, col2 string>>
        [
          -- レコードがない対象データをエミュレート
        ]
        -- [
        --   -- レコードがある場合をエミュレート
        --   ('real', 'data!')
        -- ]
      ) as col
  ),
  no_record_dummy as (
    -- tのレコードがなかった場合のダミーデータ
    select
      'some dummy' as col1,
      'data' as col2,
  ),
  at_least_one_row as (
    -- tのレコード件数
    select
      count(*) as row_count,
    from
      t
 )
select
  if(a.row_count = 0, d.col1, t.col1) as col1,
  if(a.row_count = 0, d.col2, t.col2) as col2,
from
  no_record_dummy d
  full outer join t using (
    col1,
    col2
  ),
  at_least_one_row a
where
  -- tのレコードがあった場合はfull outer joinによって空行が出現してしまうので除外
  if(a.row_count = 0, d.col1, t.col1) is not null

取得結果

対象レコードがない場合

対象レコードがある場合