when not to use *= for joins

Question

when not to use *= for joins

bp

SSCarpal Tunnel

Points: 4656
More actions
February 10, 2004 at 5:32 pm

#161616

I've always used the left outer join operator instead of the "*=" legacy syntax but below has been brought to my attention as a reason not to use the "*=". Contrary to my initial understanding, the two select statements below actually produce different results.
DECLARE @order TABLE(order_ID INT)
DECLARE @orderdetail TABLE(orderdetail_ID INT, orderdetail_order_ID INT)
INSERT INTO @order VALUES(1);
INSERT INTO @order VALUES(2);
INSERT INTO @order VALUES(3);
INSERT INTO @order VALUES(4);
INSERT INTO @orderdetail VALUES(1,1);
INSERT INTO @orderdetail VALUES(1,2);
INSERT INTO @orderdetail VALUES(1,2);
INSERT INTO @orderdetail VALUES(1,2);
SELECT *
FROM @order x, @orderdetail y
WHERE order_ID *= orderdetail_order_ID AND y.orderdetail_ID = 1;
SELECT *
FROM @order x LEFT JOIN @orderdetail y ON order_ID = orderdetail_order_ID
WHERE y.orderdetail_ID = 1;
If anyone has any comments about this, I would be interested in reading it.
Sincerely,
Billy

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

From BOL

The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

Kindest Regards,

philcart SSC-Forever Points: 47795 More actions · Answer 2

It's nothing to do with the join syntax. In an outer join query, you can't specify criteria in the where clause for the table on the opposite side of the outer join. eg: if you are using a left join, you can't specify criteria for the table on the right and vice-versa. Any criteria for the table on the opposite side of an outer join must be specified as part of the join clause.

Your LEFT JOIN query should be written as

SELECT *
FROM @order x 
LEFT JOIN @orderdetail y 
ON order_ID = orderdetail_order_ID
AND y.orderdetail_ID = 1;

--------------------
Colt 45 - the original point and click interface

bp SSCarpal Tunnel Points: 4656 More actions · Answer 3

Thanks Phill for posting the rewritten LEFT JOIN query.

Actually, it now got me thinking which version of the query is indeed "more acceptable" even though you are not supposed to specify criteria in the where clause of the opposite table.

At first I thought that the resultset that returned only orderdetail_id = 1 (and excluding orderdetail=null) was correct because that was the purpose of the orderdetail=1 clause. However, doing so would have the same effect as an INNER JOIN query. Putting the criteria outside of the WHERE clause is preserving the "OUTER JOIN"ness of the query, which seems to me to be "more acceptable".

Billy

inugroho SSC Enthusiast Points: 145 More actions · Answer 4

I do know whether this more acceptable, but you can do like this

SELECT * FROM @order x LEFT JOIN @orderdetail y ON order_ID = orderdetail_order_IDwhere isnull(y.orderdetail_ID,1) = 1;

inugroho SSC Enthusiast Points: 145 More actions · Answer 5

I do know whether this more acceptable, but you can do like this

SELECT * FROM @order x LEFT JOIN @orderdetail y ON order_ID = orderdetail_order_IDwhere isnull(y.orderdetail_ID,1) = 1;

Thanx