[Oracle]subquery, null value problem[solved]

cisco

Here's a question:

Create a query to display the name, hire date, and salary for all employees who have both the same salary and commission as Scott.
Note: Do not display SCOTT in the result set.

ENAME        HIREDATE                        SAL
FORD        03-DEC-81        3000


The following is how I tried:

SQL> select sal, comm from emp where ename = 'SCOTT';

       SAL       COMM
---------- ----------
      3000

SQL> select ename, hiredate, sal from emp where (sal, comm) in (select sal, comm from emp where ename = 'SCOTT') and ename != 'SCOTT';

no rows selected


I got no result, which is dues to, I suppose,  the null value contained in the "comm" column, so the "where (sal, comm) in" statement can't function.

Could someone give me a hint, thanks in advance.

dancingpig

子查询里返回值是null的时候oracle的结果集会有问题，很奇怪。详细的原因oracle也没仔细说，只是提醒我们，写sql的时候如果子查询里有null值一定要用函数过滤掉。
SQL> select last_name,hire_date,salary from employees
  2  where (salary,nvl(commission_pct,0)) in (select salary,nvl(commission_pct,0
) from employees where lower(last_name)='khoo');

LAST_NAME                                          HIRE_DATE      SALARY
-------------------------------------------------- ---------- ----------
Walsh                                              24-4月 -98       3100
Fleaur                                             23-2月 -98       3100
Davies                                             29-1月 -97       3100
Khoo                                               18-5月 -95       3100

SQL> select last_name,hire_date,salary from employees
  2  where (salary,commission_pct) in (select salary,commission_pct from employe
es where lower(last_name)='khoo');

未选定行

cisco

Aha, nvl function, marvellous idea