Version 10.2.0.4.0
I have a question on the expected behavior of the query below.
When I run the query below with the constraint on t1.partid = 789, I get the query result t2.Indicator showing "SPECIAL" as expected.
However, if I remove the constraint, and return all orders and parts, for the "789" part, the Indicator column is null.
select t1.orderid, t1.partid, t2.Indicator
from Orders a left outer join
(
select partid, 'SPECIAL' as Indicator
from vendors
where vendorname like '%ABC%'
) t2
on t1.partid = t2.partid
where t1.partid = '789'
I can address the issue with a case statement (below) or likely restructuring into a better statement.
But I'm just curious if this is expected or if there is some SQL rule being violated in the first example.
I tried to search for this to see if it was already addressed but didn't have much luck.
This works:
select t1.orderid, t1.partid,
case when t1.partid is not null then "SPECIAL" else null end as Indicator
from Orders a left outer join
(
select partid, 'SPECIAL' as Indicator
from vendors
where vendorname like '%ABC%'
) t2
on t1.partid = t2.partid