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.
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.
Now, run the script using EXCEPT :
SELECT t.ID, t.Name FROM #temp1 t EXCEPT SELECT t.ID, t.Name FROM #temp2 t
Now, let’s check the execution plan for these two queries:
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
Really very helpful for beginners!! Thank you Kapil!!
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.