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.