Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does INNER JOIN performance depends on order of tables?

A question suddenly came to my mind while I was tuning one stored procedure. Let me ask it -

I have two tables, table1 and table2. table1 contains huge data and table2 contains less data. Is there performance-wise any difference between these two queries(I am changing order of the tables)?

Query1:

SELECT t1.col1, t2.col2
FROM table1 t1
INNER JOIN table2 t2
ON t1.col1=t2.col2

Query2:

SELECT t1.col1, t2.col2
FROM table2 t2
INNER JOIN table1 t1
ON t1.col1=t2.col2

We are using Microsoft SQL server 2005.

like image 525
Kartic Avatar asked Oct 26 '13 21:10

Kartic


People also ask

Does inner join order matter for performance?

Basically, join order DOES matter because if we can join two tables that will reduce the number of rows needed to be processed by subsequent steps, then our performance will improve.

Does table Order Matter inner join?

For INNER joins, no, the order doesn't matter. The queries will return same results, as long as you change your selects from SELECT * to SELECT a.

Does the order of joins affect performance?

JOIN order doesn't matter, the query engine will reorganize their order based on statistics for indexes and other stuff.

Which table should come first in Inner join?

SQL Join StatementThe SELECT ... FROM statement indicates which is the first table, then the second table name is written just after the INNER JOIN keywords. How the two tables should be joined is written in the ON statement. In this case the two tables are joined using the relationship table1.id = table2.id .


1 Answers

Aliases, and the order of the tables in the join (assuming it's INNER JOIN) doesn't affect the final outcome and thus doesn't affect performance since the order is replace (if needed) when the query is executed.

You can read some more basic concepts about relational algebra here: http://en.wikipedia.org/wiki/Relational_algebra#Joins_and_join-like_operators

like image 94
Yosi Dahari Avatar answered Sep 21 '22 12:09

Yosi Dahari