035 SQL EXISTS

The current webpage explains the SQL EXISTS operator.

Purpose: The EXISTS operator is used in SQL to test for the existence of any record returned by a subquery.
How it works: If the subquery returns one or more records, the EXISTS operator evaluates to TRUE.

Syntax Example:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (
    SELECT column_name
    FROM table_name
    WHERE condition
);

Demo Tables: The page gives sample data from “Products” and “Suppliers” tables (from the Northwind database) to illustrate how EXISTS works.
Examples Provided:
- List suppliers who have products priced less than 20.
- List suppliers with a product price exactly 22.
Practice Question: The page includes a quiz to reinforce the function of EXISTS (“Checks if a subquery returns one or more records”).

Summary:
The SQL EXISTS operator helps filter results by checking if a subquery produces at least one record, often used for conditional checks and correlated subqueries in more advanced SQL queries.[1]

SQL EXISTS Operator

The SQL EXISTS Operator

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

EXISTS Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Demo Database

Below is a selection from the "Products" table in the Northwind sample database:

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18
2	Chang	1	1	24 - 12 oz bottles	19
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
4	Chef Anton's Cajun Seasoning	2	2	48 - 6 oz jars	22
5	Chef Anton's Gumbo Mix	2	2	36 boxes	21.35

And a selection from the "Suppliers" table:

SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country
1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	London	EC1 4SD	UK
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA
3	Grandma Kelly's Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA
4	Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Tokyo	100	Japan

SQL EXISTS Examples

The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:

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

The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:

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