1. Overview

The EXISTS condition in Oracle is a powerful operator used in SQL queries to check whether a subquery returns any rows. If the subquery returns at least one row, the EXISTS condition evaluates to TRUE; otherwise, it evaluates to FALSE. This feature is commonly used in scenarios requiring existence checks or correlated subqueries.

2. Key Features of EXISTS

• Row Check: Determines if the subquery returns any rows, regardless of the data content.
• Short-Circuiting: Stops execution as soon as a single row is found in the subquery.
• Correlated Subqueries: Often used with subqueries referencing columns from the outer query.

2. Syntax

SELECT column_list
FROM table1 t1
WHERE EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t1.column_name = t2.column_name
);

Explaination

• SELECT 1: The value selected in the subquery is irrelevant; EXISTS only checks for row presence.
• Subquery: Defines the condition to verify if rows exist.
• Outer Query: Returns rows based on the evaluation of the EXISTS condition.

3. Examples

3.1 Example 1: Basic Usage of EXISTS

Tables

Employee Table:

+-------------+---------+---------------+
| EMPLOYEE_ID |   NAME  | DEPARTMENT_ID |
+-------------+---------+---------------+
| 1           | Alice   | 101           |
+-------------+---------+---------------+
| 2           | Bob     | 102           |
+-------------+---------+---------------+
| 3           | Charlie | 103           |
+-------------+---------+---------------+
| 4           | David   | NULL          |
+-------------+---------+---------------+

Department table:

+---------------+-----------------+
| DEPARTMENT_ID | DEPARTMENT_NAME |
+---------------+-----------------+
| 101           | HR              |
+---------------+-----------------+
| 102           | IT              |
+---------------+-----------------+

Query

Find employees who are assigned to a department:

SELECT e.employee_id, e.name
FROM employee e
WHERE EXISTS (
    SELECT 1
    FROM department d
    WHERE e.department_id = d.department_id
);

Output

+-------------+-------+
| EMPLOYEE_ID |  NAME |
+-------------+-------+
| 1           | Alice |
+-------------+-------+
| 2           | Bob   |
+-------------+-------+

4. EXISTS vs IN

Feature	EXISTS	IN
Evaluation	Stops at the first matching row.	Evaluates all rows in the subquery.
Performance	Better for large subquery results.	Better for small subquery results.
NULL Handling	Ignores NULLs in the subquery.	May fail if the subquery contains NULLs.