SQLServerCentral / Discuss Content Posted by Chris Cubley / Article Discussions / Article Discussions by Author / Using Exotic Joins in SQL Part 2 / Latest Posts

RE: Using Exotic Joins in SQL Part 2

ccubley — Thu, 26 Jun 2003 15:07:00 GMT

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 Cubleywww.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.SectionCapacityFROM tb_ClassSection sINNER JOIN tb_Classroom cON ( 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.

RE: Using Exotic Joins in SQL Part 2

bjmarte — Thu, 26 Jun 2003 07:18:00 GMT

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.SectionCapacityFROM tb_ClassSection sINNER JOIN tb_Classroom cON ( 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.

RE: Using Exotic Joins in SQL Part 2

nlcapit — Tue, 11 Feb 2003 13:34:00 GMT

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.SectionCapacityFROM tb_ClassSection sINNER JOIN tb_Classroom cON ( s.BuildingName = c.BuildingName AND s.RoomNumber = s.RoomNumber)WHERE c.RoomCapacity < s.SectionCapacity

RE: Using Exotic Joins in SQL Part 2

AjarnMark — Wed, 05 Feb 2003 23:32:00 GMT

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?

Using Exotic Joins in SQL Part 2

ccubley@queryplan.com — Sat, 25 Jan 2003 00:00:00 GMT

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