Join Clarification 1

[petebristol]

Hello,

Please can you help?

I've tied myself up in knots recently trying to understand joins (specifically to more than 2 tables) and I think I need some clarification before I can go any further.

I've read in so many places over the web that a "join is a relationship between two and only two tables". Is this correct?

In this example, for instance, it's clear that the "relationship" is between Table1 & Table2.

Select * from Table1 inner join Table2 on .....

But what about this?

Select * from Table1 inner join Table2 on..... inner join Table3 on ......

Does the bolded text effectively become one "table" so the relationship forms between this and Table3?

Given all of that, does the ordering of the joins in the SQL statement make a difference? Or does the Query Optimiser some-how sort it all out to generate the same answer?

I really appreciate any help given and look forward to any replies with anticipation.

Regards,

Pete

 

[r937]

a join isn't a relationship

a relationship exists when you define a foreign key

a join can actually be made on any column you feel like

and a join does only operate on two tables at a time

in the example you gave, with three tables, first two of them are joined, producing an intermediate result (which is itself a table, even if not a permanent one) and this is then used to join to the next table

i prefer to think of a join as joining a table to "whatever (intermediate) table has been built so far"

so you can join multiple tables together, and at each step in the execution, it always joins the next table to something it has already constructed

does the sequence matter? if they are all INNER JOINs, no

if there are OUTER JOINs, then the sequence does matter

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[petebristol]

r937,

Thank you, that helps tremendously.

Pete

 

[gmmastros]

To add to Rudy's excellent post...

a join can actually be made on any column you feel like

This is certainly true, but it can be more complicated than it seems. You can use the join clause to filter data, like this:

[tt]
From Table1
Inner Join Table2
On Table1.Column = Table2.Column
And Table1.OtherColumn = 'HardCodedValue'
[/tt]

You can also use functions when joining tables, like this:


[tt]
From Table1
Inner Join Table2
On Left(Table1.Column, 10) = Left(Table2.Column, 10)
[/tt]

When you are writing joins, you should try to avoid using functions in your join clause because they can be disastrous in terms of performance.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom