IN vs EXISTS in SQL
SQL stands for Structured Query Language. It is used for storing data in databases, modifying or manipulating data in databases and retrieving data from databases. We can say that SQL manages data in a relational database management system (RDBMS).
The instructions that are used to communicate with a database in order to perform tasks, functions, and queries with data are called SQL Statements. SQL is not case-sensitive. Generally, SQL keywords are written in uppercase.
IN Operator in SQL :
To match an expression against a list of values, SQL provides 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, value2, ...);
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 :
Now we want to see the details of the customers whose id can be 1 or 2 or 5 or 6 or 7, we can use IN operator & give a set of values to check upon.
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 :
Now to see all the customers who’s any kind of account exists, we can make use of exists keyword as :
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, one row is returned & thus the sub-query evaluates to be true & our outer query (Select * from Customer) get executed for that particular customer.
Note: To do the same thing, i.e., to see all the customers who’s any kind of account exists, we can make use of IN keyword also :
The key differences between IN & EXISTS Operator are :
|IN Operator||EXISTS Operator|
|1.||IN can be used as a replacement for multiple OR operators.||To determine if any values are returned or not, we use EXISTS.|
|2.||IN works faster than the EXISTS Operator when If the sub-query result is small.||If the sub-query result is larger, then EXISTS works faster than the IN Operator.|
|3.||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.|
|4.||To check against only a single column, IN operator can be used.||For checking against more than one single column, you can use the EXISTS Operator.|
|5.||The IN operator cannot compare anything with NULL values.||The EXISTS clause can compare everything with NULLs.|
|6.||A direct set of values can be given for comparison.||Cannot compare directly the values, sub-query needs to be given.|