August 10, 2010 at 6:55 am
[font="Verdana"]if i have non-ansi syntax query as
SELECT A.COL1, B.COL1, A.COL2, B.COL2
FROM A, B
WHERE A.COL1 *= B.COL1
AND A.COL2 = B.COL2
if i need to change the query to ANSI-Standards then what would be the resultant query. and interestingly
what would be result .... a left outer join or an inner join of these two tables.
If second AND condition is made part of LEFT OUTER JOIN the result will be LEFT OUTER JOIN. but
if the second AND is presented in WHERE clause then ... it will result into INNER JOIN.
How this scenario was handled in non-ANSI standard queries (with any internal magic/logic :angry: )
Please let help-out.. !
Thanks
[/font]
August 10, 2010 at 9:24 am
I wonder what you expect from your current query?
It is kind of LEFT JOIN, but you filtering results for A.COL2 = B.COL2, why bother to have A.COL1 *= B.COL1?
Your current query actually is INNER JOIN on COL2 with no care if values are equal in COL1. Basically your current non-ANSI query can be written as:
SELECT A.COL1, B.COL1, A.COL2, B.COL2
FROM A, B
WHERE A.COL2 = B.COL2
And that is simply reaplacable with:
SELECT A.COL1, B.COL1, A.COL2, B.COL2
FROM A
JOIN B
ON A.COL2 = B.COL2
August 11, 2010 at 12:32 am
Eugene Elutin (8/10/2010)
I wonder what you expect from your current query?It is kind of LEFT JOIN, but you filtering results for A.COL2 = B.COL2, why bother to have A.COL1 *= B.COL1?
Your current query actually is INNER JOIN on COL2 with no care if values are equal in COL1. Basically your current non-ANSI query can be written as:
SELECT A.COL1, B.COL1, A.COL2, B.COL2
FROM A, B
WHERE A.COL2 = B.COL2
And that is simply reaplacable with:
SELECT A.COL1, B.COL1, A.COL2, B.COL2
FROM A
JOIN B
ON A.COL2 = B.COL2
[font="Verdana"]Dear Eugene Elutin,
I wish to have such simple and trivial solution. and i came to this discussion board for the sake of clarity, as i already have witnessed same sensed query in which SQL Engine Left Joined the concerned tables.
And in other scenario, the result was opposite (Inner Join).....!!!
So my peak curiosity is to discuss this strange thing, and its unpredicted results.
or at least find any rule of thumb to tailor/re-tailor such queries.
Please?:rolleyes:[/font]
August 11, 2010 at 1:06 am
CELKO (8/10/2010)
...SELECT A.col1, B.col1, A.col2, B.col2
FROM A LEFT OUTER JOIN B
ON A.col1 = B.col1
AND A.col2 = B.col2;
or as:
SELECT A.col1, B.col1, A.col2, B.col2
FROM A LEFT OUTER JOIN B
ON A.col1 = B.col1
WHERE A.col2 = B.col2;
.....
When you are converting a "code museum" to ANSI Standard, it is probably best to go back to the original specs and do it from scratch. You will usually find a nicer way to do the job.
[font="Verdana"]
Yes, definitely we have the both alternatives. First alternative is as i perceived is Left Outer Join and second alternative as Inner Join (as WHERE A.col2 = B.col2 condition would be mandatory and results only qualified rows)! Correct?
So what was the default behavior of NON-Standard Query? Is there was any concept of giving priority to some join operator to other? e.g the Left outer join would suppress second condition of A.col2 = B.col2 or vice versa?
But i really appreciate the term of "code museum", but i need to manage the museum by any means.... 😀
Thank you[/font]
August 11, 2010 at 4:15 am
Hard to say about all SQLServer versions...
In SQL2008 setting compatibility level to 80, allows to execute queries with non-ANSI joins and the result of your query is the same as for pure LEFT JOIN.
Actually trying
SELECT A.COL1, A.COL2, B.COL1, B.COL2
FROM A, B
WHERE A.COL1 *= B.COL1
AND A.COL2 *= B.COL2
Produces the same result as:
SELECT A.COL1, A.COL2, B.COL1, B.COL2
FROM A, B
WHERE A.COL1 *= B.COL1
AND A.COL2 = B.COL2
I can't remember what would happen in old SYBASE (last time used it 14 years ago) , but it may be just lazzy writing. I would prefer to use * in both joins to clearly mark its "LEFT" nature.
So, I guess you can safely replace it with LEFT JOIN:
SELECT A.COL1, A.COL2, B.COL1, B.COL2
FROM A LEFT JOIN B
ON A.COL1 = B.COL1
AND A.COL2 = B.COL2
However, the valid advice from Joe (and that is first time when I unreservedly agree with him on the matter) is: "Look into original requirements and implement it from scratch" - this would be the best and safest way...
I can bet on that you don't have the original specs... Then contact business and confirm requirements.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply