Link to home
Start Free TrialLog in
Avatar of Simon Leung
Simon Leung

asked on

Check Null value in oracle SQL

I find that the following sql will also include those null records for right table even though I specify "NOT NULL". Any idea ?

select a.itemNo, a.unitCost, a.SupplierID, a.effectiveDate from itemCost a
right join (select itemNo, min(UNITCOST) min_Cost from itemCost group by itemNo) b
on a.ItemNo = b.ItemNo and a.unitCost = b.min_Cost and a.effectiveDate <= to_date('20190101','YYYYMMDD')
and b.itemNo = 1 and a.unitCost IS NOT NULL;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HainKurt
HainKurt
Flag of Canada image
normally we do not use right join but left join or inner join

select * from a right join on ... where ...

>>>

select * from b left join a on ... where ...
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image
normally we do not use right join but left join or inner join
"Normally" is whatever is needed for a certain situation ;-) And if that means to use a right (outer) join, then this is absolutely ok! Sometimes even a right join cannot and/or should not be "converted"!