SQL Server | How to select all records from one table that do not exist in another table? – Different Methods & the Best Method

Today I will talk about how we can fetch records from one table that do not exist in another table. What are the methods available to us and which one these is the best one.? Let’s first create some sample data. Here the ask is find records from tablesA that do not exist in tablesB.

--

CREATE TABLE tablesA
(
	ID INT 
)
GO

INSERT INTO tablesA VALUES (2),(2),(3),(3)
GO

CREATE CLUSTERED INDEX Ix_ID ON tablesA(Id)

CREATE TABLE tablesB
(
	ID INT
)
GO

INSERT INTO tablesB VALUES (2),(2),(NULL)
GO

CREATE CLUSTERED INDEX Ix_ID ON tablesB(Id)

--

Now go through some of the queries written below to fetch records from tablesA that do not exist in tablesB.

--


--Method 1 | NOT IN 
SELECT ID FROM tablesA WHERE ID NOT IN ( SELECT ID FROM tablesB  WHERE tablesA.ID = tablesB.ID )


--Method 2 | NOT EXISTS
SELECT ID FROM tablesA WHERE NOT EXISTS ( SELECT ID FROM tablesB WHERE tablesA.ID = tablesB.ID )


--Method 3 | LEFT JOIN
SELECT tablesA.ID FROM tablesA LEFT JOIN tablesB ON tablesA.ID = tablesB.ID WHERE tablesB.ID IS NULL

--

First lets check the outputs of the above queries. All the above queries are giving proper results.

Pawan Khowal – Find Records that doesn’t exists

Now let’s compare the execution plans of all the queries written above then only we can say that which one is performing best out of the lot.

Pawan Khowal – Find Records that doesn’t exists – Execution Plans

Okay so we got the execution plan, the first two execution plans are identical. Developer/DBA normally don’t use correlation in inside part of the sub query “WHERE tablesA.ID = tablesB.ID”. The last execution plan we have Nested Loop join with a filter operator. The cost taken by all the execution plans are same 33%. This means all are performing almost same in this scenario at least. Now comes the question which one is the best one. Well in this case they are behaving almost same. I always prefer the second one. It scales well and gives out proper output all the time.

That’s all folks; I hope you’ve enjoyed the article and I’ll see you soon with more articles.

Thanks!

Pawan Kumar Khowal

MSBISKills.com