Can predicates in the WHERE clause affect the type of join
-
Hello In the query below, since I am using a LEFT JOIN I would expect all rows from podetm to be returned. This is true as long as the line that is commented out is not included. As soon as I include a predicate from the joined table, I seem to get only rows that exist in stockm in the result set. Why?
select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
--and s.analysis_a not in ( 'LNG', 'INFANTRUST') -
Hello In the query below, since I am using a LEFT JOIN I would expect all rows from podetm to be returned. This is true as long as the line that is commented out is not included. As soon as I include a predicate from the joined table, I seem to get only rows that exist in stockm in the result set. Why?
select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
--and s.analysis_a not in ( 'LNG', 'INFANTRUST')Yes, you're effectively turning it into a normal join in this case. Think about it, when you add the line
and s.analysis_a not in ( 'LNG', 'INFANTRUST')you're telling it to give you all records where s.analysis_a does not contain 'LNG' or 'INFANTRUST'. You didn't tell it to give you records where s.analysis_a is null. So try this:select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
and (s.analysis_a not in ( 'LNG', 'INFANTRUST') OR s.analysis_a IS NULL)Wrong is evil and must be defeated. - Jeff Ello[^]
-
Yes, you're effectively turning it into a normal join in this case. Think about it, when you add the line
and s.analysis_a not in ( 'LNG', 'INFANTRUST')you're telling it to give you all records where s.analysis_a does not contain 'LNG' or 'INFANTRUST'. You didn't tell it to give you records where s.analysis_a is null. So try this:select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
and (s.analysis_a not in ( 'LNG', 'INFANTRUST') OR s.analysis_a IS NULL)Wrong is evil and must be defeated. - Jeff Ello[^]
Looks like he did to me.
-
Looks like he did to me.
:confused:
Wrong is evil and must be defeated. - Jeff Ello[^]
-
Yes, you're effectively turning it into a normal join in this case. Think about it, when you add the line
and s.analysis_a not in ( 'LNG', 'INFANTRUST')you're telling it to give you all records where s.analysis_a does not contain 'LNG' or 'INFANTRUST'. You didn't tell it to give you records where s.analysis_a is null. So try this:select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
and (s.analysis_a not in ( 'LNG', 'INFANTRUST') OR s.analysis_a IS NULL)Wrong is evil and must be defeated. - Jeff Ello[^]
Jorgen, as usual... Thanks for the clear explanation :) Getting all the required rows after following your suggestion.
-
:confused:
Wrong is evil and must be defeated. - Jeff Ello[^]
:doh: I was looking at yours. :sigh:
-
Hello In the query below, since I am using a LEFT JOIN I would expect all rows from podetm to be returned. This is true as long as the line that is commented out is not included. As soon as I include a predicate from the joined table, I seem to get only rows that exist in stockm in the result set. Why?
select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
--and s.analysis_a not in ( 'LNG', 'INFANTRUST')If you want to include that condition then include that in the Join like left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product and s.analysis_a not in ( 'LNG', 'INFANTRUST') Else you will face that issue
-
Hello In the query below, since I am using a LEFT JOIN I would expect all rows from podetm to be returned. This is true as long as the line that is commented out is not included. As soon as I include a predicate from the joined table, I seem to get only rows that exist in stockm in the result set. Why?
select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
--and s.analysis_a not in ( 'LNG', 'INFANTRUST')try it: select * from scheme.podetm d left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product where d.qty_received >0 and d.inv_value_posted =0 and (s.analysis_a not in ('LNG','INFANREUST') or s.analysis_a is null)