外部結合でのcount結果でゼロを表示

質問

以下の様なテーブルがあります。
テーブルを結合してカウントを行っているのですが、
tbl1の商品コードに存在しないユーザが存在しない場合や
tbl3のフラグが”0”または”1”が存在しない場合、
ゼロ件と出したいのですが出力されません。
どうすれば期待した結果になるか教えて下さい。

■tbl1
ID SYOHIN_CODE
---- -----------
1 A01
2 B01
3 C01
4 D01
5 E01

■tbl2
ID NUM SYOHIN_ID
---- ------- ---------
1 NUM001 1
2 NUM002 2
3 NUM003 3
4 NUM004 4
5 NUM005 1
6 NUM006 1
7 NUM007 2
8 NUM008 3
9 NUM009 4
10 NUM010 1

■tbl3
ID NUM_ID FLG
---- -------- ----
1 1 1
2 2 1
3 3 1
4 4 0
5 5 0
6 6 0
7 7 0
8 8 1
9 9 0
10 10 0

■実行した結果
SYOHIN_CODE FLG CNT
----------- ---- ---------------
A01 0 3
A01 1 1
B01 0 1
B01 1 1
C01 1 2
D01 0 2

■期待する結果
SYOHIN_CODE FLG CNT
----------- ---- ---------------
A01 0 3
A01 1 1
B01 0 1
B01 1 1
C01 0 0
C01 1 2
D01 0 2
D01 1 0
E01 0 0
E01 1 0

実行したSQL
select
T1.syohin_code,T3.flg,count(T2.num) cnt
from tbl1 T1
inner join tbl2 T2 on T1.id=T2.syohin_id
inner join tbl3 T3 on T2.id=T3.num_id
group by T1.syohin_code,T3.flg
order by T1.syohin_code
;

回答No.1

anmochi · ベストアンサー

Oracleに限らずSQLでデータを操作する型のDBMSで外部結合の結果外部されたレコードが無いとそのフィールドはNULLになる事は知っていると思う。
そういう場合にはNULLになる可能性があるフィールドにNVL関数やCOALESCE関数をかませる。

と思ったんだけど、それ以前に提示されたSQL文がそもそも外部結合じゃないがどこで使うのだい。
商品一覧はともかくフラグの0、1とそれ以外という組み合わせを作るには
商品一覧とフラグ0、フラグ1の組み合わせの完全な一覧を作る→それに対してtbl3を外部結合してカウントを取る→必要に応じてインラインサブクエリーやNVLを使う
という流れになるだろうな。
あるいは、普通にSQLでデータを取得して（つまり、SQLでは質問文中の■実行した結果を得て）結果を画面や帳票に返す時に足りないところをプログラムで補うかしかないのでは（Oracleに限らずDBMSでは「存在しないレコードも含む完全な一覧を作る」というのは難しい）。外部結合（OUTER JOIN）の他に交差結合（CROSS JOIN）というのがあるが、これは普通使わない、と思う。
理由は、tbl2とtbl3をクロス集計してもSYOHIN_CODE（の元ネタとなるSYOHIN_ID）とFLGの組み合わせが
SYOHIN_CODE FLG
----------- ----
A01 0
A01 1
B01 0
B01 1
C01 0
C01 1
D01 0
D01 1
E01 0
E01 1
になるとは限らないから。なので、商品一覧に対してフラグが0である一覧とフラグが1である一覧をUNIONしたものを元ネタにするのが良いと思う。

(select id, 0 as flg from tbl1 union all select id, 1 as flg from tbl1) tbl1cc -- (A)

これが商品とフラグの完全な網羅パターンだ。まずはこれであなたが望む完全なリストが出てくることを確認しよう。この段階では商品コードではなく商品IDとフラグの組み合わせになる。

select * from
(select id, 0 as flg from tbl1 union all select id, 1 as flg from tbl1) tbl1cc -- (A)
order by id asc, flg asc

次に、idに名前（syohin_code）を寄せる。これはtbl1completecollectionが元々完全な商品一覧なので内部結合でも外部結合でもいい。内部結合の方が実行計画的にいいかも知れん。

select tbl1.syohin_code, tbl1completecolletion.flg from
(select id, 0 as flg from tbl1 union all select id, 1 as flg from tbl1) tbl1cc -- (A)
inner join tbl1 on tbl1completecollection.id=tbl1.id
order by tbl1.syohin_code asc, tbl1completecolletion.flg asc

なんなら(A)で直接syohin_codeを取ってきてもいいだろう。(A)'を作ってみよう。

(select id, syohin_code, 0 as flg from tbl1 union all select id, syohin_code, 1 as flg from tbl1) tbl1cc -- (A)'

これ以降の説明では(A)'を使う。さらに、質問文に合わせて(A)'の別名をT1とする。
tbl1の変わりに(A)'を基本テーブルとして使うと、tbl2とtbl3を外部結合していくことで完全な一覧とNULL行を得る事ができる。OracleのcountはNULLを数えないので、最終的に商品コード（商品ID）とフラグが両方一致しないとNULLとなるT3テーブルの何かを用いて商品とフラグごとにカウントを取ればいい。

select
T1.syohin_code, T1.flg, count(T3.flg) cnt
from
(select id, syohin_code, 0 as flg from tbl1 union all select id, syohin_code, 1 as flg from tbl1) T1
left outer join tbl2 T2 on T1.id=T2.syohin_id
left outer join tbl3 T3 on T2.id=T3.num_id and T1.flg=T3.flg
group by T1.syohin_code, T1.flg
order by T1.syohin_code asc, T1.flg asc

これを実行するとお望みの結果が得られるはずだ。
NVLとか全然関係ない解説（というか解説を書いていったらNVLはそもそも関係なかった）になったけど、まずは自分が得たい結果のサブセットを考えて少しずつSQLに肉付けしていくといいだろう。

お礼

ありがとうございます。
説明、解説まで記載していただき解りやすいです。