When using multiple joins in SQL, is it faster to join everything to table A , or join Table A to Table B, Table B to Table C, etc?

Question

To help clarify, here's some code:

Method 1

SELECT * FROM tableA a  
JOIN tableB b ON a.id=b.id  
JOIN tableC c ON a.id=c.id   
JOIN tableD d ON a.id=d.id

Method 2

SELECT * FROM tableA a      
JOIN tableB b ON a.id=b.id  
JOIN tableC c ON b.id=c.id  
JOIN tableD d ON c.id=d.id

try it and find out, but i doubt it makes much diff. i was taught that joining on the WHERE with two conditions would be fastest, but with sqlserver, i think it's all compiled into semi-equivalent bytecode before execution anyway. — dandavis, Jul 01 '15 at 19:14
Thanks for the answer, but what do you mean by that last part? — Aaron Yofin, Jul 01 '15 at 19:17
i mean that unlike in the 1980s, the sql instructions are not interpreted and strictly followed, but rather compiled into low-level code that will do the same thing your instructions demand. that means than different sql statements can run the exact same code against the DB. i would not be surprised if the optimization part of that translation obscured any performance benefits of one syntax over another. — dandavis, Jul 01 '15 at 19:21
@Kritner They're all joined on, say, VENDOR_KEY, but a is about sales, b is about location, etc. Would that allow equivalent results? — Aaron Yofin, Jul 01 '15 at 19:21
@dandavis I meant the Where with two conditions. Like this? JOIN b , JOIN c, WHERE a.id=b.id, AND b.id=c.id? — Aaron Yofin, Jul 01 '15 at 19:22
ah, that's just listing the tables in from and using a where to join, ala `FROM tableA as a , tableB as b WHERE a.id=b.id`. that pattern also tend to be more portable to different DBMSs... — dandavis, Jul 01 '15 at 19:24
@Fabio Are you talking about my original question or the comment immediately before yours? If its the former, take a look at the latter. — Aaron Yofin, Jul 01 '15 at 19:27
@Kritner, Fabio, are you *SURE* it will have different results? These are all `INNER JOIN`s — Amit, Jul 01 '15 at 19:28
@dandavis, what a benefit/difference between `WHERE` and `JOIN`? — Fabio, Jul 01 '15 at 19:28
@Fabio Oh ok. I'm gonna wander off and sit down and try to understand why / how. Thanks! — Aaron Yofin, Jul 01 '15 at 19:29
@Fabio: the benefit is mainly in portability (JOIN can have slightly different uses in different DBMSs), and in some systems (probably not sqlserver for reasons discussed) higher performance. — dandavis, Jul 01 '15 at 19:34
INNER JOINS are both associative and commutative, i.e. the order does not matter, which is why the query planner can decide on the best order (and does in many RDBMS). See also https://stackoverflow.com/questions/9614922/does-the-join-order-matter-in-sql — dhke, Jul 01 '15 at 19:41

score 1 · Answer 1 · answered Jul 01 '15 at 21:38

THERE IS NO DIFFERENCE.

Keep in mind, that databases are based on mathematical set theory. And in terms of set theory, these joins are equal.

Therefore, if you look at the actual query execution plan, you will see that SQL server is even reorganizing joins, and it might rearrange them in a completely other way.

For example, if a table contains only 10 records, then this table is often taken for the first join, because by cutting away only 5 records, you can already cut down 50% of the whole result set.

The database is maintaining some statistics about number of records and distribution of the content. With these statistics, the query engine can make a very good "guess" which order would be the fastest one.

You can influence this behaviour by using query hints, but this is only useful in very rare situations.

When using multiple joins in SQL, is it faster to join everything to table A , or join Table A to Table B, Table B to Table C, etc?

1 Answers1