CODEWITHSUNDEEP
sql-server
Pukitta
Pukitta

Reputation: 25

Using CASE WHEN with *= operator in old SQL language

I am trying to read this ancient SQL code and am having trouble interpreting the old style join query. Here is what the code looks like (the SELECT section omitted because I only have problem with the WHERE part:

  select 
  from        SAMPLE0 A,
              TABLE#DATA#SAMPLE1 B
              TABLE#DATA#SAMPLE2 C
              TABLE#DATA#SAMPLE3 D
              TABLE#DATA#SAMPLE4 E
  where       A.var1 = B.var1
  and         case when A.var2 = 'X' then A.var3 else A.var4 END *= B.var2
  and         convert(numeric(10,0),A.var5) *= convert(numeric(10,0),C.var2)
  and         D.var1 *= E.var1
  and         A.var8 >= 2000

What does this mean in recent SQL language? Especially the CASE WHEN part.

Upvotes: 1

Views: 56

Answers (1)

Code Different
Code Different

Reputation: 93171

It's the old join syntax. Even when I first learned SQL way back in 2001, I was told not to use it. *= is the equivalent of LEFT JOIN in the new syntax.

Your query should be reworked into something like this:

select      *
from        SAMPLE0 A
left join   TABLE#DATA#SAMPLE1 B on A.var = B.var1
                                 and case when A.var2 = 'X' then A.var3 else A.var4 END = B.var2
left join   TABLE#DATA#SAMPLE2 C on convert(numeric(10,0),A.var5) = convert(numeric(10,0),C.var2)
cross join  TABLE#DATA#SAMPLE3 D
left join   TABLE#DATA#SAMPLE4 E on D.var1 = E.var1
where       A.var8 >= 2000

FROM A, B means a CROSS JOIN between A and B. When you add a WHERE clause it becomes an INNER, LEFT, or RIGHT JOIN:

FROM A, B                        -- CROSS JOIN
FROM A, B WHERE A.col1 = B.col1  -- INNER JOIN
FROM A, B WHERE A.col1 *= B.col1 -- LEFT JOIN
FROM A, B WHERE A.col1 =* B.col1 -- RIGHT JOIN

Upvotes: 3

Related Questions