Your Opinion about the... SQL syntax
-
I see (so far) that most of the Topics concern questions about 'how can i do this in SQL...' and 'How can I do that'...etc, which are 100% preferable... but I would like to put a topic with a slightly diffrent theme...
I would like your opinion about the SQL syntax on joinning tables...
The "new" standard in SQL is to use the JOIN clause. Most of you I suppose you prefer this new standard because of many reasons... readability?, performance?.... but what else?
Is it so important?
And finally, what is wrong with the old way...? (to express the join in the WHERE clause).
Personally, I use SQL since 1991 and seems at least "strange" to me to use the "new" syntax of joinning tables. I love the old one..... just put the tables to the FROM clause and let the WHERE and the query optimizer to solve the puzzle...
I really feel anxious for your opinions...
------------
When you 've got a hammer, everything starts to look like a nail... -
In my mind, there is a logical separation between the function of the JOIN clause (joining tables together) and the WHERE clause (filtering the data that is returned). Shovelling everything into a WHERE clause makes the query less easy to follow (for me).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
There are many reasons to abandon the 'old' syntax and convert to the 'new' ANSI syntax, both subjective and objective ones.
For equi-joins (inner) there is no functional difference. The old legacy syntax and newer ANSI works exactly the same.
For outer joins there is a massive difference in functionality (internally), up to the point that I dare say that the old style outer joins is plain broken and doesn't work. At least, it's very easy to put oneself into a situation where a legacy outer join produces one result (which is faulty) and rewritten in ANSI works correctly. This phenomenon doesn't happen every time, but given the right circumstances one could end up there.
ANSI syntax is easier to write, read and understand. (this is a very subjective point
)There are constructs that's impossible to write in legacy syntax, but very easy in ANSI.
However, the old syntax is on the soon-to-be-deprecated list, and has been recommended against for quite a long time by MS. (I believe since 7.0 even) It's perfectly possible that in the not-so distant future the legacy syntax just won't work with the newer versions of SQL Server, which would make a good case on why to move away from it.
I do remember when I changed style myself. It was very awkward in the beginning, and I too was very happy with the 'old' ways. Though as most things that are based on habit, once you get used to it, I appreciated the new style much more, and has since never regretted (nor wished) for the 'old' again. There is absolutely nothing in the legacy way that you can't do in ANSI style, though there's a lot you can do in ANSI you can't do the old way.
End point - to progress one must move forward
/Kenneth
-
The big thing is Readability. This is why the standards board approved the new syntax. It has been a pain in the past to debug queries which contained multiple tables especialy if a single table was referenced multiple times. Then when you get into I want to join on the equal values of table 1 with table 2 (while keeping all of table 1's data otherwise) and only where table 2 meets mutiple other conditions. The new syntax greatly reduces mistakes and improves the time it takes to troubleshoot the issue.
-
Beautify it before read you SQL maybe a good choice, try a nice tool here:
-
I agree with the above. I started in 91 with the old syntax and never had issues. Outer joins were tricky, but they're still tricky.
However I swithed to ANSI syntax around 2000, and it was an adjustment. But it is definitely more readable and easier to follow.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply