Exists And Not Exists In SQL Server

EXISTS is a logical operator that is used to check the existence, it is a logical operator that returns boolean result types as true or false only.

 

It will return TRUE if the result of that subquery contains any rows otherwise FALSE will be returned as result. We can use it within IF conditions or Sub Queries.

 

EXISTS takes subquery as an argument like EXISTS (Sub Query).

 

USING WITH IF

IF EXISTS (
   SELECT [ COLUMN_NAME ]
   FROM [ TABLE_NAME ]
   WHERE [ COLUMN_NAME ] IS NOT NULL
)
BEGIN
   --QUERY TO DO ( SELECT [ID] FROM [TABLE_NAME] WHERE [ COLUMN_NAME ] IS NOT NULL)
END

Example

 

In the below query exists will check if there is any row where [NAME] is not null, then it will return true and pass the condition,

 

 

USING WITH SUBQUERY

SELECT [ID]
FROM [TABLE_NAME]
WHERE EXISTS (
   SELECT [ COLUMN_NAME ]
   FROM [ TABLE_NAME ]
   WHERE [ COLUMN_NAME ] IS NOT NULL
)

Example

 

In the below query, using exists with subquery if subquery contains any rows it will return true and the query will return data from the table,

 

 

Note

When we are using EXISTS operator in a subquery, it will return true even when the subquery return NULL, as showing below in the example,

SELECT [ COLUMN_NAME ]
FROM [ TABLE_NAME ]
WHERE EXISTS (
   SELECT NULL
)

In the above query EXISTS will return true and the query will return all data without any condition or filter. 

 

 

Using NOT EXISTS

 

NOT EXISTS is also a logical operator that returns boolean result types as true or false only. NOT EXISTS works the opposite of EXISTS.

 

It will return TRUE if the result of that subquery does not contain any rows otherwise FALSE will be returning as result. We can use it within IF conditions or Sub Queries.

 

NOT EXISTS takes subquery as an argument like: NOT EXISTS (Sub Query).

 

USING WITH IF

IF  NOT EXISTS (
   SELECT [ COLUMN_NAME ]
   FROM [ TABLE_NAME ]
   WHERE [ COLUMN_NAME ] IS NULL
)
BEGIN
   --QUERY TO DO ( SELECT [ID] FROM [TABLE_NAME] WHERE [ COLUMN_NAME ] IS NOT NULL)
END

Example

 

In the below query condition satisfied when the subquery returns zero/ no rows.

 

 

USING WITH SUBQUERY 

SELECT [ID]
FROM [TABLE_NAME]
WHERE NOT EXISTS (
   SELECT [ COLUMN_NAME ]
   FROM [ TABLE_NAME ]
   WHERE [ COLUMN_NAME ] ='Some Value'
)

Example

 

In the below query when the subquery returns no rows then only the condition is satisfied.

 

 

  

I hope this article will give you an overview of EXISTS & NOT EXISTS in SQL Server.