LEFT JOIN vs EXCEPT in Sql Server

Hi all, today I was working on a script in which I have to extract data from a table which is not present in another table. I know that it can be accomplished with the use of Left Join or Except in Sql server.

But which one should I use? Its like a Left Join vs Except in Sql Server.

Then I created a sample tables to test which one is better.

Let’s create table #temp1 and #temp2 and insert some dummy data in these table.

CREATE TABLE #temp1 (ID int, Name varchar(10))
INSERT INTO #temp1 VALUES (1,'A'),(2,'B'),(3,'C')
CREATE TABLE #temp2 (ID int, Name varchar(10))
INSERT INTO #temp2 VALUES (1,'A'),(2,'B'),(4,'D')

After inserting data let’s check that data is inserted properly or not.

Img_Except1

Now first run the script with LEFT JOIN:

SELECT t.ID, t.Name  FROM #temp1 t
LEFT JOIN #temp2 t1 ON t.ID = t1.ID
WHERE t1.ID IS NULL

We get ID 3 in the result set.

Img_Except2

Now, run the script using EXCEPT :

SELECT t.ID, t.Name FROM #temp1 t
EXCEPT
SELECT t.ID, t.Name  FROM #temp2 t

Img_Except3

Now, let’s check the execution plan for these two queries:

Img_Except4

From both these screen we can see that the query that use EXCEPT cost more than LEFT JOIN as it use SORT and TOP things extra.

So, it’s better to use LEFT JOIN in comparison to except.

Note: Query cost may be vary on scale of data volume.

Hope you like the post folks!!

 

Regards,

Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

2 Comments on “LEFT JOIN vs EXCEPT in Sql Server”

  1. Yes but the real test is to examine the statistics time and check that in reality the real elapsed time for except would take longer than rather as indicated by the execution plan. Maybe it does in this case but there are times when the plan percentage does not reflect reality.

    Tony S.

Leave a Reply

Your email address will not be published. Required fields are marked *