November 26, 2010 at 8:20 am
The book I'm reading says that you can JOIN in either the FROM or the WHERE clause but gives no examples in the WHERE. I've only ever seen it in the FROM.
Does anyone know what they mean? Are they talking about the old syntax where you list the tables with commas and then do t1.key = t2.key in the WHERE?
I'm just curious and would like to see an example if possible. I would also like to know if there is a case when you'd rather use the JOIN in the WHERE.
Thanks!
November 26, 2010 at 8:52 am
Yes, that's the old-style join syntax which is deprecated for inner joins, and obsolete for outer joins. Goes something like this
SELECT * FROM Table1 t1 JOIN Table2 t2
ON t1.ID = t2.ID
In old-style, that would be
SELECT * FROM Table1 t1, Table2 t2
WHERE t1.ID = t2.ID
You should avoid the old-style syntax since it will not be supported in a future version of SQL Server.
John
November 26, 2010 at 12:10 pm
yes avoid depricated stuff...
November 26, 2010 at 12:32 pm
The old style joining in where clause is only deprecated (and in fact removed) for outer joins. The inner join in where clause is still fully supported and is not deprecated and, if I'm not mistaken, is still in the ANSI standard.
Deprecated and removed.
SELECT * FROM Table1 t1, Table2 t2
WHERE t1.ID *= t2.ID
Not deprecated and still fully supported
SELECT * FROM Table1 t1, Table2 t2
WHERE t1.ID = t2.ID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2010 at 1:30 am
CELKO (11/26/2010)
Older, experienced SQL programmers use the original syntax so they can see n-ary relationships.
An unfounded generalisation of monumental proportion. Opinion. Blatantly wrong in a vast number of cases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2010 at 1:34 pm
Actually, it's just plain easier to read and conceptualize the relationships between the tables as one is going along when it's done in the JOIN.
Todd Fifield
November 30, 2010 at 2:28 pm
CELKO (11/29/2010)
GilaMonster (11/27/2010)
CELKO (11/26/2010)
Older, experienced SQL programmers use the original syntax so they can see n-ary relationships.It is a generalization, but it is founded on several things from language design, typography and teaching SQL for a few decades. I go into painful details in one of my books, but here are some short bullets
1) Law of Proximity. This is a principle of typography and visual psychology that says things which are close together are seen as a unit of work. The infixed operators spread the ON clauses pretty much anywhere on the page. I remember one guy who put all of the ON clauses at the end of a list of INNER JOINs.
The original notation lets me re-arrange the text so that I can see what is happening with each table by physically putting the predicates together.
2) Law of Similarity. When you do something different ways, you ought to be able to see that they are the same thing under the covers. Try this:
SELECT ..
FROM T1, T2, T3
WHERE T1.a BETWEEN T2.b AND T3.c;
Now write it with infixed operators. Bonus points if you can write all possible predicates. Did you notice how the 3-ary BETWEEN relationship is lost in binary operators? The same thing happens with IN() if you spread it out as a chain of ORs. Most programmers do not use (or really know about) the <comp> [ALL|SOME|ANY] <table expression> predicates.
3) The mindset for infixed operators (and even the definition of them in the Standards) is a sequence of left to right operations. This is not set-oriented. The example I use is addition with a + versus summation with a S when I tried to get people to think in sets.
Joe,
Your #2 is interesting. When would you possibly ever use that besides in theroy?
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
November 30, 2010 at 2:54 pm
Trey Staker (11/30/2010)
CELKO (11/29/2010)
GilaMonster (11/27/2010)
CELKO (11/26/2010)
Older, experienced SQL programmers use the original syntax so they can see n-ary relationships.It is a generalization, but it is founded on several things from language design, typography and teaching SQL for a few decades. I go into painful details in one of my books, but here are some short bullets
1) Law of Proximity. This is a principle of typography and visual psychology that says things which are close together are seen as a unit of work. The infixed operators spread the ON clauses pretty much anywhere on the page. I remember one guy who put all of the ON clauses at the end of a list of INNER JOINs.
The original notation lets me re-arrange the text so that I can see what is happening with each table by physically putting the predicates together.
2) Law of Similarity. When you do something different ways, you ought to be able to see that they are the same thing under the covers. Try this:
SELECT ..
FROM T1, T2, T3
WHERE T1.a BETWEEN T2.b AND T3.c;
Now write it with infixed operators. Bonus points if you can write all possible predicates. Did you notice how the 3-ary BETWEEN relationship is lost in binary operators? The same thing happens with IN() if you spread it out as a chain of ORs. Most programmers do not use (or really know about) the <comp> [ALL|SOME|ANY] <table expression> predicates.
3) The mindset for infixed operators (and even the definition of them in the Standards) is a sequence of left to right operations. This is not set-oriented. The example I use is addition with a + versus summation with a S when I tried to get people to think in sets.
Joe,
Your #2 is interesting. When would you possibly ever use that besides in theroy?
I can see cases where T2 and T3 hold disrelated event data (say, new customers signing on and marketing campaigns), while T1 is a calendar table, and you want to see counts of business days between those events. T2.b could be MarketingCampaigns.StartDate and T3.c could be Customers.DateAdded, and the Select clause would become a set of aggregates and the Marketing Campaign name/ID/whatever. This would be useful in data mining for the effectiveness and lag values in marketing. You'd refine to more specific queries later, but you want to start with a broad, general data set when mining, to avoid false initial assumptions prejudicing the results.
That's the first use that comes to my mind.
And putting the relation in the Where clause allows use of an intuitive "between" in this case, instead of a less intuitive set of two separate relations to T1.
So, yeah, it could have some use. There are undoubtedly easier, and more effective ways, to gather the same data, but this would be legit to a certain extent.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 30, 2010 at 7:04 pm
CELKO (11/30/2010)
The one predicate I like is generalized equality. I can do it with some work and <expr> = ALL(<table constructor >), but it would to do an n-way join with something like EQUALS (T1.a, T2.a, .. Tn.a)
It also happens to be the only one I've found to be any kind of performant. The others tend to be like many of the other gimmicks being advocated here: they end to be dangerous due to runaway cardinality (I mean - have you considered what kind of recordset you get on production size tables with a ....WHERE t1.a between t2.b and t3.c?). You can write multiple unbounded semi-joins in any syntax you wish - you STILL will bring the server down.
In the same vein as the discussion I've had with Chris Date - the syntax might be cute and all, but until we have actual systems that can actually do these n-ary joins "all at once" in a production environment with some semblance of performance, they just end up being a waste of time.
Call me an Access programmer if you wish, but it's usually wise not to bring the server to its knees just because you thought it might be a cute thing to "visualize" you tuples more elegantly. A query that is well understood by the engine you're running on, can be optimized by the same and runs in a short amount of time using a minimum amount of resources in the process, might be stodgy and unoriginal, but it's also the gold standard in my book.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 1, 2010 at 9:37 pm
JohnnyDBA (11/26/2010)
The book I'm reading says that you can JOIN in either the FROM or the WHERE clause but gives no examples in the WHERE. I've only ever seen it in the FROM.Does anyone know what they mean? Are they talking about the old syntax where you list the tables with commas and then do t1.key = t2.key in the WHERE?
I'm just curious and would like to see an example if possible. I would also like to know if there is a case when you'd rather use the JOIN in the WHERE.
You can write inner joins using either syntax - both are equally valid and supported, as has been said. For example:
DECLARE @T1 TABLE (A INT NOT NULL);
DECLARE @T2 TABLE (B INT NOT NULL);
DECLARE @T3 TABLE (C INT NOT NULL);
-- Original style
SELECT *
FROM @T1 T1,
@T2 T2,
@T3 T3
WHERE T2.B = T1.A
AND T3.C = T2.B;
-- New style
SELECT *
FROM @T1 T1
JOIN @T2 T2 ON T2.B = T1.A
JOIN @T3 T3 ON T3.C = T2.B;
For any other sort of join (outer/full) you pretty much have to use the new syntax (the OUTER keyword is optional):
SELECT *
FROM @T1 T1
LEFT
JOIN @T2 T2 ON T2.B = T1.A
LEFT
JOIN @T3 T3 ON T3.C = T2.B;
The old *= and =* syntax can be ambiguous and is strongly discouraged almost universally. In SQL Server, the database compatibility must be set to 80 (SQL Server 2000) in order to use it at all. Compatibility level 80 is not available in the next major release of SQL Server (code name Denali). I tried and failed to write the above outer join query using *= syntax, which is probably a good thing.
Most people prefer the new-style syntax, for practical reasons:
1. It is too easy to miss a join condition with the original syntax (resulting in a cross product). A missing ON clause is easy to spot using the new syntax, and will result in an error at parse time.
2. You end up using two different forms for inner and outer/full joins. It quickly becomes tedious changing between the two when a query originally written to use an inner join needs to be modified to use an outer join.
The other attraction for some people is that it visually separates the join conditions from other predicates. I tend to prefer this myself, but opinions do vary on this one.
Most places enforce the use of one syntax or the other, so the practical answer is to go with whichever is already in use wherever you are. If your circumstances allow you to make a free choice, go with whatever seems more natural to you. In my experience, most modern development (and most modern developers!) use the new syntax, so that might be another point in its favour, assuming your code will ever be read or maintained by someone else.
There might be an edge case where putting the join predicates in the WHERE clause makes sense, but I've never come across it.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 10, 2010 at 7:32 am
CELKO (11/26/2010)
Older, experienced SQL programmers use the original syntax so they can see n-ary relationships. ACCESS programmers have to do JOIN predicates in the ON clauses then filter in the WHERE clause.
Actually, you can do it in the old notation in ACCESS as well. I use it sometimes, when I feel the readability is better using the old syntax.
brgds
Philipp Post
December 10, 2010 at 6:41 pm
CELKO (12/10/2010)
>> There might be an edge case where putting the join predicates in the WHERE clause makes sense, but I've never come across it. -- Paul <<LOL! You never wrote a SELECT on one table?
Amusing 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply