2

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.

sorimaki
  • 53
  • 6
  • 3
    Run the code on a test to see. I think it incorporates the comparison in the `ON` clause, but this syntax is so archaic that you should just test it yourself. – Gordon Linoff Sep 13 '21 at 16:38
  • Agreed, the problem is all the environments were upgrades, sigh... – sorimaki Sep 13 '21 at 16:49

2 Answers2

3

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
sorimaki
  • 53
  • 6
1

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

vladimir0ne1
  • 19
  • 1
  • No, filter conditions which depend on columns being left or right joined *must* go in the `ON` clause, or you will get incorrect results – Charlieface Sep 13 '21 at 20:42