January 07, 2013

SQL Server - Which is faster INNER JOIN or LEFT JOIN?




You may be interested to know which is faster – the LEFT JOIN or INNER JOIN. Well, in general INNER JOIN will be faster because it only returns the rows matched in all joined tables based on the joined column. But LEFT JOIN will return all rows from a table specified LEFT and all matching rows from a table specified RIGHT.

We can analyze this using the STATISTICS TIME ON option. Consider the sys.objects and sys.columns catalog views and join them using INNER and LEFT joins and see the result

set statistics time on
select
    t1.object_id, t2.object_id
from
    sys.objects as t1 inner join sys.columns as t2
on t1.object_id=t2.object_id

select
    t1.object_id, t2.object_id
from
    sys.objects as t1 left join sys.columns as t2
on t1.object_id=t2.object_id

set statistics time off

The result is of SET STATISTICS TIME ON is

sql-join-inner-left
Note:  You may think here that since the LEFT join returns more rows, so it is taking more time. Now eliminate the NULL object_ids from sys.columns and observe the result

set statistics time on
select
    t1.object_id, t2.object_id
from
    sys.objects as t1 inner join sys.columns as t2
on t1.object_id=t2.object_id

select
    t1.object_id, t2.object_id
from
    sys.objects as t1 left join sys.columns as t2
on t1.object_id=t2.object_id
where
    t2.object_id is not null

set statistics time off

The result is

sql-join-inner-left-2
So even though they both return the same number of rows, INNER JOIN is still faster.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

4 Responses to "SQL Server - Which is faster INNER JOIN or LEFT JOIN?"
  1. Piwopx said...
    January 11, 2013 at 1:34 AM

    Hi.

    Try to invert the order of execution. In my tests the first query always is faster then the second one.

    In other test I write 4 times the same query. These are the results:
    Tiempos de ejecución de SQL Server:
    Tiempo de CPU = 0 ms, tiempo transcurrido = 0 ms.

    (659 filas afectadas)

    Tiempos de ejecución de SQL Server:
    Tiempo de CPU = 0 ms, tiempo transcurrido = 1 ms.

    (659 filas afectadas)

    Tiempos de ejecución de SQL Server:
    Tiempo de CPU = 0 ms, tiempo transcurrido = 114 ms.

    (659 filas afectadas)

    Tiempos de ejecución de SQL Server:
    Tiempo de CPU = 0 ms, tiempo transcurrido = 137 ms.

    Maybe you need a better way to meassure execution time.

  2. Madhivanan said...
    January 16, 2013 at 12:36 AM

    Piwopx, Ok. Did you run the same for LEFT JOIN too?

  3. Anonymous said...
    January 22, 2013 at 4:22 AM

    Yes, I did.

    If I run the execution plan of both queries the result is exactly the same.

  4. Pravin said...
    February 13, 2013 at 10:28 PM

    Since this article is misleading, could you please edit or removed it?

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions