Can anyone think of another name for the old-style Join? 4

[WoodyGuthrie]

I've seen it on this (maybe) and other forums but cannot find it in search. It's a slang term used for the old syntax:

SELECT c.strReceiptNo, d.strName 
FROM dbo.tblControlLog c, dbo.tblDepartments d
WHERE c.guidDepartmentID = d.guidDepartmentID

Thanks

Woody

 

[gmmastros]

non-ansi ???


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WoodyGuthrie]

No. That's not it. Maybe it was SQL-Sister that used the term.?? Not sure. I'm almost certain it was on this forum. Maybe the quote went something like "Stop using those _______ joins!"

Woody

 

[DaveInIowa]

ANSI SQL 87 or Cross Join?
Is this the thread you were referring to? thread183-906068

 

[WoodyGuthrie]

Found it.....The term is: "COMMA JOINS".

Posted By SQLSister:

...Next learn to use ANSI standard joins. Never use those crappy comma joins again. They are horrible to maintain and often give bad results because it is very easy to acidentally get a cross join. Further, you will need to actually understand joins in order to do left joins which cannot accurately be done in that format at all in SQL Server. The standard is 17 years old, it is time to bite the bullet and learn it.

So I've put the real task off long enough. I have no more excuses. Now I must face the big, bad, and the ugly multiple cursor'd query which calls multiple procedural coded functions which call yet more procedure coded, cursor usin' functions and comma join usin' From clauses.

I know you feel my pain. Wish me luck.

Thanks again


Woody

 

[jbenson001]

It's really not that bad to convert. It will take a little while to get used to becaue you are used to the old way, as I was. But once you start using the ANSI way, you will see that it makes much more sense and will make your code more readable and easier to debug and maintain.

 

[TysonLPrice]

We still have some of these (*= and =*) floating around:

In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators.

 

[SQLSister]

TysonPrice, look out for those *= and =* joins. They have been deprecated and even in SQl server 2000, they do not work correctly, sometimes they get interpreted as a cross join. I'd make it a priority to start replacing those.

"NOTHING is more important in a database than integrity." ESquared

 

[WoodyGuthrie]

Just so everyone knows, I learned on SS2005 (the right way). This came about because I'm rewriting a slow running query which uses multiple cursors and calls multiple table-valued function multiple times (the functions use cursors as well). The query is used by multiple reports all of which run very slowly. I know you feel my pain!

In the process I noticed that one little piece of this monster query was a subquery that uses the old-style join which was very curious. I'd seen this used once before but could not remember where. Hence the post.

When I kept digging, I finally found SQLSister's post that referred to the "Comma Join".

Thanks.

Curious. Satisfied.

Woody

 

[gmmastros]

For what it's worth, the comma join syntax vs. inner join syntax does not usually affect performance at all because the sql query engine optimizer usually creates the same execution plan for both queries.

99% of cursors don't need to be cursors. Usually they are a result of procedural programmers not understanding set based coding. SQL is a real pig when it comes to loops and/or cursors (don't think a while loop is any better than a cursor, it really isn't).

If I were you, I would concentrate on replacing the cursors with set based code. I would start with the cursors in the functions.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom