EXISTS vs IN Operator
The EXISTS operator is used to check whether the subquery returned any rows or not, if there are any rows returned then it will be true, whereas the IN operator matches with every record in the subquery if there is a match it returns the true. We can even give a direct set of values for comparison for IN operator in the form of an array and the subquery is not mandatory but in the case of EXISTS, the subquery is mandatory.
Using the IN operator we can exempt writing multiple OR statements.
Let’s see an example using the IN operator to check the courses that got inactive:
SELECT * FROM CoursesActive
WHERE courseId IN
(SELECT courseId from CoursesInActive)
The Result looks like:
Explanation: The subquery returns a list of studentIds and the main query compares the studentId with the list if there exists a studentId in the list returned by the subquery those records will be listed.
SQL Server EXISTS
The EXISTS operator is used when we are dependent on another subquery which can be in the same table or a different table. When the subquery returns any rows the EXISTS operators return true otherwise false. EXISTS Operator simply checks whether the subquery returns any row. It gives true or False based on the existence of rows. In this article, you will get a clear idea about EXISTS Operator in SQL Server.
Syntax:
SELECT * FROM table_name WHERE
column_name EXISTS (subquery)
Explanation: In the above query we fetched all the records if the subquery exists. A SELECT statement that returns rows. The EXISTS
operator is used to check if the subquery returns any rows.
Contact Us