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. 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:

ProductIDProductNameSupplierIDCategoryIDUnitPrice
1Chais1110 boxes x 20 bags18
2Chang1124 – 12 oz bottles19
3Aniseed Syrup1212 – 550 ml bottles10
4Chef Anton’s Cajun Seasoning2248 – 6 oz jars22
5Chef Anton’s Gumbo Mix2236 boxes21.35

And a selection from the “Suppliers” table:

SupplierIDSupplierNameContactNameAddressCityPostalCodeCountry
1Exotic LiquidCharlotte Cooper49 Gilbert St.LondonEC1 4SDUK
2New Orleans Cajun DelightsShelley BurkeP.O. Box 78934New Orleans70117USA
3Grandma Kelly’s HomesteadRegina Murphy707 Oxford Rd.Ann Arbor48104USA
4Tokyo TradersYoshi Nagase9-8 Sekimai Musashino-shiTokyo100Japan

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 &lt; 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 =&nbsp;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:

output 4

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:

output 5

MySQL EXISTS Operator
Show Buttons
Hide Buttons