select e.ename,d.deptno,d.dname,d.loc
from dept d left join emp e
on(d.deptno = e.deptno
and (e.deptno=10 or e.deptno=20))
order by 2;
另外,还可以先用emp.deptno进行筛选,然后进行外部联接:
select e.ename,d.deptno,d.dname,d.loc
from dept d
left join
(select ename,deptno
from emp
where deptno in (10,20)
) e on (e.deptno =d.deptno)
order by 2;