CODEWITHSUNDEEP
sqlsql-server
sorimaki
sorimaki

Reputation: 53

Converting deprecated outer join operators in SQL server, *= to left join

I have to convert an old query from the deprecated *= to the left join statement. I am not particularly familiar with the old operator, and I'm not sure if the query I'm trying to convert is badly written, or if that's the correct notation. I'll try to explain what I mean:

SELECT GivenName, Surname, OrderNumber
FROM Customers, SalesOrders
WHERE Customers.ID *= SalesOrders.CustomerID
and SalesOrders.OrderNumber = 1000 -- ???

Is OrderNumber = 1000 part of the left join? If OrderNumber is not part of the left join, then the use of the *= operator seems pointless.

Or, in other terms, which is the equivalent code:

SELECT GivenName, Surname, OrderNumber
FROM Customers LEFT JOIN SalesOrders
ON Customers.ID = SalesOrders.CustomerID 
and SalesOrders.OrderNumber = 1000

or

SELECT GivenName, Surname, OrderNumber
FROM Customers LEFT JOIN SalesOrders
ON Customers.ID = SalesOrders.CustomerID 
WHERE SalesOrders.OrderNumber = 1000

In the 2'nd query, the left join would again be pointless.

Upvotes: 2

Views: 693

Answers (2)

vladimir0ne1
vladimir0ne1

Reputation: 19

In this particular case all 3 code blocks will generate the same execution plan and provide same results.

*= means LEFT and = means INNER.

I assume we will never know original requirements but in this case choise of LEFT vs INNER does not depend on the OrderNumber = 1000 condition and you have to use LEFT join anyway.

It is a question of readability and it is a good practice to separate filters from join conditions, espesially in cases with multiple tables joins. So just put filters in WHERE and join conditions in ON clauses just to avoid situations when you don't know "is it a part of JOIN or WHERE" so the last code block looks fine.

A good description of the similar situation: https://stackoverflow.com/a/2510059/3441990

And you can find more about legacy join syntax here: Old-style outer joins are obsolete

Upvotes: 1

sorimaki
sorimaki

Reputation: 53

Answering my own question - after finding an old environment - thank you Gordon for the suggestion -

SELECT GivenName, Surname, OrderNumber
FROM Customers, SalesOrders
WHERE Customers.ID *= SalesOrders.CustomerID
and SalesOrders.OrderNumber = 1000

is correct and equivalent with

SELECT GivenName, Surname, OrderNumber
FROM Customers LEFT JOIN SalesOrders
ON Customers.ID = SalesOrders.CustomerID 
and SalesOrders.OrderNumber = 1000

Upvotes: 3

Related Questions