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.

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

Img_Except1

Now first run the script with LEFT JOIN:

We get ID 3 in the result set.

Img_Except2

Now, run the script using EXCEPT :

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