Search code examples
sqlsql-serverjoinleft-join

ANSI Joins VS NON ANSI joins

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.

Solution

  • 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;