SQL & PL/SQL :: Improve Query So That UNION Can Be Removed

select
a.empno,a.ename,a.job,
B.DNAME
from scott.emp a,scott.dept b
where ( a.ename like 'S%' and a.deptno=b.deptno)
union
select
a.empno,a.ename,a.job,
'aaa' AS DNAME
from scott.emp a,scott.dept b
where ( a.ename like 'S%' and a.job not like 'SALES%');

Output:

7369SMITHCLERKRESEARCH
7369SMITHCLERKaaa
7788SCOTTANALYSTRESEARCH
7788SCOTTANALYSTaaa

Quote:There is other way to improve the query so that UNION can be removed.