SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Exotic Joins in SQL Part 1


Using Exotic Joins in SQL Part 1

Author
Message
ccubley@queryplan.com
ccubley@queryplan.com
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 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
Dave Poole
Dave Poole
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26604 Visits: 3543
Is there any benefit in using these sort of joins instead of using the WHERE clause?

LinkedIn Profile
www.simple-talk.com
ccubley@queryplan.com
ccubley@queryplan.com
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 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
Dave Poole
Dave Poole
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26604 Visits: 3543
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
www.simple-talk.com
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (43K reputation)

Group: Moderators
Points: 43274 Visits: 1917
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
@‌kbriankelley
jfmccabe
jfmccabe
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1137 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.





David le Quesne
David le Quesne
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 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...
Antares686
Antares686
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: Moderators
Points: 44480 Visits: 803

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





Parker Smith
Parker Smith
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 Visits: 135
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.



Bilster-223920
Bilster-223920
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search