curiosity

Oracle SQL curiosity of the day

While testing around with user HR from Oracle 11g Database samples,

I found curious that :

select count(*) from locations l where l.state_province!= 'Texas' 

only returns 16 rows when it should return 22 ! This happens because the SQL engine literally interprets NULL as a non-existing value. So any columns with a NULL state province will not display : a non-existing value is not different from “Texas” !

The correct query should be as below

 select count(*) from locations l where l.state_province!= 'Texas' 
or l.state_province is null
 

Bear this in mind when writing your own SQL !