which way is better for joins? or are both options popular?

Question

which way is better for joins? or are both options popular?

wallywizard

Ten Centuries

Points: 1004
More actions
March 6, 2019 at 6:22 pm

#374077
i have a question about joining tables. I have read online people describing the two methods below..
Method 1 - joining tables in a straight line (see below)
Method 2- joining both tables from the first table (see below)
- Is there a difference regarding output?
- when would I know to use which method? This one confuses the heck out of me.
*please DO NOT comment on how the tables are not related to each other. I randomly selected 3 tables, this is purely just for visual purposes.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

No. It's just a visual representation. Use whatever layout you find easier to understand. (Not that it matters in 2019... database diagrams are gone.

wallywizard Ten Centuries Points: 1004 More actions · Answer 2

so you mean both options will produce the same result? It doesn't matter?

pietlinden SSC Guru Points: 63428 More actions · Answer 3

Like I just said - what you see in Access or SQL Server's relationships window is a visual REPRESENTATION of the relationships. Which way you drag or whatever to create them makes ZERO difference. What determines the direction of the relationship is the cardinality of the columns you're joining on.
You can drag the images of the tables all over the place. As long as you don't change the way the tables are related, it doesn't affect the database (and querying) at all.

DesNorton SSC-Insane Points: 24278 More actions · Answer 4

I suspect that the question is more along the lines of ....

Which is better?

SELECT ... FROM TableA AS a JOIN TableB AS b ON a.ID = b.ID JOIN TableC AS c ON a.ID = c.ID -- NOTE the join to TableA
OR
SELECT ... FROM TableA AS a JOIN TableB AS b ON a.ID = b.ID JOIN TableC AS c ON b.ID = c.ID -- NOTE the join to TableB

How to post data/code on a forum to get the best help.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 5

DesNorton - Wednesday, March 6, 2019 9:35 PM
I suspect that the question is more along the lines of ....
Which is better?
SELECT ... FROM TableA AS a JOIN TableB AS b ON a.ID = b.ID JOIN TableC AS c ON a.ID = c.ID -- NOTE the join to TableA
OR
SELECT ... FROM TableA AS a JOIN TableB AS b ON a.ID = b.ID JOIN TableC AS c ON b.ID = c.ID -- NOTE the join to TableB

In theory, it shouldn't matter, but in practice I would expect the first query to always have TableA in the first join done, whereas in the second query I would not be surprised to see SQL join B and C and only after that join to A.

But overall the number of rows in the tables will have a far bigger effect on exactly how SQL does the joins. Say, for example, that A had 40M rows, B had 20M rows, and C had 5 rows. SQL would almost certainly use C in the first join since that will drastically reduce the number of rows that have to be processed. SQL is trying to get the best efficiency, while of course guaranteeing a 100% accurate result.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 6

wallywizard - Wednesday, March 6, 2019 6:22 PM

In theory, the compiler is allowed to rearrange the joins as long as it effectively computes the same result. The standards state, however, that the result must be the same as if you had done the joins from left to right. You can also put parentheses with the infixed joins.

SELECT ...
FROM ((TableA AS A
    INNER JOIN
    TableB AS B
    ON A.something_col = B.something_col)
     INNER JOIN
     TableC AS C
     ON A.something_col = C.something_col);

This is complicated by the ability to name the intermediate results

SELECT ...
FROM ((TableA AS A
    INNER JOIN
    TableB AS B
    ON A.something_col = B.something_col) AS Foobar
     INNER JOIN
     TableC AS C
     ON Foobar.something_col = C.something_col);

Did you notice a problem? Did the "something_col" in Foobar come from table A or table B? We need a list of column names for foobar in which we specify which table things came from. The scoping rules in SQL are a little trickier than most people think.

Please post DDL and follow ANSI/ISO standards when asking for help.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

ScottPletcher - Thursday, March 7, 2019 8:29 AM
DesNorton - Wednesday, March 6, 2019 9:35 PM
I suspect that the question is more along the lines of ....
Which is better?
SELECT ... FROM TableA AS a JOIN TableB AS b ON a.ID = b.ID JOIN TableC AS c ON a.ID = c.ID -- NOTE the join to TableA
OR
SELECT ... FROM TableA AS a JOIN TableB AS b ON a.ID = b.ID JOIN TableC AS c ON b.ID = c.ID -- NOTE the join to TableB
In theory, it shouldn't matter, but in practice I would expect the first query to always have TableA in the first join done, whereas in the second query I would not be surprised to see SQL join B and C and only after that join to A.
But overall the number of rows in the tables will have a far bigger effect on exactly how SQL does the joins. Say, for example, that A had 40M rows, B had 20M rows, and C had 5 rows. SQL would almost certainly use C in the first join since that will drastically reduce the number of rows that have to be processed. SQL is trying to get the best efficiency, while of course guaranteeing a 100% accurate result.

The way the SQL Server engine works, it evaluates all of the possible ways to join the 3 tables in the query, so even if you write the query the second way, I've seen cases where SQL Server decide the best execution plan is actually the way the first query is written. So it can join things in different orders or differently than you've explicitly specified in the query.

wallywizard Ten Centuries Points: 1004 More actions · Answer 8

DesNorton - Wednesday, March 6, 2019 9:35 PM
I suspect that the question is more along the lines of ....
Which is better?
SELECT ... FROM TableA AS a JOIN TableB AS b ON a.ID = b.ID JOIN TableC AS c ON a.ID = c.ID -- NOTE the join to TableA
OR
SELECT ... FROM TableA AS a JOIN TableB AS b ON a.ID = b.ID JOIN TableC AS c ON b.ID = c.ID -- NOTE the join to TableB

Thank you, yes this is exactly what I meant. Thank you all for responding. ok, so for the most part, the consensus is that it shouldnt really matter.