Frequently, we come across a scenario where we need to check the existence of a record and based on it perform some action. This article explains how to use the EXISTS clause to check the existence of a record in a table. There are multiple options for checking the existence of a record, but EXISTS clause is the best option in terms performance. Basically the Exists clause checks the existence of a record and it stops processing further records after finding the first instance of the record which matches the criteria

This article covers the following examples:

• Using EXISTS clause in the IF statement to check the existence of a record
• Using EXISTS clause in the CASE statement to check the existence of a record
• Using EXISTS clause in the WHERE clause to check the existence of a record
• EXISTS clause having subquery joining multiple tables to check the record existence in multiple tables

To demonstrate this let us create a Customer and Order table as shown in the below image by the following script:

USE TEMPDB
GO
--Create Customer Table
CREATE TABLE dbo.Customer
(CustId INT, CustName NVARCHAR(50))
--Create Order Table
CREATE TABLE dbo.[Order]
(OrderId INT, CustId INT, OrderTotal Money)
GO
--Insert sample records into Customer table 
INSERT INTO dbo.Customer
VALUES(1, 'Basavaraj Biradar'),
 (2,'Kalpana Biradar')
 --Insert sample records into Customer table
INSERT INTO dbo.[Order]
VALUES(100,1, 1000), (101,1,9500)
GO

[ALSO READ] How to check if a Database exists

EXAMPLE 1: Using EXISTS clause in the IF statement to check the existence of a record

Below example script checks the existence of the customer record with CustId = 2 in the IF statement

DECLARE @CustId INT = 2
IF EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK)
          WHERE CustId = @CustId)
	BEGIN
		PRINT 'Record Exists'
	END
ELSE
	BEGIN
		PRINT 'Record doesn''t Exists'
	END

RESULT:

[ALSO READ] How to check if a Table exists

EXAMPLE 2: Using EXISTS clause in the CASE statement to check the existence of a record

DECLARE @CustId INT = 2
SELECT (CASE WHEN EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK)
    WHERE CustId = @CustId) THEN 'Record Exists'
    ELSE 'Record doesn''t Exists' END) AS [Employee?]

RESULT:

[ALSO READ] How to check if Temp table exists

EXAMPLE 3: Using EXISTS clause in the WHERE clause to check the existence of a record

DECLARE @CustId INT = 1
SELECT 'Record Exist'
WHERE EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK)
       WHERE CustId = @CustId)

RESULT:

[ALSO READ] How to check if a Stored Procedure exists in Sql Server

EXAMPLE 4: EXISTS clause having sub query joining multiple tables to check the record existence in multiple tables

DECLARE @CustId INT = 1
IF EXISTS(SELECT 1 
          FROM dbo.Customer C WITH(NOLOCK) 
			INNER JOIN dbo.[Order] O WITH(NOLOCK)
				ON C.CustId = O.CustId	
		  WHERE C.CustId = @CustId)
	BEGIN
		PRINT 'Record Exists'
	END
ELSE
	BEGIN
		PRINT 'Record doesn''t Exists'
	END
GO

RESULT:

[ALSO READ] :
How to check if a Database exists
How to check if a Table exists
How to check if a Stored Procedure exists in Sql Server
How to check if a View exists
How to check if Temp table exists