MySQL Exists

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.

Syntax

The following are the syntax to use the EXISTS operator in MySQL:

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.

This operator terminates immediately for further processing after the matching result found. This feature improves the performance of the query in MySQL.

Parameter Explanation

The following are parameters used in the EXISTS operator:

MySQL EXISTS Operator Example

Let us understand how the EXISTS operator works in MySQL. Here, we are going to first create two tables named "customer" and "orders" using the following statement:

Next, we need to insert values into both tables. Execute the below statements:

To verify the tables, run the SELECT command as below:

We will get the below output:

MySQL SELECT EXISTS Example

In this example, we are going to use EXISTS operator to find the name and occupation of the customer who has placed at least one order:

The following output appears:

Again, if we want to get the name of the customer who has not placed an order, then use the NOT EXISTS operator:

It will give the below output:

MySQL EXISTS With DELETE Statement Example

Suppose we want to delete a record from the Orders table whose order_id = 3, execute the following query that deletes the record from Orders table permanently:

To verify the output, run the below command:

In the output, we can see that the table record whose order_id=3 is deleted successfully.

If we want to check whether a row exists in a table or not, use the following query:

We will get the output 1 that means true. Hence, cust_id=104 exists in the table.

Difference between EXISTS and IN operator

The main differences between the EXISTS and IN operator is given in a tabular form:

SELECT col_names
FROM tab_name
WHERE [NOT] EXISTS (subquery);