October 19, 2006 at 9:51 am
I have hit a stumbling block when converting statements with OUTER JOIN operators *= and =* to LEFT OUTER JOIN and RIGHT OUTER JOIN statements, respectively.
Here is a comparison of results from the old and new syntax:
SELECT f.hz, f.amp
FROM freq f (NOLOCK), outcome o (NOLOCK), flip fl1 (NOLOCK)
WHERE o.id =* f.id AND
fl1.od =* f.od
-- returns 479,756 rows
SELECT f.hz, f.amp
FROM freq f (NOLOCK)
right outer join outcome o (NOLOCK) on o.id = f.id
right outer join flip fl1 (NOLOCK) on fl1.od = f.od
-- returns 415,533 rows
Can anyone help me figure out why the results are not the same, and how to formulate the new version query in order to obtain the same results as the old version?
Thanks for your help!
RandyB
October 19, 2006 at 11:14 am
Have you tried running this query on both levels to see if it returns the same set?
SELECT f.hz, f.amp
FROM freq f (NOLOCK)
right outer join outcome o (NOLOCK) on o.id = f.id
right outer join flip fl1 (NOLOCK) on fl1.od = f.od
-- returns 415,533 rows
October 19, 2006 at 11:18 am
No, I'm running in Compatibility Level 80 for both queries. I'll set the level to 90 and run the modified query and post the results later today. (I'm working on another task at the moment, can't do it right now).
Thanks for your help!
Best regards, Randy
October 19, 2006 at 11:44 am
I have not used old style outer joins for some time, so maybe I am missing something, but I think:
SELECT f.hz, f.amp
FROM freq f (NOLOCK)
,outcome o (NOLOCK)
,flip fl1 (NOLOCK)
WHERE o.id =* f.id
AND fl1.od =* f.od
translates to:
SELECT f.hz, f.amp
FROM freq f WITH (NOLOCK)
LEFT JOIN outcome o WITH (NOLOCK) ON o.id = f.id
LEFT JOIN flip fl1 WITH (NOLOCK) ON fl1.od = f.od
October 19, 2006 at 11:54 am
This gives the correct result, thanks!
I used Microsoft's sample code to come up with the new syntax:
[START CODE SAMPLE]
This query uses an old-style outer join operator.
USE pubs
SELECT employee.job_id, employee.emp_id,
employee.fname, employee.minit, jobs.job_desc
FROM employee, jobs
WHERE employee.job_id *= jobs.job_id
ORDER BY employee.job_id
This query uses the ANSI standard keywords LEFT OUTER JOIN.
USE pubs;
SELECT employee.job_id, employee.emp_id,
employee.fname, employee.minit, jobs.job_desc
FROM employee LEFT OUTER JOIN jobs ON
employee.job_id = jobs.job_id
ORDER BY employee.job_id
[END CODE SAMPLE]
I didn't think to try LEFT OUTER JOIN, since in the Microsoft example, *= is a left outer join, so I assumed that =* is a right outer join. Go figure . . . .
Thanks!
RandyB
October 20, 2006 at 2:09 am
With the =* and *= notation, I find it best to think of the * being against the table where every row is displayed.
eg. Assuming AID does not allow nulls:
SELECT col1 ...
FROM A, B
WHERE B.AID =* A.AID
is the same as:
SELECT col1 ...
FROM A, B
WHERE A.AID *= B.AID
is the same as:
SELECT col1 ...
FROM A
LEFT JOIN B ON A.AID = B.AID
October 20, 2006 at 9:13 am
Hi, Ken:
Thanks! I resolved the issue by changing the RIGHT OUTER JOIN to a LEFT OUTER JOIN, I'll make a note that the position of the * relative to the = will indicate either LEFT or RIGHT in the OUTER JOIN statement.
Note: This is the first time I've ever posted a technical question on a forum, I'm very appreciative of the responses.
Best regards, Randy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply