SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

SQL IN vs SQL EXISTS
2021-05-18 22:08:17

SQL IN vs SQL EXISTS

SQL IN vs SQL EXISTS

This article discusses in detail about the IN and the EXISTS operators in SQL. It is a common question between developers that what is the difference between these two operators. To know the difference, let’s first discuss about each of these operators in detail.

IN Operator

The IN Operator is used to get results when a specified value matches a value in a set of values or is returned by an inner query. This operator can also be used with the WHERE clause to specify more than one value. The IN operator is often referred to as the acronym for the OR operator because it reduces the use of multiple OR conditions in SELECT, INSERT, UPDATE or DELETE queries.

In this operator, the subquery is solved first and that result is then used to solve the outer query.

The following is the general syntax of the IN operator:

SELECT column_name
 FROM table_name
 WHERE column_name IN (value1, value2, …);

Example:

Let’s consider the following Customer table.

Cust_idCust_nameCityOccupation
1HarryKolkataBusiness
2RonMumbaiDoctor
3AlbusDelhiEngineer
4DobbyPuneScientist
5SnapeBangaloreStudent

Query:

SELECT *
 FROM Customer    
 WHERE Occupation IN ('Doctor', 'Scientist', 'Engineer');

The above query will return all the customers who are either Doctor or Scientist or Engineer.

Output:

Cust_idCust_nameCityOccupation
2RonMumbaiDoctor
3AlbusDelhiEngineer
4DobbyPuneScientist

EXISTS Operator

The EXISTS operator, unlike the IN operator, returns a Boolean value. This operator checks the result of the inner query and returns a Boolean value i.e., either true or false. If the inner query returns a single or multiple record, then the operator returns true else it returns false when no records are found.

This operator is also efficient as it stops further processing as soon as the first true event is detected.

The following is the syntax of the EXISTS operator:

SELECT column_names    
 FROM table_name    
 WHERE NOT EXISTS (    
 SELECT column_names     
 FROM table_name     
 WHERE condition    
 );

Example:

Let’s consider the following Customer table.

Cust_idNameOccupationAge
101HarryEngineer32
102RonDeveloper30
103DobbyLeader28
104AlbusScientist45
105SnapeCarpenter26
106GingerActor25
107NULLNULLNULL

Let’s consider another table Orders.

Order_idCust_idProduct_nameOrder_date
1101Laptop2021-01-10
2103Desktop2021-02-12
3106IPhone2021-02-15
4104Mobile2021-03-05
5102TV2021-03-20

Query:

SELECT Name, Occupation
 FROM Customer    
 WHERE EXISTS (
 SELECT *
 FROM Orders     
 WHERE Customer.Cust_id = Orders.Cust_id
 );

The above query will return the name and occupation of all the customers who have placed at least one order.

Output:

NameOccupation
HarryEngineer
RonDeveloper
DobbyLeader
AlbusScientist
GingerActor

Difference table between IN and EXISTS operator

The key differences between the IN operator and the EXISTS operator are listed below in a tabular manner:

IN OperatorEXISTS Operator
It minimizes the usage of the OR conditions.It checks the existence of a record in the inner query.
It compares the values of the inner query with the value of the outer query.It does not compare the values between the inner query and the sub query.
It checks all the values inside the block of the IN clause.It stops any further execution as soon as the first true condition is met.
It can return TRUE, FALSE or NULL.It returns either TRUE or FALSE.
It can be used to check NULL values.It cannot be used to check NULL values.
It is used with both subqueries and values.It is used only with subqueries.
Execution is faster when the result of the inner query is less.Execution is faster even when the result of the inner query is large. It is more efficient than the IN operator.
Syntax: SELECT column_names FROM table_name WHERE column_name IN (subquery);Syntax: SELECT column_namesFROM table_nameWHERE [NOT] EXISTS (subquery);

Conclusion:

In this topic, a comparison has been made between the IN operator and the EXISTS operators of SQL. Both the operators do the same operation but their internal working is different. They have different logical working. Any one of them can be selected according to the requirement. But, if the set of data is large, it is always recommended to go for the EXISTS operator.