Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Exotic Joins in SQL Part 2 Expand / Collapse
Author
Message
Posted Saturday, January 25, 2003 12:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 27, 2008 9:40 PM
Points: 19, Visits: 3
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ccubley/usingexoticjoinsinsqlpart2.asp


Chris Cubley
www.queryplan.com
Post #9522
Posted Wednesday, February 5, 2003 11:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, July 7, 2012 4:08 PM
Points: 21, Visits: 55
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?




Post #52457
Posted Tuesday, February 11, 2003 1:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:36 PM
Points: 9, Visits: 67
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





Post #52458
Posted Thursday, June 26, 2003 7:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 17, 2007 2:59 PM
Points: 67, Visits: 1
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.




Post #52459
Posted Thursday, June 26, 2003 3:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 10, 2003 12:00 AM
Points: 3, Visits: 1
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
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.








Post #52460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse