12

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.

bummi
  • 27,123
  • 14
  • 62
  • 101
Kartic
  • 2,935
  • 5
  • 22
  • 43
  • 4
    Not in any sane database system. Even if you meant to change the order of the tables. All modern databases keep statistics about data volumes for this reason. – Laurence Oct 26 '13 at 21:43
  • Aliases has no affect on the query. What is your concern? – Yosi Dahari Oct 26 '13 at 21:44
  • related: http://stackoverflow.com/questions/9614922/does-the-join-order-matters-in-sql – Tim Schmelter Oct 26 '13 at 21:45
  • I'm sorry, I've edited my question.. please check my question again. – Kartic Oct 26 '13 at 21:46
  • You should also specify which DBMS you're using. Performance depends on the quality of the query optimizer, so it's hard to generalize over all databases. – Barmar Oct 26 '13 at 21:47
  • Also related: http://stackoverflow.com/questions/1018952/condition-within-join-or-where/1018979#1018979 – Bill Karwin Oct 26 '13 at 21:51
  • We are using Microsoft SQL server 2005. Updated my answer accordingly. – Kartic Oct 26 '13 at 21:52
  • Use the command to display the execution plan. This will show you if the plans for both statements are the same. – Barmar Oct 26 '13 at 21:52
  • possible duplicate of [SQL Server 2005 - Order of Inner Joins](http://stackoverflow.com/questions/1334944/sql-server-2005-order-of-inner-joins) – bummi Nov 15 '14 at 18:01

1 Answers1

7

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

Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44