SQL - IN vs EXISTS

---

In general, to make a query easy or avoid the use of multiple OR conditions in the query, we used IN. The IN operator in SQL allows you to match an expression against a list of values. where EXISTS is the operator to return the Boolean value that is true or false. Generally, if EXISTS checks that one or more rows are available in the sub query, it will return true otherwise false.

SQL - IN

The IN operator is used to check whether a specific value matches any other value in the set of values. These set of values can either be specified separately or be returned by any sub query.

IN is used with the WHERE clause, and it allows us to use multiple values. It reduces the use of multiple OR conditional statements in the SELECT, UPDATE, INSERT, and DELETE queries.

Let us say that we have a table named Customers and we want to see the details of the customers based on their IDs. Here, we can use the IN operator to fetch the details of these IDs with the help of the WHERE clause.

Syntax

Following is the syntax of the IN operator −

SELECT Columns_name FROM table_name WHERE column_name IN (val1, val2, …, valN);

In the above syntax, the column_name matches every value (val1, val2, ... valN). If the matches occur, IN is obtained to be true; otherwise, IN is obtained to be false.

Example

In the following example, we are creating a SQL query that will fetch the customer's name and salary from the customers table whose ID is 1, 2, or 3.

Following is the customers table −

+------+----------+------+-----------+--------+
| ID   | NAME     | AGE  | ADDRESS   | SALARY |
+------+----------+------+-----------+--------+
|    1 | Ramesh   |   32 | Ahmedabad |   2000 |
|    3 | kaushik  |   23 | Kota      |   2000 |
|    4 | Chaitali |   25 | Mumbai    |   6500 |
|    2 | Aman     |   23 | Ranchi    |  40000 |
+------+----------+------+-----------+--------+

Following is the SQL query to fetch the details −

SELECT Name, Salary FROM customers WHERE ID IN(1, 2, 3);

Output

When we execute the above SQL select query, we get the name and salary of the customer whose ID is 1, 2, or 3.

+---------+--------+
| Name    | Salary |
+---------+--------+
| Ramesh  |   2000 |
| kaushik |   2000 |
| Aman    |  40000 |
+---------+--------+

SQL - EXISTS

The EXISTS operator is used to look for the existence of a row in a given table that satisfies a set of criteria. It is a Boolean operator that compares the result of the subquery to an existing record and returns true or false.

The retuned value is true, if the subquery fetches single or multiple records and false if no record is matched. It is used in the combination of the subquery and checks whether a row is returned through this subquery or not.

EXISTS operators follow the query’s efficiency features. When the first true event is detected, it will automatically stop further processing.

We can use the EXISTS operator with the SELECT, UPDATE, INSERT and DELETE queries.

syntax

SELECT column_name FROM table_name
WHERE EXISTS(
SELECT column_name FROM table_name
WHERE condition );

Example

In the following SQL query, we are fetching the name and age of the customers whose age matches from the employees table.

Following is the customers table −

+------+----------+------+-----------+--------+
| ID   | NAME     | AGE  | ADDRESS   | SALARY |
+------+----------+------+-----------+--------+
|    1 | Ramesh   |   32 | Ahmedabad |   2000 |
|    3 | kaushik  |   23 | Kota      |   2000 |
|    4 | Chaitali |   25 | Mumbai    |   6500 |
|    2 | Aman     |   23 | Ranchi    |  40000 |
+------+----------+------+-----------+--------+

Following is the employees table −

+-----+-------+-----+--------+------------+
| EID | NAME  | AGE | CITY   | C_Phone    |
+-----+-------+-----+--------+------------+
|   1 | Aman  |  23 | Ranchi | 1122567800 |
|   2 | Vivek |  25 | Ranchi | 9304567890 |
+-----+-------+-----+--------+------------+

Following is the SQL query to display the records −

SELECT NAME, AGE FROM customers WHERE EXISTS( SELECT * FROM employeeS WHERE customers.AGE = employees.AGE);

Output

When the above SQL query is executed, we get the name and age of the customers using the exists operator if the age columns of both tables match.

+----------+------+
| NAME     | AGE  |
+----------+------+
| kaushik  |   23 |
| Chaitali |   25 |
| Aman     |   23 |
+----------+------+

IN vs EXISTS

Following is the differences between IN and EXISTS −

S.No.	IN	EXISTS
1	It is applied to the SQL query to remove the multiple OR conditions.	It is used to find whether the data in the subquery truly exist.
2	It executes all values contained within the IN block.	If the value is matched, display the details of the given value. It will terminate the further process if the condition is met.
3	It can be used for the comparison of a null value because it returns true, false, and a null value.	It cannot be used for the comparison of a null value because it returns only true and false values.
4	It can be used with subqueries as well as with values.	It can be used only with subqueries.
5	It executes faster when the subquery is smaller.	It executes faster when the subquery is larger. because it is more efficient than IN and returns only a Boolean value.