Most optimal order (of joins) for left join

Posted by Ram on Stack Overflow See other posts from Stack Overflow or by Ram
Published on 2010-06-11T18:30:24Z Indexed on 2010/06/11 18:43 UTC
Read the original article Hit count: 236

Filed under:
|
|
|

I have 3 tables Table1 (with 1020690 records), Table2(with 289425 records), Table 3(with 83692 records).I have something like this

SELECT * FROM Table1 T1 /* OK fine select * is bad when not all columns are needed, this is just an example*/
LEFT JOIN Table2 T2 ON T1.id=T2.id
LEFT JOIN Table3 T3 ON T1.id=T3.id

and a query like this

SELECT * FROM Table1 T1
LEFT JOIN Table3 T3 ON T1.id=T3.id
LEFT JOIN Table2 T2 ON T1.id=T2.id

The query plan shows me that it uses 2 Merge Join for both the joins. For the first query, the first merge is with T1 and T2 and then with T3. For the second query, the first merge is with T1 and T3 and then with T2.

Both these queries take about the same time(40 seconds approx.) or sometimes Query1 takes couple of seconds longer.

So my question is, does the join order matter ?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2008