1. Home
  2. MySQL Exists

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:

Parameter Name Descriptions
col_names It is the name of column(s) that contains in the specified table.
tab_name It is the name of the table from which we are going to perform the EXISTS operator.
condition It specifies for searching the specific value from the table.
subquery It is usually the SELECT statement that begins with SELECT *, but MySQL ignores it in a subquery.

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 Exists

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:

MySQL Exists

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

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.

MySQL Exists

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.

MySQL Exists

Difference between EXISTS and IN operator

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

SN IN EXISTS
1. It is used to minimize the multiple OR conditions in MySQL. It is used to check the existence of data in a subquery.
2. SELECT col_names FROM tab_name WHERE col_name IN (subquery); 
SELECT col_names
FROM tab_name
WHERE [NOT] EXISTS (subquery);
3. It compares all values inside the IN operator. It stops for further execution as soon as it finds the first true occurrence.
4. It can use for comparing NULL values. It cannot use for comparing NULL values.
5. It executes faster when the subquery result is less. It executes faster when the subquery result is large.
6. It performs a comparison between parent query and child query or subquery. It does not perform a comparison between parent query and child query or subquery.
Next TopicMySQL NOT Condition




Hot Tutorials

Contact US

Email:[email protected]

MySQL Exists
10/30