-2

I'm struggling with converting this query:

select * from T t, U u1, C c, I i, S s, TY ty, U u2 where
t.id = u1.id
t.curr = c.curr
t.instanceId = i.instanceId
i.symbol = s.symbol
i.type = ty.type
t.exec *= u2.exec

I know *= is a left join but I'm not sure exactly when I do my joins how to reference the tables properly in order when I do my joins.

user3650664
  • 161
  • 1
  • 14
  • 3
    SQL is case insensitive so this is unecessarily verbose to begin with. – Stu Mar 28 '22 at 08:30
  • Have also an eye on https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms – Reporter Mar 28 '22 at 08:30
  • https://stackoverflow.com/questions/47648402/convert-legacy-sql-outer-join-to-ansi –  Mar 28 '22 at 08:35
  • I *assume* you are suggesting the above aren't ANSI JOINs? They are, they are just based on the ANSI-89 syntax, and they were superseded by the (explicit) ANSI-92 syntax *30 years* ago. The syntax you are using above for the `LEFT JOIN` (`t.exec *= u2.exec`) was deprecated in SQL Server 2008 and support was removed in SQL Server 2012; you **cannot** use that 80's style syntax in any supported version of SQL Server (extended or fully supported). There is *literally* no reason to be using the ANSI-89 syntax any more. – Thom A Mar 28 '22 at 08:53
  • See [Bad Habits to Kick : Using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) – Thom A Mar 28 '22 at 08:54
  • @Larnu Comma with *= etc was never ANSI. – philipxy Mar 28 '22 at 09:05

1 Answers1

1

It seems that *= is RIGHT OUTER JOIN
(If the following doesn't give the expected results try it with LEFT OUTER JOIN)

select * from 
T t, 
JOIN U u1 ON t.id = u1.id
JOIN C c ON t.curr = c.curr
JOIN I i ON t.instanceId = i.instanceId 
JOIN S s ON i.symbol = s.symbol
JOIN TY ty ON i.type = ty.type
RIGHT OUTER JOIN U u2 ON t.exec = u2.exec;
  • Actually, I thought that both `*=` and `=*` can translate to either left or right join. F.e. `t1.id *= t2.t1id` versus `t2.t1id =* t1.id` – LukStorms Mar 28 '22 at 09:24
  • @LukStorms : I agree you can write `SELECT * FROM t1 left join t2 on t2.id = t1.id;` and there's nothing technically wrong with it. However it does seem back to front and harder to read. –  Mar 28 '22 at 09:29
  • That's not the point I was trying to make about the old style. But personally I think that in the JOIN style using the joined table first in the ON clause looks better if you got multiple criteria. Not that it really matters. – LukStorms Mar 28 '22 at 09:47