データ無し時は空白行にしたい【SQLITE】

質問

こんにちは
日時とその時の人数データを格納しているテーブルがあり
日付をキーに0時から23時までのデータを獲得するのですが
データが存在しない時刻があり、
その時刻はブランク(NULL)で表示したいです。
外部結合とやらで実現できそうと思い、

＜テーブル構成＞
人数データテーブル(CNT_DATA)
日時、人数
ブランク行用テーブル(BLANK_DATA)
時(00～23)

SELECT B.時,C.人数 FROM BLANK_DATA B LEFT OUTER JOIN CNT_DATA C
ON B.時 = strftime('%H',C.日時+2415018.5)
WHERE C.日時による範囲指定

とやったのですが、
CNT_DATAに存在する時刻行しか引っ張ってこず、
ブランク行ができません。

根本的に考え方が違う様に思えてきているのですが、
どなたか教えていただけないでしょうか？

回答No.1

anmochi · ベストアンサー

私はSQLITEは試しに触ったくらいで実際に開発業務などで使った事はないので劇的に的外れかもしれないが、普通のSQL的にはLEFT OUTER JOINする側（存在しない場合にNULLになる側）をWHERE句で条件指定したらいけないんじゃないかな。

SQLITEでサブクエリーが使えるかどうかは分からんけど
SELECT B.時,C.人数 FROM BLANK_DATA B LEFT OUTER JOIN (SELECT * FROM CNT_DATA WHERE 日時による範囲指定) C ON B.時=strftime('%H',C.日時+2415018.5)
という風に、日時指定で特定の日時だけを抽出したものをLEFT OUTER JOINしてやるとか。

＜別解＞
　C.日時による範囲指定が C.日時>='2009-01-01' and C.日時<='2009-01-31'などのように不等号で表す事ができるのならば、LEFT OUTER JOINのON句に「定数を先にして」記述する事で対応できる。LEFT OUTER JOIN的には。

SELECT B.時,C.人数 FROM BLANK_DATA B LEFT OUTER JOIN CNT_DATA C
ON B.時 = strftime('%H',C.日時+2415018.5) and '2009-01-01'<=C.日時 and '2009-01-31'>=C.日時

　これはLEFT OUTER JOINの仕様なのでSQLITEがどこまでそれにそっているかによる。また、この場合は範囲指定にBETWEENは使えない。LEFT OUTER JOINのON句に記述する条件は必ず等号、不等号の右側に（ここで言う）Cテーブルが来ないといけない。

＜よく分かる解説＞
質問者のSQLがLEFT OUTER JOIN的にどうマズいのかについて。
ID列があるテーブルBとID列とNAME列があるテーブルCがある。
[テーブルB]
|ID|
|01|
|04|
|09|

[テーブルC]
|ID|NAME|
|01|HOGE|
|09|FUGA|

SELECT B.ID, C.ID, C.NAME FROM B LEFT OUTER JOIN C ON B.ID=C.ID
結果はこうなる
|B.ID|C.ID|C.NAME|
| 01| 01|HOGE |
| 04|NULL|NULL |　←(1)
| 09| 09|FUGA |

(1)に注目すると、この行はC.ID列がNULLとなっている。CテーブルがないためにCテーブルから抽出される列は全てNULLになる行が返される訳だ。ここで、WHERE句にC.ID BETWEEN 00 AND 99とすると、C.ID列がNULLである(1)は抽出対象から外れてしまうよね。この話自体はLEFT OUTER JOINは関係なくって、NULLはBETWEEN 00 AND 99にはヒットしないだけの話。質問者のSQLで「WHERE C.日時による範囲指定」の「C.日時」がまさにこのNULLの状態な訳だ。なのでせっかくCからの列がNULLで抽出できた行がWHERE句によってヒットしなかったという訳。

お礼

ご丁寧な回答、大変わかり易い解説ありがとうございます。
おかげさまで、意図してた結果が求められました。
Where句の位置なんて「基本中の基本」ですね・・・　お恥ずかしい