SQL EXISTS Keyword

The EXISTS keyword in SQL is used with subqueries (queries nested inside another query) to check if the subquery returns any rows. It's a very efficient way to test for the existence of data that meets a certain condition.

EXISTS: Definition and Usage

Unlike other subquery operators that might return data from the subquery, EXISTS only cares about whether the subquery finds *any* matching rows. If the subquery finds at least one row that satisfies the condition, EXISTS returns TRUE; otherwise, it returns FALSE. This makes it ideal for checking if related data exists without needing to retrieve that data itself.

Syntax

SELECT column_names
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);
      

Note that the subquery typically uses SELECT 1 (or any constant value) instead of selecting actual columns, since EXISTS only cares about the existence of rows, not their content.

Examples

Finding Suppliers with Products Under $20

This query retrieves the names of suppliers who have at least one product priced under $20. It uses a subquery to check for the existence of such products for each supplier.

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT 1 FROM Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
      

SupplierName
------------
Supplier A (Example - adapt to your data)
Supplier B (Example - adapt to your data)

      

Finding Suppliers with Products Priced at $22

This query finds suppliers who offer at least one product priced exactly at $22.

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT 1 FROM Products WHERE SupplierId = Suppliers.supplierId AND Price = 22);
      

SupplierName
------------
Supplier C (Example - adapt to your data)