EXISTS Operator

EXISTS Operator

The EXISTS operator checks whether a subquery returns any rows. It returns true if the subquery finds at least one row.

Example

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (
  SELECT ProductName FROM Products
  WHERE Products.SupplierID = Suppliers.SupplierID
  AND Price < 20
);

Returns suppliers who have at least one product under $20.

How it reads

For each supplier, run the inner query. If it returns any rows, keep the supplier.

EXISTS with NOT

SELECT SupplierName
FROM Suppliers
WHERE NOT EXISTS (
  SELECT ProductName FROM Products
  WHERE Products.SupplierID = Suppliers.SupplierID
);

Returns suppliers with no products.

EXISTS vs IN

• EXISTS is often faster with large tables.
• IN works with a list of values; EXISTS checks for existence of rows.
• They answer similar questions but behave differently with NULLs.

TL;DR

• EXISTS is TRUE if a subquery returns any rows.
• Pairs naturally with a correlated subquery.
• NOT EXISTS finds rows with no match.
• Often faster than IN on big tables.