inner join vs left join - which is faster for the same result?

Question

If both inner join and left join can achieve the same result, which one is faster and has better performance (especially on large data)?

inner join:

SELECT *
FROM Table_A A
INNER JOIN Table_B B
ON A.PK_A = B.PK_B

left join:

SELECT *
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK_A = B.PK_B

WHERE A.PK_A = B.PK_B

Table A:

PK_A    User
1   FOX
2   COP
3   TAXI
6   WASHINGTON
7   DELL
5   ARIZONA
4   LINCOLN
10  LUCENT

Table_B:

PK_B    Message
1   TROT
2   CAR
3   CAB
6   MONUMENT
7   PC
8   MICROSOFT
9   APPLE
11  SCOTCH

Any ideas? How can I test their performance on a large data?

Second one is non-nonsensical. The where clause will make your left join behave as an inner join. — Gurwinder Singh, Feb 17 '17 at 12:34
You test this the same way you test anything else. Get the time, in milliseconds before and after you run the query, and subtract. — Dan Bracuk, Feb 17 '17 at 12:36
I would point you to [this](http://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server) question. — Asier Azkuenaga, Feb 17 '17 at 12:44
check it out http://stackoverflow.com/questions/17100819/left-join-significantly-faster-than-inner-join — venky513, Feb 17 '17 at 12:47

score 14 · Accepted Answer · answered Feb 17 '17 at 12:50

14

By adding the WHERE A.PK_A = B.PK_B the query optimizer is smart enough to understand that you wanted an inner join and use that instead. So they will have the same performance since the same execution plan will be created.

So never use a left join with where on the keys when you want an inner join, it will just be frustrating to maintain and understand.

answered Feb 17 '17 at 12:50

Magnus

45,362
8
80
118

The right answer. An outer join with conditions in `WHERE` instead of `JOIN ON` will be treated as `INNER JOIN` – Rahul Feb 17 '17 at 12:58
1

@DanBracuk As I stated in my answer: _they will have the same performance since the same execution plan will be created_ – Magnus Feb 17 '17 at 14:17

score 3 · Answer 2 · answered Feb 17 '17 at 12:35

Left join and inner join solves different purposes. You can check the performance of query by executing execution plan. It will tell What all indexes query is using, how many rows it's scanning etc. There are very good tutorial available for the same on vendor's website.

score 2 · Answer 3 · answered Feb 17 '17 at 12:37

2

First of all, Inner join and left join are not same. INNER JOIN gets all records that are common between both tables

LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL.

So obviously in terms of performance, Inner Join is faster. Hope it will help you :)

answered Feb 17 '17 at 12:37

CSK

29
2

1

In theory, inner joins will always be faster. In real life, I have co-workers who tell me they have observed better performance from left joins. – Dan Bracuk Feb 17 '17 at 12:40
1

It depends on what exactly you want to retrieve. If you want to get the data only from the left table then go with LEFT Join it will be faster but in case if you want all the data which is common in both tables then INNER Join is much more efficient than LEFT – CSK Feb 17 '17 at 12:50
@DanBracuk, running the left join right after the inner join - or? – jarlh Feb 17 '17 at 12:50
@jarlh, I don't know. They were not my observations. – Dan Bracuk Feb 17 '17 at 12:56
1

@DanBracuk, I just wanted to point out that you can get misleading results depending on how you do the performance tests. – jarlh Feb 17 '17 at 13:01

inner join vs left join - which is faster for the same result?

3 Answers3