Join Queries  «Prev 

How the EXISTS clause works

The literal in the subquery SELECT clause can be any literal you choose and it makes no difference. You use a literal because this reduces the need for retrieving actual data FROM the database when executing the subquery.
The EXISTS clause works like a switch: as soon as one row is found that satisfies the subquery's WHERE clause, the EXISTS clause sets itself to TRUE and stops. If no row is found, the EXISTS clause sets itself to FALSE. You save time because the database does not go and find every single row that satisfies the subquery. It stops as soon as one row is found.
In addition, since the subquery is not retrieving data FROM the table, the execution of the subquery stops after evaluating the WHERE clause and does not continue by retrieving the associated row data. Very often, Oracle has an index to use when evaluating the WHERE clause. An index stores the indexed data and the row ID of the associated table row. Accessing an indexed column is one of the fastest access methods available in an Oracle database.

Example 5-5 Semijoin Using WHERE EXISTS

The following query uses a WHERE EXISTS clause to list only the departments that contain employees
SELECT department_id, department_name
FROM departments
FROM employees
WHERE employees.department_id = departments.department_id)

The execution plan reveals a NESTED LOOPS SEMI operation in Step 1:
Example 5-5

For each row in departments, which forms the outer loop, the database obtains the department ID, and then probes the employees.department_id index for matching entries.
Conceptually, the index looks as follows:

If the first entry in the departments table is department 30, then the database performs a range scan of the index until it finds the first 30 entry, at which point it stops reading the index and returns the matching row from departments. If the next row in the outer loop is department 20, then the database scans the index for a 20 entry, and not finding any matches, performs the next iteration of the outer loop. The database proceeds in this way until all matching rows are returned.