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 12»»

Using Exotic Joins in SQL Part 1 Expand / Collapse
Author
Message
Posted Sunday, January 19, 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/usingexoticjoinsinsql.asp


Chris Cubley
www.queryplan.com
Post #9382
Posted Monday, February 3, 2003 3:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:45 PM
Points: 2,892, Visits: 1,784
Is there any benefit in using these sort of joins instead of using the WHERE clause?



LinkedIn Profile
Newbie on www.simple-talk.com
Post #51933
Posted Monday, February 3, 2003 9:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 27, 2008 9:40 PM
Points: 19, Visits: 3
The benefit of doing these type of joins in the FROM clause rather than the WHERE clause is the same as it is for an equijoin. For inner joins, the two different syntaxes are equivelent. For outer joins, performing the joins in the WHERE clause will cause non-matched rows to be excluded. This is because any comparison to NULL (i.e. 5 = NULL, NULL = NULL, NULL BETWEEN 5 AND 20) returns false. Furthermore, Microsoft recommends that all joins be done in the FROM clause and warns that support for WHERE clause joins may be dropped in future versions of SQL Server.

Chris Cubley
www.queryplan.com

Chris Cubley
www.queryplan.com



Chris Cubley
www.queryplan.com
Post #51934
Posted Tuesday, February 4, 2003 2:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:45 PM
Points: 2,892, Visits: 1,784
I'm not sure how they could drop support for the WHERE clause.

If they are then they would be breaking away from ANSI standard SQL and force a paradigm shift on their user base.

If it ain't broke.....



LinkedIn Profile
Newbie on www.simple-talk.com
Post #51935
Posted Tuesday, February 4, 2003 6:53 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
I think Microsoft may actually be toeing the line on this one... It used to be okay to use *= or =* in the WHERE clause for OUTER JOIN, however, sometimes the use of such sometimes leads to ambiguous queries... I don't have an example handy, it's too early in the morning. You almost get the feeling that the standard may evolve to where none of the JOIN syntax can be in the WHERE clause. But to be honest, I don't see the INNER JOIN syntax in the WHERE clause going away anytime soon.


K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #51936
Posted Friday, April 29, 2005 2:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 1:01 AM
Points: 351, Visits: 440

As somebody who works in education, I use this type of construct on a regular basis. It would be nice to see an example with real numbers rather than integers. Typically you end up writing it as

SELECT
      s.StudentID,
      g.LetterGrade
FROM
      tb_StudentGrade s
INNER JOIN
      tb_GradeScale g
          ON(
(s.NumericGrade >= g.MinNumeric) AND (s.NumericGrade < g.MaxNumeric)
)

Which may or may not be the best way.




Post #178607
Posted Friday, April 29, 2005 2:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, Visits: 32

Hello Chris

Please excuse a question from a newbie, but is there an advantage in using the between operator as opposed to using >= and <= comparisons. 

Looking at the execution plans of some of my queries it seems to make no difference to the plan structure or cost of the query replacing >= and <= with between in either the WHERE or from clause, but it certainly makes the syntax a bit neater.

David



If it ain't broke, don't fix it...
Post #178609
Posted Friday, April 29, 2005 8:09 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 22, 2014 1:22 PM
Points: 8,369, Visits: 734

BETWEEN is purely simpler to read. There is no specific engine enhancements that make BETWEEN any faster.




Post #178770
Posted Friday, April 29, 2005 8:11 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, June 15, 2014 11:29 AM
Points: 51, Visits: 93
I have frequent need to use BETWEEN type logic for date ranges, where the start date or the end date (or both) may be null.

If just the start date is null, I want anything earlier than the end date.

If just the end date is null, I want anything later than the start date.

If both are null, I want all records.

I can do this with a user defined function - but want to know if there is a particularly efficient way to approach it. Performance is an issue.



Post #178772
Posted Friday, April 29, 2005 9:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 16, 2005 7:37 AM
Points: 9, Visits: 1

What about performance using this methodology?
There are many articles about using "SARG"able methodologies which allow indexes to operate correctly.
http://www.databasejournal.com/features/mssql/article.php/1436301
http://www.windowsitpro.com/SQLServer/Article/ArticleID/42349/42349.html

It appears that the JOIN portion is SARGable, but how do we know for sure since the join seems to now operate onmultiple values?


INNER JOIN
      tb_GradeScale g
      ON(
            s.NumericGrade BETWEEN g.MinNumeric AND g.MaxNumeric
      )

- B

Post #178814
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse