I was working on an old app at work while we were in the process of moving to SQL 2005. I ran across a query like this:

SELECT pt.id, IsNull(disp_id,0) AS disps
FROM product_types pt, dispenser_products dp
WHERE pt.prod_type_id *= dp.prod_type_id AND disp_id=13
AND pt.prod_type_id = 42

and got this error message the new server:

[Microsoft][ODBC SQL Server Driver][SQL Server]The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes

I dutifully changed the query to an outer join and removed the *= (see below), but it did not give the same results as the original query.

SELECT pt.id, IsNull(disp_id,0) as disps
FROM product_types pt
LEFT JOIN dispenser_products dp ON pt.prod_type_id = dp.prod_type_id
WHERE pt.prod_type_id =13
AND dp.disp_id = 42

It turns out this is the correct query:

SELECT pt.id, IsNull(disp_id,0) as disps
FROM product_types pt
LEFT JOIN dispenser_products dp ON pt.prod_type_id = dp.prod_type_id AND dp.disp_id = 42
WHERE pt.prod_type_id =13
ORDER BY prod_type_display_order

I had to put "dp.disp_id = 42" as part of the JOIN and not part of the WHERE. But putting the other part of the WHERE into the JOIN doesn't give the correct results either.

How did SQL interpret the original query correctly and know which part of the WHERE clause went where? How exactly does the "*=" work?

This is more of an informational post. I'm not worried about the answer. I had a terrible time trying to search here and Google for "*=" though. Hopefully this post will help someone else.


Jeff

This is exactly what I was looking for ! I was having the same issue and was dumb founded until I found this post ! You just saved me a lot of time and effort! Thanks a heap !

Here is the example.

STUDENT_MASTER
stud_id, stud_name
1 , 'Steve'
2, 'Andrew'
3, 'Bucknor'
4, 'Flintoff'
5, 'John'


STUDENT_MARK
stud_id,stud_marks
2, 87
4, 56


SELECT A.STUD_ID, A.STUD_NAME, B.STUD_MARK
FROM STUDENT_MASTER A INNER JOIN STUDENT_MARKS B ON A.STUD_ID=B.STUD_ID
            OR
SELECT A.STUD_ID, A.STUD_NAME, B.STUD_MARK
FROM STUDENT_MASTER A RIGHT OUTER JOIN STUDENT_MARKS B ON A.STUD_ID=B.STUD_ID

Result
STUD_ID,STUD_NAME,STUD_MARK
2, 'Andrew', 87
4, 'Flintoff', 56


SELECT A.STUD_ID, A.STUD_NAME, B.STUD_MARK
FROM STUDENT_MASTER A LEFT OUTER JOIN STUDENT_MARKS B ON A.STUD_ID=B.STUD_ID

Result
STUD_ID,STUD_NAME,STUD_MARK
1 , 'Steve', NULL
2, 'Andrew', 87
3, 'Bucknor', NULL
4, 'Flintoff', 56
5, 'John', NULL

In all case STUDENT_MASTER is at LEFT POSITION and STUDENT_MARKS is at RIGHT POSITION

urt

Consider this example:



create table #foo
(
  a int,
  b int
)

insert into #foo values(1,1)
insert into #foo values(2,2)
insert into #foo values(3,3)
insert into #foo values(4,4)

create table #bar
(
  c int,
  d int
)

insert into #bar values(3,3)
insert into #bar values(4,4)
insert into #bar values(5,5)
insert into #bar values(6,6)



Query 1:


select *
from #foo left join #bar
  on #foo.a = #bar.c
where #foo.b = 3


Returns this

a b c d
----------- ----------- ----------- -----------
3 3 3 3

Query 2:



select *
from #foo left join #bar
  on #foo.a = #bar.c
     and #foo.b = 3


returns

a b c d
----------- ----------- ----------- -----------
1 1 NULL NULL
2 2 NULL NULL
3 3 3 3
4 4 NULL NULL

Query 3:


select *
from #foo, #bar
where #foo.a *= #bar.c
  and #foo.b = 3


returns

a b c d
----------- ----------- ----------- -----------
3 3 3 3

The where clause in query 1 is not part of the left join, and so it eliminates everything were #foo.b <> 3. In query 2, the #foo.b restriction is part of the join clause, so it doesn't eliminate any rows from #foo, it just replaces the columns of #bar with NULL when it can't match. The surprising thing about Query 2 is it retains everything from #foo where #foo.b <> 3.

Query 3 and Query 1 are equivalent, but Query 2 has surprising behavior. Nothing from #foo is eliminated in Query 2 because #foo.b <> 3; instead the database left joins every row where #foo.b <> 3.

JWChoc: Are you asking a question or just trying to say the same thing that was said a long time ago??

If you are asking a question, then...

You wrote:
 

I completely disagree. Query 2 has EXACTLY the correct and expected behavior, if you understand how JOINs are supposed to work and go back to first principles of relational databases.

  Awesome, Jeff... very cool to see someone share a part of their brain like that.

--Jeff Moden

Ummm...Mr. Moden, did you note that Mr. Kanning posted that back in December??

Note that Jeff Kanning didn't really understand why the second condition had to be moved into the ON clause, but if you perform the full Cartesian Product and then apply the conditions, you'll see why.

Yes... saw that it was done way back... still wanted to thank the guy that started it.

--Jeff Moden

Gotcha. Don't think he'll ever see your thanks, since that was the one and only post he ever made here (check his total number of posts), but w.t.h.

I posted the "why" of this a long time ago at 4GuysFromRolla, and there's a great article on SQLTeam.com about it, but I've never seen a "why" that fully explains what JWChoc showed. Wonder if it's worth writing it up.

I wouldn't think so... the non-Ansi outer joins haven't worked since SQL Server 7 which does have Ansi join capability.

--Jeff Moden