Many a times we have requirements to check existence of a particular value in a particular table. There could be several ways to accomplish it viz. using left join and sub-query. All of us know that sub-queries are not optimized way while dealing with large volume of data. So what is the alternative to the Left Join which could also cause performance issues if you are dealing with very large set of data? The answer is EXISTS() and NOT EXISTS() functions.

These functions are designed specifically for this purpose to check the existence. So why to not use them? I am not saying that these functions will solve all your problems, but yes, if used wisely and effectively you can gain much more from them.

One thing I would like to suggest when you are using these functions – Don’t supply the SELECT * as an argument to the function instead just supply SELECT 1. It will save IO which in turn help query to perform much better.

Here is the sample code for the various approaches :

Consider we have 2 tables – one for Product and another for Product Transaction. We want to write a query which should return all the products from Product table which does not exists in the ProductTransaction table.

Approach 1 – Using Left Join

Select Product.*
From Product
	Left Join ProductTransaction
		On ProductTransaction.ProductID = Product.ProductID
Where IsNull(ProductTransaction.TransactionID, 0) = 0

Approach 2 – Using Left Join

Select Product.*
From Product
	Left Join ProductTransaction
		On ProductTransaction.ProductID = Product.ProductID
Where ProductTransaction.TransactionID Is Null

Approach 3 – Using Sub-Query

Select Product.*
From Product
Where ProductID Not In (Select ProductID From ProductTransaction)

Approach 4 – Using Not Exists()

Select Product.*
From Product
Where Not Exists (Select 1 From ProductTransaction Where ProductTransaction.ProductID = Product.ProductID)

We have seen 4 approaches to accomplish the same task. Evaluate them based on your need before using it in the Production.

• SQL Server