Re: Is this a bug, or am I loosing it

From: JayDBA <jaykash_at_hotmail.com>
Date: Thu, 1 Feb 2007 15:08:01 -0600
Message-ID: <BAY113-DAV1614E199616556AFADAA82ACA40@phx.gbl>

Thanks for your responses. Looks like this is an expected behavior. The Oracle 10g rel 2 SQL reference manual says following.

If any item in the list following a NOT IN operation evaluates to null, then all rows evaluate to FALSE or UNKNOWN, and no rows are returned. For example, the following statement returns the string 'True' for each row:

SELECT 'True' FROM employees

WHERE department_id NOT IN (10, 20);

However, the following statement returns no rows:

SELECT 'True' FROM employees

WHERE department_id NOT IN (10, 20, NULL);

The preceding example returns no rows because the WHERE clause condition evaluates to:

department_id != 10 AND department_id != 20 AND department_id != null

Because the third condition compares department_id with a null, it results in an UNKNOWN, so the entire expression results in FALSE (for rows with department_id equal to 10 or 20). This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.

Regards,

Original Message ----- From: Wolfgang Breitling To: jaykash_at_hotmail.com Cc: oracle-l-freelists Sent: Thursday, February 01, 2007 2:44 PM Subject: Re: Is this a bug, or am I loosing it

This is not a bug, it's because of the NULLs and precisely the reason why not in and not exists are not equivalent whereas in and exists are.

At 12:47 PM 2/1/2007, JayDBA wrote:

I am getting weird results from a NOT IN and NOT EXISTS query. Could this be because of NULL's or is it a bug? Regards

  Wolfgang Breitling
  Centrex Consulting Corporation
  www.centrexcc.com

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 01 2007 - 15:08:01 CST