请教一个SQL查询问题，如果碰到空值如何处理？

[qq_35902228]

问题：查询和员工CLARK的工资以及奖金相同的公司员工的姓名和工资
表如下：


CLARK的奖金是空的，写成

select ename,sal from emp
where sal=(select sal from emp where ename='CLARK')
and
comm=(select comm from emp where ename='CLARK');

什么都查不出来，空值不能跟空值比较相等

想先用一次查询把空值替换成0，但是写成下面又报错，说表e不存在：
select e.ename,e.hiredate,e.sal
from (select ename,hiredate,sal,nvl(comm,0) as comm1 from emp) e
where e.sal=(select e.sal from e where e.ename='CLARK')
and e.comm1=(select e.comm1 from e where e.ename='CLARK');


初学Oracle，一开始以为这是简单的问题，但是现在我已经没辙了，请教一下各位大神这个怎么处理啊？

[js14982]

select e.ename,e.hiredate,e.sal 
from (select ename,hiredate,sal,nvl(comm,0) as comm1 from emp) e 
where e.sal=(select e.sal from  (select ename,hiredate,sal,nvl(comm,0) as comm1 from emp) e where e.ename='CLARK')
and e.comm1=(select e.comm1 from  (select ename,hiredate,sal,nvl(comm,0) as comm1 from emp) e where e.ename='CLARK');

不能你那样直接用e表的

[js14982]

空值判断是通过 comm is null或者 comm is not null 来判断。

[ghx287524027]

select * from EMP A 
where(nvl(sal,0),nvl(comm,0)) in (select nvl(sal,0),nvl(comm,0) from emp b where b.ENAME='CLARK')
and a.ENAME<>'CLARK'

[qq_35902228]

非常感谢二位~！按照你们的方法真的解决了~