开发者

join with the on supplied later

开发者 https://www.devze.com 2023-02-14 16:59 出处:网络
I have a query in the following format select * from Table1 t1 inner join Table2 t2 inner join Table3 t3 on t2.ID = t3.ID

I have a query in the following format

select
    *
from
    Table1 t1
    inner join Table2 t2
    inner join Table3 t3 on t2.ID = t3.ID
    on t3.ID = t1.ID

What I do know:

  1. Not providing the last on condition results开发者_运维技巧 in an error.
  2. Additionally changing the first join condition from on t2.ID = t3.ID to on t1.ID = t2.ID results in an error that t1.ID could not be bound.

Obviously the above examples are arbitrary and may not actually produce a practically useful result. However, an explanation of what providing the on later is actually doing would be great.

Thanks

EDIT I'm not trying to change the question to something that works but to understand what MSSQL is doing when I provide it.


You can use the format you specified (presuming the correct table aliases), if you use parenthesis.

Select ... -- never use Select *
From (Table1 As T1
    Join Table2 As T2
        On T2.ID = T1.ID)
    Join Table3 As T3
        On T3.ID = T1.ID

However, with equi-joins (inner joins) it really makes no difference and it is easier to read if you do not use parenthesis. However, this format is very useful with outer joins. Take the following two examples:

Example 1

Select ...
From Table1 As T1
    Left Join Table2 As T2
        On T2.T1_ID = T1.ID
    Join Table3 As T3
        On T3.T2_ID = T2.ID

Example 2

Select ...
From Table1 As T1
    Left Join (Table2 As T2
        Join Table3 As T3
            On T3.T2_ID = T2.ID)
        On T2.T1_ID = T1.ID

Suppose in this situation, that T3.T2_ID is a non-nullable foreign key to Table2. In Example1, the Inner Join to Table3 will effectively filter out rows that would have been null because the given T2.T2_ID does not exist in Table1. However, in the second example, the join between Table2 and Table3 is done before the Left Join to Table1 is processed. Thus, we'll get the same rows from Table1 and Table2 as:

Example 3

Select ...
From Table1 As T1
    Left Join Table2 As T2
        On T2.T1_ID = T1.ID


Assuming you meant t1 rather than t, then your query:

select
    *
from
    Table1 t1
    inner join Table2 t2
    inner join Table3 t3 on t2.ID = t3.ID
    on t3.ID = t1.ID

...can be made rather more clear by the addition of the brackets it doesn't really need:

select
    *
from
    Table1 t1
      inner join 
        (Table2 t2 inner join Table3 t3 on t2.ID = t3.ID) on t3.ID = t1.ID

Effectively, you're explicitly saying "join t2 to t3, then join t1 to that."

Does that help?


First off--you don't define what t is

Table1 is aliased t1 Table2 is aliased t2 Table3 is aliased t3

But there is no plain t.

Second, you are not doing a join of t1 to t2, but of t1 to t3 and then t3 to t2. That will work. If there is a relation between t1 and t2 (t1.ID=t2.ID) then that "on" statement should directly follow the inner join statement for t2:

select
    *
from
    Table1 t1
    inner join Table3 t3 on t1.ID = t3.ID
    inner join Table2 t2 on t3.ID = t2.ID

UPDATE (based on your update) are t1.ID, t2.ID, and t3.ID all the same data type?

0

精彩评论

暂无评论...
验证码 换一张
取 消