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.
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:
CREATE table Customer(
CUSTOMERID INT PRIMARY KEY,
INSERT INTO Customer(CUSTOMERID,FIRST_NAME,LAST_NAME,AGE) VALUES
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.
SELECT * FROM Customer where FIRST_NAME IN('Mohit', 'Praful', 'Ritik');
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.
SELECT columnName(s) FROM tableName1 WHERE EXISTS
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,
INSERT INTO Accounts(CUSTOMERID,ACCNO,ACCTYPE) VALUES
Now to see all the customers whose any kind of account exists, we can make use of exists keyword as:
SELECT * FROM Customer WHERE EXISTS(SELECT * FROM Accounts
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.
SELECT * FROM Customer WHERE CUSTOMERID IN (SELECT CUSTOMERID FROM Accounts);
IN Vs EXISTS Operators
|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.|
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?
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?
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”?
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.