On Using JOINS in FROM clause

  • I have a couple of questions in the below query which joins 4 tables:

    select t1.*

    from table1 t1

    inner join table2 t2

    on t1.id = t2.id

    inner join table3 t3

    inner join table4 t4

    on t3.id = t4.id

    on t2.name = t3.name

    My Questions:

    1. Can table3 be JOINed without specifying ON what fields they have to join?

    2. Should only table1 be joined with table2, table3 and table4 i.e., the first table should be joined with the rest of the tables in the FROM clause? or can table2 be joined with table4, table3 be joined with table2 and so on?

    3. inner join table4 statement is joined ON id, ON name. Here, ON clause is used twice. Is using 2 ONs same as ON and AND?

    select ....

    from ....

    inner join table4 t4

    on t3.id = t4.id

    on t2.name = t3.name

    is this same as

    select ....

    from ....

    inner join table4 t4

    on t3.id = t4.id

    and t2.name = t3.name

    4. Can INNER JOINS/OUTER JOINS be used in WHERE clause?

  • gyessql (1/22/2009)


    I have a couple of questions in the below query which joins 4 tables:

    select t1.*

    from table1 t1

    inner join table2 t2

    on t1.id = t2.id

    inner join table3 t3

    inner join table4 t4

    on t3.id = t4.id

    on t2.name = t3.name

    My Questions:

    1. Can table3 be JOINed without specifying ON what fields they have to join?

    Well, yes, but that's what's referred to as a cartesian product, meaning every row is associated with every other row. It's generally considered to be a bad thing.

    2. Should only table1 be joined with table2, table3 and table4 i.e., the first table should be joined with the rest of the tables in the FROM clause? or can table2 be joined with table4, table3 be joined with table2 and so on?

    If I understand the question, yes, you can join one table to more than one other table like this:

    SELECT...

    FROM Table1 t1

    JOIN Table2 t2

    ON t1.Id = t2.Id

    JOIN Table42 t42

    ON t1.Id = t42.Id

    3. inner join table4 statement is joined ON id, ON name. Here, ON clause is used twice. Is using 2 ONs same as ON and AND?

    select ....

    from ....

    inner join table4 t4

    on t3.id = t4.id

    on t2.name = t3.name

    is this same as

    select ....

    from ....

    inner join table4 t4

    on t3.id = t4.id

    and t2.name = t3.name

    No. That's not an AND. That's just two seperate ON clauses. While you can format the code that way, most people, for ease of readability, format the code with each JOIN associated directly to the ON clause. However, you can use AND clauses within the JOIN clause, no issue:

    SELECT...

    FROM Table1 t1

    JOIN Table2 t2

    ON t1.Id = t2.Id

    AND t2.Date > '1/1/2009'

    JOIN Table3 t3

    ON t2.Id = t3.Id

    4. Can INNER JOINS/OUTER JOINS be used in WHERE clause?

    Yes they can.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant, would you mind showing an example of how you would use joins in a where clause? I'm not sure what you mean.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Oops. There I go again not reading everything before posting.

    You can't.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks Grant for the answers.

    Grant Fritchey (1/22/2009)


    If I understand the question, yes, you can join one table to more than one other table like this:

    SELECT...

    FROM Table1 t1

    JOIN Table2 t2

    ON t1.Id = t2.Id

    JOIN Table42 t42

    ON t1.Id = t42.Id

    Can Table2 and Table42 be joined in this query?

    SELECT ...

    FROM Table1 t1

    JOIN Table2 t2

    ON t1.Id = t2.Id

    JOIN Table42 t42

    ON t1.Id = t42.Id

    AND t2.Id = t42.Id-- Is this correct?

    3. inner join table4 statement is joined ON id, ON name. Here, ON clause is used twice. Is using 2 ONs same as ON and AND?

    No. That's not an AND. That's just two seperate ON clauses. While you can format the code that way, most people, for ease of readability, format the code with each JOIN associated directly to the ON clause.

    Does this mean, instead of ON and AND, for readability ON and ON can be used? Do the below 2 queries return the same result

    select ....

    from ....

    inner join table4 t4

    on t3.id = t4.id

    on t2.name = t3.name

    select ....

    from ....

    inner join table4 t4

    on t3.id = t4.id

    and t2.name = t3.name

    How are the tables joined in the below query? Aren't table1 and table2 joined both on id and name?

    For example:

    select ...

    from table t1

    inner join t2

    on t1.id = t2.id

    on t1.name = t2.name

  • Bob Hovious (1/22/2009)


    Grant, would you mind showing an example of how you would use joins in a where clause? I'm not sure what you mean.

    Well, it's not the join keyword and it's an old way of doing things, and the outer join syntax only works in SQL 2000 and earlier, but

    from table1, table2, table3, table4

    where t1.id = t2.id and t3.id = t4.id and t2.name = t3.name

    joins in the where clause.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • gyessql (1/22/2009)


    Does this mean, instead of ON and AND, for readability ON and ON can be used?

    No. They're not synonymous.

    select ....

    from ....

    inner join table4 t4

    on t3.id = t4.id

    on t2.name = t3.name

    Msg 156, Level 15, State 1, Line 5

    Incorrect syntax near the keyword 'on'.

    How are the tables joined in the below query? Aren't table1 and table2 joined both on id and name?

    Yup, that's a join based on two columns. It's not what you had earlier, where the two clauses did not refer to the same tables.

    good practice (for readability) is to have the on straight after the join so.

    from table1

    inner join table2 on < join conditions between table1 and table2>

    inner join table3 on < join conditions between table2 and table3, or between table1 and table3, whichever is the correct one>

    inner join table4 on < join conditions between table4 and either table1, table2 or table3, whichever is correct>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail..

    GilaMonster (1/22/2009)


    Yup, that's a join based on two columns. ...

    Is there any difference between joining in these 2 ways?

    select ...

    from table t1

    inner join t2

    on t1.id = t2.id

    on t1.name = t2.name

    select ...

    from table t1

    inner join t2

    on t1.id = t2.id

    and t1.name = t2.name

    from table1

    inner join table2 on < join conditions between table1 and table2>

    inner join table3 on < join conditions between table2 and table3, or between table1 and table3, whichever is the correct one>

    inner join table4 on < join conditions between table4 and either table1, table2 or table3, whichever is correct>

    Thanks Gail, this clears a lot of my questions.

  • gyessql (1/22/2009)


    Is there any difference between joining in these 2 ways?

    select ...

    from table t1

    inner join t2

    on t1.id = t2.id

    on t1.name = t2.name

    select ...

    from table t1

    inner join t2

    on t1.id = t2.id

    and t1.name = t2.name

    Yes. One of them will run and one of them will give you a syntax error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/22/2009)


    Yes. One of them will run and one of them will give you a syntax error.

    :D:D Good one!!

    Thanks for all the clarifications..

  • GilaMonster (1/22/2009)


    gyessql (1/22/2009)


    Is there any difference between joining in these 2 ways?

    select ...

    from table t1

    inner join t2

    on t1.id = t2.id

    on t1.name = t2.name

    select ...

    from table t1

    inner join t2

    on t1.id = t2.id

    and t1.name = t2.name

    Yes. One of them will run and one of them will give you a syntax error.

    Thanks for picking up on all these Gail. I was off in meetings.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • GilaMonster (1/22/2009)[hrWell, it's not the join keyword and it's an old way of doing things, and the outer join syntax only works in SQL 2000 and earlier, but

    from table1, table2, table3, table4

    where t1.id = t2.id and t3.id = t4.id and t2.name = t3.name

    joins in the where clause.

    Thanks Gail, I was familiar with that and wondered if that style was what Grant was referring to. I always think of that as being a WHERE clause to what is essentially an implied cross join. Never looked at the execution plan though.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Bob Hovious (1/22/2009)


    GilaMonster (1/22/2009)[hrWell, it's not the join keyword and it's an old way of doing things, and the outer join syntax only works in SQL 2000 and earlier, but

    from table1, table2, table3, table4

    where t1.id = t2.id and t3.id = t4.id and t2.name = t3.name

    joins in the where clause.

    Thanks Gail, I was familiar with that and wondered if that style was what Grant was referring to. I always think of that as being a WHERE clause to what is essentially an implied cross join. Never looked at the execution plan though.

    Nah, I was just being sloppy.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Watch out... Cardinal Moden may be lurking about somewhere close by with pork chop in one hand and a fish in the other.

    😉

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Bob Hovious (1/22/2009)


    Never looked at the execution plan though.

    Providing you haven't left a cross join in by accident, it'll be the same as for the one with JOIN.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 17 total)

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