Suppose you wanted to retrieve the department number (deptno) and name (dname) fields for employees in an emp table. One way to get the values is with this SQL query:
SELECT distinct d.deptno,d.dnameFROM dept d,emp eWHERE d.deptno=e.deptno
The preceding query takes four milliseconds to execute (on my machine).
But if you write the query like this instead…
SELECT d.deptno,d.dnameFROM dept dWHERE EXISTS(select e.deptnoFROM emp eWHERE d.deptno=e.deptno);
…you’ll find that the query is much faster (about one millisecond on my machine).
Apparently, using the EXISTS command improves the performance considerably.