Skip to Main Content
  • Questions

  • Please explain why null value is not returned

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, anto.

Asked: February 19, 2018 - 11:34 am UTC

Last updated: February 19, 2018 - 3:45 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

select distinct commission_pct from hr.employees;
if i run the above query it returns null value, But if i run below query it have't return null value.Pls explain
select distinct commission_pct from hr.employees where commission_pct <> '.0';

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/file/content_GAQ0D7I3IECFQ2RVA6MZG7YPE.html

and Chris said...

The result of comparing something to null is unknown. This applies to inequalities too. So

1 > null => unknown

Queries only return rows where the result of the conditions is true.

To find null rows, you must use the is null condition:

select count(*) from hr.employees;

COUNT(*)   
       107 

select count(*) from hr.employees
where  commission_pct is null or
       commission_pct > 0;

COUNT(*)   
       107


I discuss this in more detail in this video:



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.