IN vs EXISTS in SQL

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, and other statements. You can list values directly or provide a query result to the IN operator.

Syntax:

SELECT columnName(s)

FROM tableName

WHERE columnNameX IN (value1,vaule2,....);

Parameters:

• columnName(s): The columns to retrieve data from.
• tableName: The name of the table from which to retrieve data.
• columnNameX: The column to compare against the list of values.
• value1, value2, ...: The list of values to check against.

Example

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

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

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);

Parameters:

• columnName(s): The columns to retrieve data from.
• tableName1: The main table from which to retrieve data.
• subquery: A subquery that returns a set of values.

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.

Example

Now, let up suppose we have another table: "Accounts"

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

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

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

IN Vs EXISTS Operators

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.