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.
How to Use SQL Server EXISTS?
First of all, We have to create a table for performing operations. So here we took two tables which are CourseseActive and CourseseInactive. If you don’t know how to create a table then refer to this Create a table in SQL Server.
All Records of tables look like this:
CourseseInactive Table
All Records of CourseseInactive look like this:
CourseseInactive Table
Example 1:
Let’s check the courseDetails that got Inactive out of the Active courses:
SELECT * FROM CoursesActive
WHERE EXISTS
(SELECT * FROM CoursesInactive
WHERE CoursesActive.courseId = CoursesInactive.courseId)
The Result Looks Like this:
All record with same CourseId
Explanation:
It retrieves all columns (*) from the CoursesInactive table where the courseId in CoursesActive matches the courseId in CoursesInactive. This is done through the condition CoursesActive.courseId = CoursesInactive.courseId. The EXISTS keyword is used in the outer query’s WHERE clause. It checks if the subquery returns any rows. If the subquery returns at least one row, the condition is considered true, and the corresponding row from CoursesActive is included in the result set.
Example 2:
Let’s say we have a Query to check when we use SELECT NULL in subquery as it returns true.
SELECT courseName FROM CoursesActive
WHERE EXISTS (SELECT NULL)
The Result Looks Like this:
Result
Explanation: In the above query ,selects the courseName column from the CoursesActive table for all rows, regardless of the content of the subquery. The use of EXISTS with SELECT NULL in this context is not typically practical for filtering records based on conditions; it essentially acts as a way to check if there are any rows in the subquery result.
Advantage of Using EXISTS Operator
- It is a logical operartor.
- EXISTS Operator is widely used to test the existance of matched records in another tables.
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:
Result with IN Operator
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.
EXISTS vs JOIN
JOINS are used for combined multiple tables based on a particular condition whereas the EXISTS operator returns only true or false when there is exists any matched rows in the subquery. The EXISTS operator is just used to test the existence in another table where as the JOIN is used for extending the table with the results from another table.
Let’s see an example of INNER JOIN on the two tables based on the match of courseId :
SELECT * FROM CoursesActive
INNER JOIN CoursesInactive
ON CoursesActive.courseId = CoursesInactive.courseId
The result looks like this:
Result with INNER Join
Explanation: The INNER JOIN is done on the tables coursesActive and coursesInactive based on the condition of the match on the courseId, we can see that both tables got combined based on that condition.
Conclusion
We have seen in the above examples, how we can use EXISTS Operator in different manners to solve a query. Basically, it is used to check whether the particular row meets the condition specified in the subquery. The EXISTS operator also optimizes query performance by stopping processing once a match is found contributing to its efficiency.