SQL Server Performance

OUTER JOINS IN SQL2K5

Discussion in 'SQL Server 2005 General Developer Questions' started by stefanoale, Sep 8, 2006.

  1. stefanoale New Member

    Since the *= and =* syntax has been deprecated in SQL2K5 I'm in the process of upgrading our current queries to the new standard.

    While a simple query like this will migrate just fine:
    (database Northwind)
    OLD: select * from customers c, orders o where c.customerID *= o.customerID
    NEW: select * from customers c left outer join orders o on c.customerID = o.customerID

    Here is the problem:
    OLD: select * from customers c, orders o where c.customerID *= o.customerID
    and o.orderID = 10248

    NEW: select * from customers c left outer join orders o on c.customerID = o.customerID
    where o.orderID = 10248

    Adding a parameter restriction on the linked table brings back:
    only records meeting the criteria
    RATHER THAN
    all records from the cust table and the details from the orders table where the criteria is met.


    I'm sure this is something others had to deal with, any suggestions? I rather not import the dbs and keep them in sql 2000 version.

    Thanks in advance.
  2. stefanoale New Member

    I found a way around it, but it's quite involved and kind of ugly especially in long stored procedures<br /><br />OLD: select * from customers c, orders o where c.customerID *= o.customerID<br />and o.orderID = 10248<br /><br />NEW: select * from customers c left outer join <br />(select * from orders where orderID = 1024<img src='/community/emoticons/emotion-11.gif' alt='8)' /> o on c.customerID = o.customerID<br /><br />Any better ideas?
  3. Roji. P. Thomas New Member

    What about

    SELECT *
    FROM Customers C
    LEFT OUTER JOIN Orders O
    ON O.CustomerID = C.CustomerID
    AND O.OrderID = 10248


    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  4. Adriaan New Member

    If you want to include rows from Customers for which there is no match in O.OrderId, then first of all you need to use an outer join. Second, you need to understand the IS NULL syntax in a WHERE clause, which will find unmatched rows from the inner table.

    SELECT *
    FROM Customers C
    LEFT JOIN Orders O
    ON O.CustomerID = C.CustomerID
    WHERE O.OrderID = 10248
    OR O.OrderID IS NULL

Share This Page