Using Exotic Joins in SQL Part 2

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


    Chris Cubley
    www.queryplan.com

  • 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?

  • 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

  • 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.

  • 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