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:
All Records of CourseseInactive look like this:
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:
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:
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.
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