3

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?

Improve this question
6
  • 5
    Why do LEFT JOIN if you want an INNER JOIN...? Commented Feb 17, 2017 at 12:33
  • 2
    Second one is non-nonsensical. The where clause will make your left join behave as an inner join. Commented Feb 17, 2017 at 12:34
  • 1
    You test this the same way you test anything else. Get the time, in milliseconds before and after you run the query, and subtract. Commented Feb 17, 2017 at 12:36
  • I would point you to this question. Commented Feb 17, 2017 at 12:44
  • check it out stackoverflow.com/questions/17100819/… Commented Feb 17, 2017 at 12:47

3 Answers 3

Reset to default
15

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.

enter image description here 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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The right answer. An outer join with conditions in WHERE instead of JOIN ON will be treated as INNER JOIN
@DanBracuk As I stated in my answer: they will have the same performance since the same execution plan will be created
3

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.

Improve this answer

Comments

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 :)

Improve this answer

5 Comments

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.
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
@DanBracuk, running the left join right after the inner join - or?
@jarlh, I don't know. They were not my observations.
@DanBracuk, I just wanted to point out that you can get misleading results depending on how you do the performance tests.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.