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

RE: Using Exotic Joins in SQL Part 1

John Q Public-431253 — Wed, 29 Aug 2007 09:54:00 GMT

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"

RE: Using Exotic Joins in SQL Part 1

ROhit Shrivastava — Wed, 03 May 2006 05:23:00 GMT

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

RE: Using Exotic Joins in SQL Part 1

Calvin Lawson — Mon, 02 May 2005 18:15:00 GMT

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.

RE: Using Exotic Joins in SQL Part 1

YSLGuru — Mon, 02 May 2005 09:26:00 GMT

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

RE: Using Exotic Joins in SQL Part 1

jeffrey yao — Fri, 29 Apr 2005 09:51:00 GMT

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')

RE: Using Exotic Joins in SQL Part 1

Bilster-223920 — Fri, 29 Apr 2005 09:26:00 GMT

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

RE: Using Exotic Joins in SQL Part 1

Parker Smith — Fri, 29 Apr 2005 08:11:00 GMT

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.

RE: Using Exotic Joins in SQL Part 1

Antares686 — Fri, 29 Apr 2005 08:09:00 GMT

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

RE: Using Exotic Joins in SQL Part 1

David le Quesne — Fri, 29 Apr 2005 02:15:00 GMT

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

RE: Using Exotic Joins in SQL Part 1

jfmccabe — Fri, 29 Apr 2005 02:08:00 GMT

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.

RE: Using Exotic Joins in SQL Part 1

K. Brian Kelley — Tue, 04 Feb 2003 06:53:00 GMT

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 Kelleyhttp://www.truthsolutions.com/Author: Start to Finish Guide to SQL Server Performance Monitoring http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

RE: Using Exotic Joins in SQL Part 1

David.Poole — Tue, 04 Feb 2003 02:28:00 GMT

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.....

RE: Using Exotic Joins in SQL Part 1

ccubley@queryplan.com — Mon, 03 Feb 2003 21:14:00 GMT

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 Cubleywww.queryplan.comChris Cubleywww.queryplan.com

RE: Using Exotic Joins in SQL Part 1

David.Poole — Mon, 03 Feb 2003 03:37:00 GMT

Is there any benefit in using these sort of joins instead of using the WHERE clause?

Using Exotic Joins in SQL Part 1

ccubley@queryplan.com — Sun, 19 Jan 2003 00:00:00 GMT

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