Subselect question

[Mockenrue]

Problem:
Query retrieves one record, but when I use the exact same query as subquery
the master query returns no rows.

Example with EMP table:
----------------------------

SQL> select e.job
2 , e.mgr
3 , e.sal
4 , e.comm
5 , e.deptno
6 from emp e
7 where e.empno = 7900;

JOB MGR SAL COMM DEPTNO
--------- ---------- ---------- ---------- ----------
CLERK 7698 950 30

SQL> select ename
2 from emp
3 where ( job,mgr,sal,comm,deptno ) in
4 (
5 select e.job
6 , e.mgr
7 , e.sal
8 , e.comm
9 , e.deptno
10 from emp e
11 where e.empno = 7900
12 );

no rows selected

----------------------------

I think it has to do with NULL values in the COMM column, but it drives me nuts !
Help please ...

 

[sem]

Add NVL with some meaningless value.

Regards, Dima

 

[Mockenrue]

Yes, that works, thanks.

Still don't understand why the row did not show without the NVL.

 

[ddrillich]

It didn’t work without the NVL because no null is equal to any other null.
By using NVL, you assign a value to these nulls and therefore these two converted nulls can be equal.

Cheers,
Dan