0

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'
user15676
  • 123
  • 2
  • 10
  • Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) – philipxy Aug 10 '23 at 21:14

0 Answers0