CODEWITHSUNDEEP
mysqlsqljoinleft-joininner-join
Run
Run

Reputation: 57276

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

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?

Upvotes: 3

Views: 18709

Answers (3)

Magnus
Magnus

Reputation: 46997

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.

Upvotes: 14

CSK
CSK

Reputation: 29

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

Upvotes: 2

Aman Goyal
Aman Goyal

Reputation: 393

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.

Upvotes: 3

Related Questions