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.
