Question: What are the different ways in which the 'EXISTS' clause is used in Oracle?
In Oracle, the EXISTS clause is used in subqueries to test for the existence of records in a table or a combination of tables. It returns a Boolean value (TRUE or FALSE) based on whether the subquery returns any rows.
Here are some of the different ways in which the EXISTS clause can be used in Oracle:
Correlated subqueries: In a correlated subquery, the subquery references a column from the outer query. The subquery is evaluated once for each row returned by the outer query, based on the value of the correlated column. Correlated subqueries are useful when you want to filter records based on a condition that involves data from another table.
Non-correlated subqueries: In a non-correlated subquery, the subquery does not reference any column from the outer query. The subquery is evaluated only once and returns a single result, which is used in the outer query. Non-correlated subqueries are useful when you want to retrieve a single value or to filter records based on a condition that does not involve data from another table.
Subqueries with EXISTS and NOT EXISTS: The EXISTS clause can be used with the NOT operator to test for the non-existence of records. For example, to retrieve all customers who have not placed any orders, you can use the NOT EXISTS clause in a subquery.
Subqueries with correlated IN: The IN clause is similar to EXISTS, but it tests for the presence of a value in a list or a subquery. A correlated IN subquery references a column from the outer query, similar to a correlated EXISTS subquery.
EXISTS with UNION, INTERSECT, and EXCEPT: The EXISTS clause can be used with set operators like UNION, INTERSECT, and EXCEPT to combine the results of multiple subqueries. For example, to retrieve all customers who have either placed an order with a total amount greater than $1000 or who are from a country with at least one other customer who has placed such an order, you can use the UNION operator with two EXISTS subqueries.
In summary, the EXISTS clause in Oracle can be used in various ways to test for the existence or non-existence of records in subqueries, and it can be combined with other operators and functions to filter and transform data.
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
WHERE EXISTS (SELECT 1
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.
