WHERE name != 'HENRY' excludes name which has NULL [message #623390] |
Tue, 09 September 2014 14:20 |
rkhatiwala
Messages: 178 Registered: April 2007
|
Senior Member |
|
|
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
Hi,
I have a simple query as
SELECT * FROM emp_na_canada
WHERE emp_full_name != 'HENRY' ;
emp_na_canada table has records, which has emp_full_name = NULL, which are valid.The above query is excluding those records as well.
There are 241 records which has no emp_full_name ( emp_full_name is null), and 31 records which has some value, not 'HENRY'. But the above query is bringing only 31, and not 241 + 31 = 272.
But if i do
SELECT * FROM emp_na_canada
WHERE (emp_full_name !='HENRY' OR emp_full_name IS NULL);
Then it brings 272.
Why it would exclude the Null value records in the first query?
Thanks,
rk
|
|
|
|
|
Re: WHERE name != 'HENRY' excludes name which has NULL [message #623518 is a reply to message #623516] |
Thu, 11 September 2014 03:08 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Even NVL could be used as a trick :
SQL> WITH DATA AS(
2 SELECT ENAME FROM EMP UNION ALL
3 select null from dual UNION ALL
4 select null from dual UNION ALL
5 SELECT NULL FROM DUAL UNION ALL
6 SELECT NULL FROM DUAL UNION ALL
7 SELECT NULL FROM DUAL
8 )
9 SELECT * FROM DATA
10 where nvl(ename,'foo')<>'SCOTT'
11 /
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
ADAMS
JAMES
FORD
MILLER
18 rows selected.
Regards,
Lalit
|
|
|
|
|
|
|
|
|