I'm trying to retrieve all rows from left table using a left join but only matched records are returned when adding a where clause.
When there is no where clause then all rows from items table is returned, with the following script:
select *
from items i
left join orders o
on i.item_id = o.item_id
and i.country_code = 'AUS'
but when I tried to include a where clasue only matched records are returned, see below script:
select *
from items i
left join orders o
on i.item_id = o.item_id
and i.country_code = 'AUS'
where o.customer_id = '123456'
I've also tried to select into a temp table first and then do the select with where clause but only match records are returned, see below script:
with itemordered as (
select i.*, o.customer_id, o.date
from items i
left join orders o
on i.item_id = o.item_id
and i.country_code = 'AUS'
)
select * from itemordered
where customer_id = '123456'