Re: Subquery with null returns

On Thu, 27 Mar 2003 20:47:09 GMT, "David Wang" <ywang_at_adelphia.net> wrote:

>Hi, I have a subquery question, thanks for any response.
>"EMP" table has two columns "empno" and "mgr", I do this
>SELECT ENAME FROM EMP WHERE EMPNO NOT IN
>(SELECT MGR FROM EMP);
>I assumed this query will give me all employees who are not
>manager but it returns me "no rows selected" bacause one employee
>has null value in "MGR" column in subquery. Then I do,
>SELECT ENAME FROM EMP WHERE EMPNO IN
>(SELECT MGR FROM EMP);
>it gives me the names of all managers as it should.
>why with the same subquery one can retrives rows and one can't.
>I did this query on 8.1.7
>-DW-
>
>

Hi DW,

Because NULL values are discarded in joins, subqueries, correlated subqueries and comparison operators, "=","!=", ">" etc. That is the correct behaviour, because NULL is not comparable to anything, not even another NULL :

select 1 from dual where 1 != NULL;
and
select 1 from dual where NULL = NULL;

Returns no row.

1 is clearly not null, but the comparison 1 != NULL is impossible (That's why we have the IS (NOT) NULL operator). So no result.

Same with

  SELECT ENAME FROM EMP WHERE EMPNO NOT IN  (SELECT MGR FROM EMP); To check a NOT IN, Oracle must check against ALL the values in the subset, and one of these values is NULL. Oracle cannot make this comparison, so no rows.

On the other hand

  SELECT ENAME FROM EMP WHERE EMPNO IN
 (SELECT MGR FROM EMP); Oracle just needs to find ONE match in the subquery to return the row. It doesn't matter if there's a NULL in the subset , as soon Oracle has found just one match, it can ignore all other values, including the NULL.

• Kenneth Koenraadt