Post reply

when not to use *= for joins

  • 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

  • February 10, 2004 at 5:38 pm

    #493538

    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,

  • February 10, 2004 at 9:19 pm

    #493547

    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

  • February 11, 2004 at 11:17 am

    #493690

    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

  • February 12, 2004 at 5:10 am

    #493774

    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;

  • February 12, 2004 at 5:12 am

    #493775

    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

     

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

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