[PostgreSQL][9.6.x]VIEW生成用SQLとVIEWのレコード数が相違する

Question

現象

あるテーブルAに複数のテーブル（B,C,D）をleft joinしてviewを作っているのですが、
view生成用SQLを実行すると結果として4レコード返ってくる（想定通り）のに対して、
同じSQLで作成したviewを参照すると2レコードになってくる現象が起きており困っています。

識者の方、この現象に対して心当たりがありましたら御教授をお願いいたします:bow_tone1:

view生成用SQL概要

    select ...(略)
      from table_a as a
 left join table_b as b
        on a.b_id = b.id
       and b.deleted = false
 left join table_c as c
        on a.c_id = c.id
       and c.deleted = false
 left join table_d as d
        on a.d_id = d.id
       and d.deleted = false
;

• このSQLを直接実行：4件
• このSQLのVIEW参照：2件 ←？？？

別記事項

left joinで参照する先のテーブルにはon句で一致するデータがない場合があります。
また、on句の中にandでdeletedというフラグがfalseであることを指定しています。

私としては（普通に？）、条件合致しないカラムはnullで結合されてくることを期待しているのですが…。

コンソール（件数確認）

m

test-> ;
 count
-------
     4
(1 行)


test=> select count(*) my_view;
 count
-------
     2
(1 行)

version:9.6.x

補足（解決）情報

以下のようにleft join箇所で副問い合わせを行うようにして、
そこにonで結合を行うことで希望のレコードも抽出できました。

直接left join onで記載すると、主レコードの結合キーがnullの場合などに抽出が利かなくなるようです。

訂正依頼＆御回答いただいた皆様、ありがとうございました。

    select ...(略),
           b.column1,
           c.column1,
           d.column1
      from table_a as a
 left join (
    select table_b.id,
           table_b.column1,
           ...
      from table_b
     where deleted = false
           ) b
        on a.b_id = b.id
 left join (
    select table_c.id,
           table_c.column1,
           ...
      from table_c
     where deleted = false
           ) c
        on a.c_id = c.id
 left join (
    select table_d.id,
           table_d.column1,
           ...
      from table_d
     where deleted = false
           ) d
        on a.d_id = d.id
;

Comments

[sazi]

提示されている情報以外のところに問題がありそうです。再現可能となる情報を追加してください。

[sllmejacob]

saziさん コメントいただきありがとうございます。クエリを見直して解決できました。質問を改修して現象を再現できるようにしておきたいと思います。ありがとうございました。

Answer 1

解せないので、検証しましたが、やはりそのような事象は発生しませんでした。
構築

create table table_a(id int not null, b_id int, item text, CONSTRAINT table_a_key PRIMARY KEY (id));
insert into table_a values (1,1,'item1'),(2,2,'item2'),(3,3,'item3'),(4,Null,'item4');
create table table_b(id int not null, item text, deleted boolean, CONSTRAINT table_b_key PRIMARY KEY (id));
insert into table_b values (1,'item1',false),(2,'item2',true),(3,'item2',null);

create view view_test as (
select a.*,b.item as b_item from table_a a left join table_b b on a.b_id=b.id and b.deleted=false
);

検証

table_a

id	b_id	item
1	1	item1
2	2	item2
3	3	item3
4	Null	item4

table_b

id	item	deleted
1	item1	False
2	item2	True
3	item3	Null
```SQL
select * from view_test
```
結果
---
id	b_id	item
:--:	:--:	:--:
1	1	item1
2	2	item2
3	3	item3
4	Null	item4

Comments

[sllmejacob]

saziさん

ありがとうございます。いただいたSQLでローカル確認してみましたが、期待通りになりますね…。
私としても元のクエリの方が単純で良いと思っていますので、
本質問はクローズにしてしまいましたが、もう少し調査してみます。

結果はこちらに記載させていただきます。
わざわざ追伸ありがとうございます。

Answer 2

こちらについて追伸をしそびれていましたが、結局sazi様に記載していただいた通り、
元のSQLでも問題はなかったようです。

なぜかPCを再起動したりしたところ、元のSQLでも問題なく結果が出せるようになりました…。
大変お騒がせいたしました。

Answer 3

結合元テーブルの結合キーがnullの場合には副問い合わせを行ってレコードを作らないといけないようです。
※この認識が正しいか、正確な知識が足りないため若干怪しいと思っているのですが…。

補足（解決）情報欄を質問箇所に追記いたしました。
皆様、ありがとうございました。

Answer 4

この書き込みを読んでいる人が現象を再現できるだけの必要最小限の情報(テーブル定義やテーブル中に存在するデータ)を提示してください。

Comments

[sllmejacob]

Orlofskyさん

再現情報としてはサブクエリを使わないとon句のキーが紐づかなかったためのようです。
質問を編集して意図が理解できるようにしてクローズします。

御回答ありがとうございました。

[Orlofsky]

質問にはありませんが、ちょっと気になったので。
where deleted = false のためだけにインランビューにしてますが、通常は deleted = false で必要なデータを大幅に絞り込める用途以外はインランビューにはしません。on の次に where を記述することが多いです。保守性を高めるためにSQLを無意味に複雑にしないことは大切です。
https://qiita.com/Papageno/items/ab87e8a0cde8e514ab3f

[sllmejacob]

Orlofskyさん

whereの位置についてですが、今回のケースですと複数のサブクエリ下で将来的にそれなりの数（のdeleted=true）が出る予測があります。
ご指摘を受けて改めて検討してみましたが、大幅に絞り込める用途としては合致しているのかなと考えました。

よって一旦はこのままとしたく思っています。追伸のご指摘ありがとうございました。
また何かの時に質問させていただくかもしれません。よろしくお願いいたします。