CODEWITHSUNDEEP
sqloracle11gleft-join
zundarz
zundarz

Reputation: 1594

How to keep null records in this left join?

Even though there is no record in fruit_batch_info, I do want AP01 in my results, so I'm using a left join.

However as soon as I add 'where fruit_batch_info=11' then my AP01 no longer appears in my results.

How do I keep AP01 in my results?

Requirements: Report all records in 'all_fruits' plus any information about 'fruit_batch=11'

Desired Results

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

Here's my query:

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'

Upvotes: 2

Views: 3232

Answers (1)

isaace
isaace

Reputation: 3429

When using LEFT JOIN add the condition from that table to the join ON clause instead of the WHERE clause:

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

Upvotes: 8

Related Questions