sql - 如何在此左连接中保留空记录？

Question

虽然 fruit_batch_info 中没有记录，但我确实想要 AP01结果，所以我使用左连接。

但是，一旦我添加“where fruit_batch_info=11”，我的 AP01 就不再出现在我的结果中。

如何在我的结果中保留 AP01？

要求:报告“all_fruits”中的所有记录以及有关“fruit_batch=11”的任何信息

期望的结果

ID  , DESC   , BATCH, STORAGE 
----- -------- ------ ------------
APO1, Apple  , null , null  
PE01, Pear   , 11   , Warehouse-11
KU01, Kumquat, 11   , Warehouse-11

这是我的查询:

with all_fruits as
(select 'AP01' as fruit_id, 'Apple' as fruit_desc from dual union all
 select 'PE01' as fruit_id, 'Pear' as fruit_desc from dual union all
 select 'KU01' as fruit_id, 'Kumquat' as fruit_desc from dual),
 fruit_batch_info as
 (select 'PE01' as fruit_id, '10' as fruit_batch , 'Warehouse-10' as fruit_storage from dual union all
  select 'PE01' as fruit_id, '11' as fruit_batch , 'Warehouse-11' as fruit_storage from dual union all
  select 'PE01' as fruit_id, '12' as fruit_batch , 'Warehouse-12' as fruit_storage from dual union all
  select 'KU01' as fruit_id, '10' as fruit_batch , 'Warehouse-10' as fruit_storage from dual union all
  select 'KU01' as fruit_id, '11' as fruit_batch , 'Warehouse-10' as fruit_storage from dual)

 select a.fruit_id  , 
        fruit_desc, 
        fruit_batch,
        fruit_storage
 from all_fruits  a 
 left join fruit_batch_info i  on a.fruit_id = i.fruit_id
 where i.fruit_batch='11' 

Answer 1

当使用 LEFT JOIN 时，将该表的条件添加到连接 ON 子句而不是 WHERE 子句:

left join fruit_batch_info i  on a.fruit_id = i.fruit_id and i.fruit_batch='11'