Using Exotic Joins in SQL Part 1
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ccubley/usingexoticjoinsinsql.asp
Chris Cubley
www.queryplan.com -
Is there any benefit in using these sort of joins instead of using the WHERE clause?
-
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
Chris Cubley
Chris Cubley
www.queryplan.com -
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.....
-
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 -
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.
-
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...
-
BETWEEN is purely simpler to read. There is no specific engine enhancements that make BETWEEN any faster.
-
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.
-
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
-
I think you can use the following sql to do your work
select * from My_Table
where col_date between isnull(@Start_Date, '1900-01-01') and isnull(@End_Date, '2999-01-01')
Assume your real date is no earlier than '1900-01-01' and no later than '2999-01-01')
-
David,
You say that dropping the support for the WHERE clause is breaking away from the ANSI standard. It is the ANSI standard that has pshed the move to using the FROM clause for Joins and not the WHERE clause. Based on that how would this move by Microsoft be considered a move away from the ANSI standard? Even if the latest standard allows for joins in the FROM and WHERE clause the fact that the preferred method of joins is in the FROM clause I would think that sticking with usisng the WHERE clause would actually be a move away from the ANSI standard.
PLease understand that I am not trying to sound negative or attack your comment, I know personally how emails and postings are poor at properly conveying emotions. I am merely trying to understand why you feel this way and try to find out if maybe I have missed something or have improperly understood the ANSI standard.
Thanks
Ed
Kindest Regards,
Just say No to Facebook! -
BETWEEN is purely simpler to read. There is no specific engine enhancements that make BETWEEN any faster.
2cents more..."Between" is not ANSI 92 standard, so using < and > is more portable.
Signature is NULL
-
Hi
You can write query like this:
suppose say you are passing two parameters name @StartDate and @EndDate and you column name in table is TransDate
TransDate BETWEEN ISNULL(@StartDate, TransDate) AND ISNULL(@EndDate, TransDate)
Rohit
-
One of the poorest articles I have read in the long time. Theta join is by no means an exotic operator. Set equality and set containment join is. Google "relational division"
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. Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply