Open In App

IN vs EXISTS in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

SQL stands for Structured Query Language. SQL is used for retrieving useful information from a large set of data and it is used for storing the data in the Database, modifying, or manipulating the data from the database.

In this article, we are going to discuss IN Operator and EXISTS Operator in SQL as well as the difference between these two operators in detail.

IN Operator in SQL

To match an expression against a list of values, SQL provides the IN Operator. So we do not need to use multiple OR conditions in SELECT, UPDATE, etc.

We can list values directly or we want to provide a query result to the IN operator.

Syntax:

SELECT columnName(s) FROM tableName WHERE columnNameX IN (value1,vaule2,….);

Here, ColumnNameX is matched with every value (value1, value2,…) in the list. If the match occurs, then IN evaluates to be TRUE, and if the mismatch occurs, then IN evaluates to be FALSE.

Suppose we have a table named “Customers” as:

Query

CREATE table Customer(
  CUSTOMERID INT PRIMARY KEY,
  FIRST_NAME VARCHAR(50),
  LAST_NAME VARCHAR(50),
  AGE INT);
INSERT INTO Customer(CUSTOMERID,FIRST_NAME,LAST_NAME,AGE) VALUES
(1,'Mohit','Kumar',21),
(2,'Praful','Singh',22),
(3,'Ritik','Kumar',25),
(4,'Vishnu','Yadav',26);

Output

Customer Table

Customer Table

Now we want to see the details of the customers whose FIRST_NAME can be ‘Mohit’ or ‘Praful’ or ‘Ritik’, we can use IN operator & give a set of values to check upon.

Query

SELECT * FROM Customer where FIRST_NAME IN('Mohit', 'Praful', 'Ritik');

Output

output

output

Exists Operator in SQL

If the argument sub-query is non-empty, exists construct returns the value true, otherwise false. To check whether a row is returned through this sub-query or not, it is used. True is returned if one or more rows are returned by executing the sub-query, otherwise False when no rows are returned.

Syntax

SELECT columnName(s) FROM tableName1 WHERE EXISTS

(subquery);

subquery is of the form : SELECT columnName FROM tableName2 WHERE condition

Here the sub-query is executed first. 

The sub-query is a SELECT statement.  The EXISTS condition will be met & it will return TRUE if the subquery returns at least one record in its result set, else,  the EXISTS condition will not be met and it will return FALSE.

Note: The sub-query is returning for EVERY row in the outer query’s table.

Now, let up suppose we have another table: “Accounts” as:

Create Accounts table

CREATE table Accounts(
  CUSTOMERID INT PRIMARY KEY,
  ACCNO INT,
  ACCTYPE VARCHAR(50))
INSERT INTO Accounts(CUSTOMERID,ACCNO,ACCTYPE) VALUES
(1,101,'Savings'),
(2,102,'Savings'),
(3,103,'Savings'),
(4,104,'Current');

Output

Accounts Table

Accounts Table

Now to see all the customers whose any kind of account exists, we can make use of exists keyword as:

Query

SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Accounts 
WHERE Customer.CUSTOMERID=Accounts.CUSTOMERID);

Output

output

output

We here check: match for every CUSTOMERID in the customer table with the CUSTOMERID in the accounts table. 

If Customer.CUSTOMERID = Accounts.CUSTOMERID evaluates to be true in the sub-query, and one row is returned & thus the sub-query evaluates to be true & our outer query (Select * from Customer) gets, executed for that particular customer.

Note: To do the same thing, i.e. to see all the customers whose any kind of account exists, we can make use of IN keyword also.

Query

SELECT * FROM Customer WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM Accounts);

Output

output

output

IN Vs EXISTS Operators

IN Operator EXISTS Operator
IN can be used as a replacement for multiple OR operators.  To determine if any values are returned or not, we use EXISTS. 
Faster execution as subquery executes only once.  Slower execution as  EXISTS Operator forces the sub-query to execute again &  again for each subsequent outer Query.
In the IN-condition SQL Engine compares all the values in the IN Clause. Once true is evaluated in the EXISTS condition then the SQL Engine will stop the process of further matching.
Follow BOTTOM – UP  Approach of execution. Follow  TOP-DOWN  Approach of execution.
The IN operator cannot compare anything with NULL values. The EXISTS clause can compare everything with NULLs.
A direct set of values can be given for comparison. Cannot compare directly the values, a sub-query needs to be given.

Conclusion

In this article, we have basically mentioned the IN and EXISTS operator and the differences between them. Both the operators, whether the IN and EXISTS, play an important role in the Structured Query language. IN operator is used to reduce the OR conditions, whereas EXISTS operator is used to look for the existence of a row in a given table that satisfies a set of criteria or not. The main characteristic of the IN operator is its following BOTTOM-UP approach for execution. There are some differences between their functions, IN operator can not be compared to anything with NULL values but EXISTS can compare with NULL values.

Frequently Asked Questions

Q.1: How does the “IN” clause work?

Answer:

The “IN” clause checks if a value in a specified column matches any value in a list. For example,

SELECT * FROM employees

WHERE department_id IN (101, 102, 103);

retrieves employees in departments 101, 102, or 103.

Q.2: What is the “EXISTS” clause in SQL?

Answer:

The “EXISTS” clause is used to check if a subquery returns any rows. If the subquery returns at least one row, the “EXISTS” condition evaluates to true.

Q.3: When should I use “IN” vs. “EXISTS”?

Answer:

Use the “IN” clause when you want to filter rows based on a specific list of values. Use the “EXISTS” clause when you want to check for the existence of rows that meet certain conditions in a subquery.



Last Updated : 19 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads