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.


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

4 comments:

Piwopx said...

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.

Madhivanan said...

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

Anonymous said...

Yes, I did.

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

Pravin said...

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