SQL EXISTS Keyword

 The EXISTS keyword in SQL is used to test for the existence of any record in a subquery. It returns TRUE if the subquery returns one or more rows, and FALSE otherwise. It is commonly used in conditional statements like WHERE or IF.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

• subquery: A SELECT statement that checks for the existence of rows meeting certain criteria.

Key Points

1. Evaluates Existence: The EXISTS keyword does not return data; it merely tests whether the subquery results in any rows.
2. Performance: The execution plan may vary based on the database system. For certain use cases, EXISTS can be faster than IN because it stops processing once a match is found.
3. Correlation: Often used with correlated subqueries where the subquery references columns from the outer query.

---

Example 1: Basic EXISTS Query

SELECT EmployeeID, EmployeeName
FROM Employees
WHERE EXISTS (
    SELECT 1
    FROM Orders
    WHERE Orders.EmployeeID = Employees.EmployeeID
);

This query retrieves employees who have at least one order in the Orders table.

---

Example 2: EXISTS with NOT

SELECT EmployeeID, EmployeeName
FROM Employees
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders
    WHERE Orders.EmployeeID = Employees.EmployeeID
);

This query retrieves employees who have no orders.

---

Difference Between EXISTS and IN

• EXISTS: Checks for the existence of any row returned by a subquery.
• IN: Compares a column to a list of values.

Example:

-- Using EXISTS
SELECT * FROM Employees e
WHERE EXISTS (
    SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID
);

-- Using IN
SELECT * FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Orders);

Use EXISTS when you need to check for existence without returning the actual data from the subquery.