select t.empno,nvl(t.mgr,'MGR') from emp t where t.deptno in (select s.deptno from dept s where s.deptno='1')
select t.empno,nvl(t.mgr,'MGR') from emp t where exists (select s.deptno from emp s where s.deptno='1' and t.deptno=s.deptno)
select t.empno,nvl(t.mgr,'MGR') from emp t where t.deptno is not null and exists (select s.deptno from emp s where s.deptno='1' and t.deptno=s.deptno)
select t.empno,nvl(t.mgr,'MGR') from emp t where not exists (select s.* from dept s where s.deptno in(2,3) and t.deptno=s.deptno)
select t.empno,nvl(t.mgr,'MGR') from emp t where t.deptno in (select s.deptno from dept s where s.deptno not in(2,3))