EXISTS in SQL

EXISTS in SQL

🧠 What is EXISTS?

EXISTS is a logical operator in SQL that checks whether a subquery returns any rows.

  • If the subquery returns at least one row, EXISTS evaluates to TRUE.
  • If the subquery returns no rows, EXISTS evaluates to FALSE.

It’s often used in a WHERE clause to test for the existence of related data.

🧩 Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS (
    SELECT 1
    FROM other_table
    WHERE condition
);
  • The subquery can be any query that returns rows.
  • The SELECT 1 inside is conventional — the actual column or value doesn’t matter, since EXISTS only checks for row existence.

📘 Example 1: Basic Use

Suppose you have two tables:

Customers

CustomerIDCustomerName
1Alice
2Bob
3Carol

Orders

OrderIDCustomerIDAmount
1011500
1023300

Now, find customers who have placed at least one order.

SELECT CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

✅ Result:

CustomerName
Alice
Carol

(Bob has no orders, so he’s excluded.)

📗 Example 2: Using NOT EXISTS

Find customers who have never placed an order:

SELECT CustomerName
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

✅ Result:

CustomerName
Bob

📙 Example 3: Correlated Subquery with Condition

Find customers who have placed orders greater than $400:

SELECT CustomerName
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
      AND o.Amount > 400
);

✅ Result:

CustomerName
Alice

⚖️ EXISTS vs IN

While both can test membership, they differ:

  • EXISTS stops searching once it finds a match (efficient for large datasets).
  • IN often works better when you have a small, fixed list of values.

Example equivalence:

-- Using IN
SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

-- Using EXISTS (equivalent)
SELECT CustomerName
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

Leave a Reply