postgresql - 此查询如何填充数据？

Question

据我了解，当此查询运行时，由于 where 子句，它不会在运行任何次数时填充任何数据

where c.company_id = lot.company_id
  and p.product_id = lot.product_id 
  and l.packlevel_id = lot.packlevel_id

在我看来，一开始当表fact_table_lot为空时，where 子句将返回空数据，因为它不会在空表中找到任何内容，并且每次都会发生。我的理解错了吗？

insert into fact_table_lot(company_id, product_id, packlevel_id, l_num, sn_count, comm_loct, comm_start, commdate_end, man_date, exp_date, user_id, created_datetime)
select c.company_id, p.product_id, l.packlevel_id, l_num, sn_count, comm_loct, comm_start, commdate_end, man_date, exp_date, user_id, sysdate
from staging_serials s 
  left outer join fact_table_lot lot on s.lotnumber = lot.l_num
  join company c on c.lsc_company_id = s.companyid
  join product p on s.compositeprodcode = p.compositeprodcode
  join level l on l.unit_of_measure = p.packaginguom
where c.company_id = lot.company_id
  and p.product_id = lot.product_id 
  and l.packlevel_id = lot.packlevel_id
  and lot.created_datetime is null

标签： postgresqlamazon-redshift

Answer 1

在您的查询staging_serials s left outer join fact_table_lot lot on s.lotnumber= lot.l_num中，这将提供包含来自事实表的所有记录的结果集staging_serials，因为事实表对于事实表中的那些列是空的空值。如果您不希望返回任何记录，请使用 ainner join而不是left join.