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
  1. IF EXISTS (  
  2.    SELECT [ COLUMN_NAME ]  
  3.    FROM [ TABLE_NAME ]  
  4.    WHERE [ COLUMN_NAME ] IS NOT NULL  
  5. )  
  6. BEGIN  
  7.    --QUERY TO DO ( SELECT [ID] FROM [TABLE_NAME] WHERE [ COLUMN_NAME ] IS NOT NULL)  
  8. 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,
 
Exists And Not Exists In SQL Server
 
USING WITH SUBQUERY
  1. SELECT [ID]  
  2. FROM [TABLE_NAME]  
  3. WHERE EXISTS (  
  4.    SELECT [ COLUMN_NAME ]  
  5.    FROM [ TABLE_NAME ]  
  6.    WHERE [ COLUMN_NAME ] IS NOT NULL  
  7. )  
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,
 
Exists And Not Exists In SQL Server
 
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,
  1. SELECT [ COLUMN_NAME ]  
  2. FROM [ TABLE_NAME ]  
  3. WHERE EXISTS (  
  4.    SELECT NULL  
  5. )  
In the above query EXISTS will return true and the query will return all data without any condition or filter. 
 
Exists And Not Exists In SQL Server
 
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
  1. IF  NOT EXISTS (  
  2.    SELECT [ COLUMN_NAME ]  
  3.    FROM [ TABLE_NAME ]  
  4.    WHERE [ COLUMN_NAME ] IS NULL  
  5. )  
  6. BEGIN  
  7.    --QUERY TO DO ( SELECT [ID] FROM [TABLE_NAME] WHERE [ COLUMN_NAME ] IS NOT NULL)  
  8. END  
Example
 
In the below query condition satisfied when the subquery returns zero/ no rows.
 
Exists And Not Exists In SQL Server
 
USING WITH SUBQUERY 
  1. SELECT [ID]  
  2. FROM [TABLE_NAME]  
  3. WHERE NOT EXISTS (  
  4.    SELECT [ COLUMN_NAME ]  
  5.    FROM [ TABLE_NAME ]  
  6.    WHERE [ COLUMN_NAME ] ='Some Value'  
  7. )  
Example
 
In the below query when the subquery returns no rows then only the condition is satisfied.
 
 Exists And Not Exists In SQL Server
  
I hope this article will give you an overview of EXISTS & NOT EXISTS in SQL Server.