SQL IN vs SQL EXISTS

SQL IN vs SQL EXISTS

This article discusses in detail about the IN and the EXISTS operators in SQL. It is a common question between developers that what is the difference between these two operators. To know the difference, let’s first discuss about each of these operators in detail.

IN Operator

The IN Operator is used to get results when a specified value matches a value in a set of values or is returned by an inner query. This operator can also be used with the WHERE clause to specify more than one value. The IN operator is often referred to as the acronym for the OR operator because it reduces the use of multiple OR conditions in SELECT, INSERT, UPDATE or DELETE queries.

In this operator, the subquery is solved first and that result is then used to solve the outer query.

The following is the general syntax of the IN operator:

SELECT column_name
 FROM table_name
 WHERE column_name IN (value1, value2, …); 

Example:

Let’s consider the following Customer table.

Cust_id	Cust_name	City	Occupation
1	Harry	Kolkata	Business
2	Ron	Mumbai	Doctor
3	Albus	Delhi	Engineer
4	Dobby	Pune	Scientist
5	Snape	Bangalore	Student

Query:

SELECT *
 FROM Customer    
 WHERE Occupation IN ('Doctor', 'Scientist', 'Engineer'); 

The above query will return all the customers who are either Doctor or Scientist or Engineer.

Output:

Cust_id	Cust_name	City	Occupation
2	Ron	Mumbai	Doctor
3	Albus	Delhi	Engineer
4	Dobby	Pune	Scientist

EXISTS Operator

The EXISTS operator, unlike the IN operator, returns a Boolean value. This operator checks the result of the inner query and returns a Boolean value i.e., either true or false. If the inner query returns a single or multiple record, then the operator returns true else it returns false when no records are found.

This operator is also efficient as it stops further processing as soon as the first true event is detected.

The following is the syntax of the EXISTS operator:

SELECT column_names    
 FROM table_name    
 WHERE NOT EXISTS (    
 SELECT column_names     
 FROM table_name     
 WHERE condition    
 ); 

Example:

Let’s consider the following Customer table.

Cust_id	Name	Occupation	Age
101	Harry	Engineer	32
102	Ron	Developer	30
103	Dobby	Leader	28
104	Albus	Scientist	45
105	Snape	Carpenter	26
106	Ginger	Actor	25
107	NULL	NULL	NULL

Let’s consider another table Orders.

Order_id	Cust_id	Product_name	Order_date
1	101	Laptop	2021-01-10
2	103	Desktop	2021-02-12
3	106	IPhone	2021-02-15
4	104	Mobile	2021-03-05
5	102	TV	2021-03-20

Query:

SELECT Name, Occupation
 FROM Customer    
 WHERE EXISTS (
 SELECT *
 FROM Orders     
 WHERE Customer.Cust_id = Orders.Cust_id
 ); 

The above query will return the name and occupation of all the customers who have placed at least one order.

Output:

Name	Occupation
Harry	Engineer
Ron	Developer
Dobby	Leader
Albus	Scientist
Ginger	Actor

Difference table between IN and EXISTS operator

The key differences between the IN operator and the EXISTS operator are listed below in a tabular manner:

IN Operator	EXISTS Operator
It minimizes the usage of the OR conditions.	It checks the existence of a record in the inner query.
It compares the values of the inner query with the value of the outer query.	It does not compare the values between the inner query and the sub query.
It checks all the values inside the block of the IN clause.	It stops any further execution as soon as the first true condition is met.
It can return TRUE, FALSE or NULL.	It returns either TRUE or FALSE.
It can be used to check NULL values.	It cannot be used to check NULL values.
It is used with both subqueries and values.	It is used only with subqueries.
Execution is faster when the result of the inner query is less.	Execution is faster even when the result of the inner query is large. It is more efficient than the IN operator.
Syntax: SELECT column_names FROM table_name WHERE column_name IN (subquery);	Syntax: SELECT column_namesFROM table_nameWHERE [NOT] EXISTS (subquery);

Conclusion:

In this topic, a comparison has been made between the IN operator and the EXISTS operators of SQL. Both the operators do the same operation but their internal working is different. They have different logical working. Any one of them can be selected according to the requirement. But, if the set of data is large, it is always recommended to go for the EXISTS operator.