July 12, 2012 at 10:56 am
Or maybe when they forget the join condition in a where clause and get a cross join on 2 tables with 1M+ rows each. 😀
Two main reasons:
1) Code is far easier to read and understand.
2) It is far too easy to accidentally create a cross join when a condition is forgotten in the where clause (the join syntax throws and error with no ON specified)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 12, 2012 at 11:23 am
Sean Lange (7/12/2012)
Or maybe when they forget the join condition in a where clause and get a cross join on 2 tables with 1M+ rows each. 😀Two main reasons:
1) Code is far easier to read and understand.
2) It is far too easy to accidentally create a cross join when a condition is forgotten in the where clause (the join syntax throws and error with no ON specified)
Thanks, I already tried thouse though.
Ah well.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 12, 2012 at 11:42 am
To add to the code clarity, when you have queries that use both INNER and OUTER joins, it makes the FROM clause much more readable and concise when all the JOINs and their criteria are in one place.
July 12, 2012 at 12:27 pm
Jan Van der Eecken (7/12/2012)
Guess that'll only happen once it is deprecated and was really purged out of the product. Which didn't happen in 2012 although I kind of expected it. At least the old-style outer joins are gone.
Thing is, you can't really deprecate the old style joins. What do you deprecate, the ability to comma-list tables in the from? The ability to compare two columns in the where clause?
Should this be valid?
-- cross join for lots of rows
SELECT c.object_id from sys.columns c, sys.columns c1
What about this?
SELECT <stuff>
FROM Orders o inner join Deliveries d on o.OrderID = d.OrderID
WHERE d.deliverydate > DATEADD(ww,1,o.ShippingDate)
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
July 12, 2012 at 1:17 pm
Steve, will there be a SSC Party at Summit again this year? If so, can I put in an early plug for a non-gambling reduced/free entry option? I still have my chip tickets from the last two years and although they are cool (should I frame them?), I'm not really a collector.... :-D.
Thanks,
Chad
July 12, 2012 at 1:28 pm
mtillman-921105 (7/11/2012)
'Guess it's time for me to pu away my aluminum floil[/url] now. MIT has researched the effectiveness of tin foil hats. :w00t:[edited link]
HAH! They convinced you with that little blurb too? You should know better, never trust the government or any purported 'study', they LIE. Of course they'll tell you that you shouldn't use them. They quite obviously DO work well enough! I haven't heard from the Shlamoofs in years since I started wearing mine!
(Bonus points if you know what a Shlamoof is...)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 12, 2012 at 1:59 pm
GilaMonster (7/12/2012)
Jan Van der Eecken (7/12/2012)
Guess that'll only happen once it is deprecated and was really purged out of the product. Which didn't happen in 2012 although I kind of expected it. At least the old-style outer joins are gone.Thing is, you can't really deprecate the old style joins. What do you deprecate, the ability to comma-list tables in the from? The ability to compare two columns in the where clause?
Should this be valid?
-- cross join for lots of rowsSELECT c.object_id from sys.columns c, sys.columns c1
...
This:
-- cross join for lots of rows
SELECT c.object_id from sys.columns c, sys.columns c1
becomes this:
-- cross join for lots of rows
SELECT c.object_id from sys.columns c cross join sys.columns c1
July 12, 2012 at 2:30 pm
And hence joins in the where clause become
SELECT <stuff>
FROM t1 CROSS JOIN t2
WHERE t1.ID = t2.OtherID
The point is that joins in the where cannot be deprecated without a substantial rewrite of the SQL standard. Honestly, should comma-separated lists of tables be considered invalid? Should comparing columns in the where be considered invalid?
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
July 12, 2012 at 2:43 pm
GilaMonster (7/12/2012)
And hence joins in the where clause becomeSELECT <stuff>
FROM t1 CROSS JOIN t2
WHERE t1.ID = t2.OtherID
The point is that joins in the where cannot be deprecated without a substantial rewrite of the SQL standard. Honestly, should comma-separated lists of tables be considered invalid? Should comparing columns in the where be considered invalid?
But this:
SELECT <stuff>
FROM t1 CROSS JOIN t2
WHERE t1.ID = t2.OtherID
is just this
SELECT <stuff>
FROM
t1 INNER JOIN t2
ON t1.ID = t2.OtherID
July 12, 2012 at 2:47 pm
The point's over there ->
Re: deprecating the old style of joins, ie joining in the where clause. Cannot be easily done.
I'm not endorsing old style joins. I'm saying they cannot be deprecated because the individual pieces of syntax are valid and have uses.
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
July 12, 2012 at 2:51 pm
And since the old style cannot be easily deprecated, it's best just to teach the new ways imho.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2012 at 3:42 pm
SQLRNNR (7/12/2012)
And since the old style cannot be easily deprecated, it's best just to teach the new ways imho.
untill you run into a production system using sql 7 because its easier to keep an old machine running than upgrade millions of dollars of manufacturing hardware
and gail congrats on the MCM
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 12, 2012 at 3:51 pm
capn.hector (7/12/2012)
SQLRNNR (7/12/2012)
And since the old style cannot be easily deprecated, it's best just to teach the new ways imho.untill you run into a production system using sql 7 because its easier to keep an old machine running than upgrade millions of dollars of manufacturing hardware
in that case the old ways still work but you can still teach the new ways 😉
as for sql 7... Dynamite
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2012 at 3:52 pm
capn.hector (7/12/2012)
SQLRNNR (7/12/2012)
And since the old style cannot be easily deprecated, it's best just to teach the new ways imho.untill you run into a production system using sql 7 because its easier to keep an old machine running than upgrade millions of dollars of manufacturing hardware
and gail congrats on the MCM
I used ANSI-92 style joins in SQL Server 7 with no problems. I actually started with SQL Server 6.5 and never wrote a line of SQL using ANSI-89 style joins.
July 12, 2012 at 5:48 pm
A request to the threadizens, please don't pile on TeraByteMe if you happen across our little spat, at least not on my behalf. If it continues I'll be attempting to take it to PMs and get it off the main boards.
Edit: I should say if you want to take a swipe at me however, feel free. I can live with it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 36,841 through 36,855 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply