Using Exotic Joins in SQL Part 2

  • ccubley@queryplan.com

    SSC-Addicted

    Points: 411

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ccubley/usingexoticjoinsinsqlpart2.asp


    Chris Cubley
    www.queryplan.com

  • AjarnMark

    SSC Veteran

    Points: 251

    Good article with valuable information, but it appears that this has a repeated typo. Shouldn't s.RoomNumber = s.RoomNumber be s.RoomNumber = c.RoomNumber?

  • nlcapit

    SSC Rookie

    Points: 41

    Is there an advantage in putting the inequality in the join statement rather than in a WHERE clause? Does the query run faster?

    example:

    SELECT s.CourseID,s.SectionNumber,

    c.RoomCapacity, s.SectionCapacity

    FROM tb_ClassSection s

    INNER JOIN tb_Classroom c

    ON ( s.BuildingName = c.BuildingName

    AND s.RoomNumber = s.RoomNumber)

    WHERE c.RoomCapacity < s.SectionCapacity

  • bjmarte

    SSC Enthusiast

    Points: 153

    quote:


    Is there an advantage in putting the inequality in the join statement rather than in a WHERE clause? Does the query run faster?

    example:

    SELECT s.CourseID,s.SectionNumber,

    c.RoomCapacity, s.SectionCapacity

    FROM tb_ClassSection s

    INNER JOIN tb_Classroom c

    ON ( s.BuildingName = c.BuildingName

    AND s.RoomNumber = s.RoomNumber)

    WHERE c.RoomCapacity < s.SectionCapacity


    That would be my questions too. Too bad there's no answer.

  • ccubley

    SSC Journeyman

    Points: 99

    I don't know of any performance difference from putting the join criteria of an inner join into the where clause rather than the from clause. However, I always put join criteria (comparisons between columns of tables) in the from clause for consistency's sake because there IS a difference when it comes to outer joins. By doing it the same for inner and outer joins, things are easier down the road if you need to change the code.

    Chris Cubley

    http://www.queryplan.com

    quote:


    quote:


    Is there an advantage in putting the inequality in the join statement rather than in a WHERE clause? Does the query run faster?

    example:

    SELECT s.CourseID,s.SectionNumber,

    c.RoomCapacity, s.SectionCapacity

    FROM tb_ClassSection s

    INNER JOIN tb_Classroom c

    ON ( s.BuildingName = c.BuildingName

    AND s.RoomNumber = s.RoomNumber)

    WHERE c.RoomCapacity < s.SectionCapacity


    That would be my questions too. Too bad there's no answer.


Viewing 5 posts - 1 through 5 (of 5 total)

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