MySQL EXISTS Operator

           MySQL 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 sample database:

Products

And a selection from the “Suppliers” table:

Suppliers

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

Example

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

Example — With SELECT Statement using NOT EXISTS

The MySQL EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM Products
WHERE NOT EXISTS (SELECT *
                  FROM Suppliers
                  WHERE Products.SupplierID = Suppliers.SuppliersID);

Example — With DELETE Statement

The following is an example of a DELETE Statement that uses the MySQL EXISTS condition:

DELETE FROM Suppliers
WHERE EXISTS (SELECT *
              FROM Products
              WHERE Suppliers.SupplierID = Products.SupplierID);