Home » SQL & PL/SQL » SQL & PL/SQL » WHERE name != 'HENRY' excludes name which has NULL
WHERE name != 'HENRY' excludes name which has NULL [message #623390] Tue, 09 September 2014 14:20 Go to next message
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 #623391 is a reply to message #623390] Tue, 09 September 2014 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because NULL is neither equal nor not equal to any value, including NULL.
More precisely the returned value for "A = NULL" or "A != NULL" is UNKNOWN, as it is not TRUE the row is not returned.

Re: WHERE name != 'HENRY' excludes name which has NULL [message #623516 is a reply to message #623391] Thu, 11 September 2014 02:33 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You could opt to use the less known function LNNVL

SQL> With orafaq
  2  As
  3  (
  4  Select 1 empno, 'Jack' emp_full_name From dual Union all
  5  Select 2 empno, 'Frank' emp_full_name From dual Union all
  6  Select 3 empno, 'Henry' emp_full_name From dual Union all
  7  Select 4 empno, 'David' emp_full_name From dual Union all
  8  Select 5 empno, 'Henry' emp_full_name From dual Union all
  9  Select 6 empno, Null emp_full_name From dual
 10  )
 11  Select *
 12  From Orafaq
 13  Where LNNVL(emp_full_name='Henry')
 14  /

     EMPNO EMP_F
---------- -----
         1 Jack
         2 Frank
         4 David
         6


MHE
Re: WHERE name != 'HENRY' excludes name which has NULL [message #623518 is a reply to message #623516] Thu, 11 September 2014 03:08 Go to previous messageGo to next message
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
Re: WHERE name != 'HENRY' excludes name which has NULL [message #623520 is a reply to message #623518] Thu, 11 September 2014 03:36 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
True, but I wanted to demonstrate the use of a more obscure function. To be honest I'd probably opt for the NVL as well, just to make sure others would understand the code as well.

MHE
Re: WHERE name != 'HENRY' excludes name which has NULL [message #623548 is a reply to message #623520] Thu, 11 September 2014 06:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Well, there are many ways to do same thing in SQL. Good to see LNNVL, I would have never remembered it.

The same could be achieved using DECODE :

SELECT * FROM DATA WHERE DECODE(ename,NULL,'foo',ename) <> 'SCOTT'
Re: WHERE name != 'HENRY' excludes name which has NULL [message #623553 is a reply to message #623548] Thu, 11 September 2014 07:20 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
So many choices indeed.
I recently came across this variant in a package:
Where decode(emp_full_name, 'Henry',1) is null


MHE
Re: WHERE name != 'HENRY' excludes name which has NULL [message #623566 is a reply to message #623553] Thu, 11 September 2014 08:51 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Nice.

Another (not so good) way would be to use CASE expression in where clause, it is just to expand the same decode logic.
Re: WHERE name != 'HENRY' excludes name which has NULL [message #623569 is a reply to message #623553] Thu, 11 September 2014 09:01 Go to previous messageGo to next message
ind!@2020
Messages: 23
Registered: September 2014
Location: India
Junior Member

SELECT * FROM EMP WHERE (comm IS NULL OR comm<>300);

SELECT * FROM EMP WHERE CASE WHEN comm IS NULL THEN 0 ELSE comm END <>300;


SAM
Re: WHERE name != 'HENRY' excludes name which has NULL [message #623576 is a reply to message #623569] Thu, 11 September 2014 10:23 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Well, from performance point of view, my conclusion would be to mention the NULL condition in where clause explicitly.

Something like,

 where (
            (
                 val1 is null
             and
                 Col is null
            )
         or
            Col = val1
        )
    and (
            (
                 val2 is null
             and
                 Col2 is null
            )
         or
            Col2 = val2
        )
Previous Topic: Help required for SQL query to fetch desired records
Next Topic: RAISE_APPLICATION_ERROR
Goto Forum:
  


Current Time: Fri Mar 29 09:27:07 CDT 2024