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.dname
FROM dept d,emp e
The preceding query takes four milliseconds to execute (on my machine).
But if you write the query like this instead...
FROM dept d
WHERE EXISTS(select e.deptno
FROM emp e
...you'll find that the query is much faster (about one millisecond on my machine).
Apparently, using the EXISTS command improves the performance considerably.