SQL Server Performance Forum – Threads Archive

OUTER JOINS IN SQL2K5

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.

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

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

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

]]>