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


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


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