Difference between IF EXISTS and IF NOT EXISTS in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

I am confused between if exists and if not exists in sql. 

Can anyone provide me the sample query in SP so that I can understand it. 

You are viewing reply posted by: dharmendr 3 months ago.
Posted 3 months ago

Hi chetan,

Basically IF EXISTS returns true if the query return 1 or more rows.

IF NOT EXISTS returns false if the query return 1 or more rows.

Both statements will return a boolean true/false result.

EXISTS returns true if the result set IS NOT empty.

NOT EXISTS returns true if the result set IS empty.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

SQL

-- IF EXISTS
IF EXISTS (SELECT * FROM Customers WHERE CustomerID ='ALFKI') 
BEGIN
   SELECT 'Exist'
END
ELSE
BEGIN
    SELECT 'Not Exist'
END
GO
-- IF NOT EXISTS
IF NOT EXISTS (SELECT * FROM Customers WHERE CustomerID ='ALFKI') 
BEGIN
   SELECT 'Not Exist'
END
ELSE
BEGIN
	SELECT 'Exist'
END

In both the cases the output will be Exist.