Usuage of In and Exists - Taken from Oracle Document (Interview Question)

Example 1: Using IN - Selective Filters in the Subquery
This example demonstrates how rewriting a query to use IN can improve performance. This query identifies all employees who have placed orders on behalf of customer 144.

The following SQL statement uses EXISTS:
SELECT /* EXISTS example */
         e.employee_id, e.first_name, e.last_name, e.salary
  FROM employees e
 WHERE EXISTS (SELECT 1 FROM orders o                  /* Note 1 */
                  WHERE e.employee_id = o.sales_rep_id   /* Note 2 */
                    AND o.customer_id = 144);            /* Note 3 */


Note 1: This shows the line containing EXISTS.
Note 2: This shows the line that makes the subquery a correlated subquery.
Note 3: This shows the line where the correlated subqueries include the highly selective predicate customer_id = number.

Execution Plan:

Rewriting the statement using IN results in significantly fewer resources used.

SELECT /* IN example */
         e.employee_id, e.first_name, e.last_name, e.salary
    FROM employees e
   WHERE e.employee_id IN (SELECT o.sales_rep_id         /* Note 4 */
                             FROM orders o
                            WHERE o.customer_id = 144);  /* Note 3 */


Note 3: This shows the line where the correlated subqueries include the highly selective predicate customer_id = number
Note 4: This indicates that an IN is being used. The subquery is no longer correlated, because the IN clause replaces the join in the subquery.

Execution Plan:

Example 2: Using EXISTS - Selective Predicate in the Parent

This example demonstrates how rewriting a query to use EXISTS can improve performance. This query identifies all employees from department 80 who are sales reps who have placed orders.
The following SQL statement uses IN:

SELECT /* IN example */
         e.employee_id, e.first_name, e.last_name, e.department_id, e.salary
    FROM employees e
   WHERE e.department_id = 80                                    /* Note 5 */
     AND e.job_id        = 'SA_REP'                              /* Note 6 */
     AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o); /* Note 4 */


Note 4: This indicates that an IN is being used. The subquery is no longer correlated, because the IN clause replaces the join in the subquery.
Note 5 and 6: These are the selective predicates in the parent SQL.

Execution Plan:

Rewriting the statement using Exists results in significantly fewer resources used.

SELECT /* EXISTS example */
         e.employee_id, e.first_name, e.last_name, e.salary
    FROM employees e
   WHERE e.department_id = 80                           /* Note 5 */
     AND e.job_id        = 'SA_REP'                     /* Note 6 */
     AND EXISTS (SELECT 1                               /* Note 1 */
                   FROM orders o
                  WHERE e.employee_id = o.sales_rep_id);  /* Note 2 */

Note 1: This shows the line containing EXISTS.
Note 2: This shows the line that makes the subquery a correlated subquery.
Note 5 & 6:These are the selective predicates in the parent SQL.

Execution Plan: