Coding 1)
select comm
from emp
where comm is not null and comm != 0;
Coding 2)
select comm
from emp
where comm != 0;
The results of Coding 1 and Coding 2 are the same.
I know that the values of null and 0 are different, but I do not know why.
Well:
0 = the numeric value zero
null = UNKNOWN; which could mean many things, such as:
- not applicable: the attribute isn't relevant for this row
- missing: there should be a value, but someone forgot to enter it
- etc.
Your queries return the same rows because:
null != <anything>
returns UNKNOWN.
SQL statements only access rows if the where clause is TRUE. So
comm is not null
is implied in both your statements.