MySQL EXISTS Operator Vs. IN Operator

Important Points About MySQL EXISTS Operator

Although both IN operator and EXISTS operator seems to perform similar types of tasks, there is vast difference between their implementation and uses. Let’s see how they are different from each other.

Difference Between EXISTS Operator and IN Operator
IN Operator	EXISTS Operator
IN operator is used to search a data into the table that matches the data within the specified set or subquery	EXISTS operator checks for the existence of a subquery.
It is less efficient for large dataset as it continues to execute until it traverses the whole.	EXISTS operator are efficient for large dataset as it stops execution when a matching data is found
Comparisons between parent queries and subqueries happen.	Comparisons between parent queries and subqueries do not happen.
IN operator is used to decrease the number of “=” operator. We can club all those conditions in one set or subquery.	Exists operator is used to check for existence of a subquery with more complex conditions than the IN operator conditions.
It checks for the existence of specific value from a finite set or small set.	It checks for the existence of a row matching specific complex conditions.

MySQL IN and EXISTS Operator Example

We are using the w3wiki table to get all the rows with rank lies in the set (‘3’ , ‘2’, ‘1’).

Note: We are using the same tables from the Examples block.

IN operator example

Query

SELECT *
FROM w3wiki 
WHERE rank IN ('3','2','1');

Output:

Result – In operator

In the above example, we can clearly observe all those rows with rank column belongs to the set (‘3′,’2′,’1’) are displayed in output.

EXISTS Operator Example

Let’s fetch all the values from the table w3wiki where id column of w3wiki matches with the id columns of the courses table.

Query

SELECT *
FROM w3wiki 
WHERE EXISTS (SELECT courses.id FROM courses 
    WHERE courses.id = w3wiki.id);

Output:

Result – Exists Operator