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. The EXISTS operator in MySQL is a type of Boolean operator which returns the true or false result. It is used in combination with a subquery and checks the existence of data in a subquery. It means if a subquery returns any record, this operator returns true. Otherwise, it will return false. The true value is always represented numeric value 1, and the false value represents 0. We can use it with SELECT, UPDATE, DELETE, INSERT statement.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);The NOT operator is used to negates the EXISTS operator. It returns true when the subquery does not return any row. Otherwise, it returns false. Generally, the EXISTS query begins with SELECT *, but it can start with the SELECT column, SELECT a_constant, or anything in the subquery. It will give the same output because MySQL ignores the select list in the SUBQUERY.
Is exist query in MySQL?
The EXISTS operator in MySQL is a type of Boolean operator which returns the true or false result. It is used in combination with a subquery and checks the existence of data in a subquery. It means if a subquery returns any record, this operator returns true.
How do you use exists?
Use EXISTS to identify the existence of a relationship without regard for the quantity. For example, EXISTS returns true if the subquery returns any rows, and [NOT] EXISTS returns true if the subquery returns no rows. The EXISTS condition is considered to be met if the subquery returns at least one row.
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 |
MySQL EXISTS Examples
The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
Example
SELECT SupplierName<br>FROM Suppliers<br>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:
Example
SELECT ;SupplierName<br>FROM;Suppliers<br>WHERE;EXISTS&;(SELECT ;ProductName ;FROM ;Products ;WHERE;Products.SupplierID = Suppliers.supplierID;AND;Price = 22);
More Example of MySQL EXISTS
Now let us consider the EXISTS condition applying between multiple tables. Let us see the example for the same .Let us create the below tables for the same and perform “EXISTS”. Earlier created table and order_details tables as below: –
Order_details table: –
<code>create table order_details<br>(<br>o_id int,<br>id int,<br>product varchar(20),<br>quantity int<br>);</code>
Let us insert data into the order_details table. select the table values as below: –
<code>Select * from order_details;</code>
Output:
Now let us search the existing rows from the “EXISTS_Demo” table are present in the “order_details”. Let us query the table as below: –
<code>select * from EXISTS_Demo D where EXISTS (select * from order_details O WHERE O.ID=D.ID);</code>
Here if we check the table of “EXISTS_DEMO” and “order_details” table. We could see that the id=6 is not existing in the “Order_details” table. So we get the output for all the columns except the “id=6”.
Output:
