November 17, 2004 at 8:22 am
I have looked at BOL as well as web searches and I cannot find a good answer to the following.
First: can you do a FULL OUTER JOIN using the WHERE clause? (I see where you can do a LEFT or RIGHT using *= or =*, respectively).
Second: is there an overwhelming reason to change from using the WHERE clause to using JOINs?
I am hoping to push for this change and I have three basic reasons:
The LEFT or RIGHT version of this is unsupported and can cause "ambiguous" results (*=, =*)
New people coming onto staff will be much more used to seeing explicit JOINs.
You can set further constraints within the JOINs to make for efficiency, [ FROM TableA A INNER JOIN TableB B ON( A.IDNumber = B.IDNumber AND UPPER( A.Color) = "GREEN") - this should make the JOIN more efficient for SQL Server to process... ].
Finally: when recoding JOINs from the WHERE clause to INNER JOINs, (no RIGHT or LEFT, just straight JOINs) is there anything I should be weary of? (oops, a preposition) On occassion I am finding different results; but that may take place in other parts of the code..., not sure. Is better to use JOIN instead of INNER JOIN when making these changes?
Thanks in advance.
I wasn't born stupid - I had to study.
November 17, 2004 at 1:32 pm
Okay.
First: No answer to the FULL OUTER JOIN question, but regarding the old JOIN syntax this might be interesting http://www.microsoft.com/sql/techinfo/tips/development/July23.asp
Second: No, not really. AFAIK is this a SQL 89 relict which still can be found in the SQL 2003 standard (Joe, correct me when I'm wrong on this!)
Third: Can you post a repro script for this? If you only use INNER JOINs it doesn't matter since the optimizer will rearrange anyway if necessary. And actually the INNER keyword should be kind of optional. So you should not find a difference at all.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 17, 2004 at 3:42 pm
Thanks Frank - I figured you or one or two other people would be the only ones to answer this...
The script is not worth anyones time; actually, I was hoping for a quick place to search and thought maybe someone had had difficulty with the same issue in the past...
As for my Second question, I am perplexed. I learned this from one of those #$@#$ 20 year olds who really knew his stuff (kids..., I hate it when they are that smart...) I assumed this would force optimizer to recognize the constaints along with the JOIN and hence make the joining of the tables more efficient, (i.e., less table scanning...)).
I wasn't born stupid - I had to study.
November 18, 2004 at 12:51 am
As for your second question:
What I wanted to say, was, that when Microsoft wants SQL 92 entry level conformance (and SQL 2003 conformance) they have to support this syntax. So there is no timing reason to rewrite your code. However, there might be other valid reasons, like the ones you've mentioned (inhouse coding standards, readability and, and, and) to make the move.
When you search the Google groups, you'll find man useful threads.
Here are some, that I find interesting:
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply