3

There are 

*= and =*

sql equalities in my application queries. What does it mean? I have investigated on google but i could not find sufficient information. For example :

SELECT ODR_YO_ID, OGR_OKUL_NO, OGR_ADI+' '+OGR_SOYADI OGR, COUNT(ODR_YO_ID) SAYI
FROM OGRENCI,YIL_SUBE,YIL_OGRENCI, OGRENCI_DAVRANIS 
WHERE  ODR_OLUMLU = 1 AND YO_OGR_ID = OGR_ID AND  ODR_YO_ID = YO_ID AND YO_AKTIF = 1 AND 
YO_YSB_ID = YSB_ID AND  YSB_ID = 2183 AND YSB_YIL_KOD *= ODR_YIL_KOD AND  ODR_OGR_ID =* OGR_ID  
GROUP BY ODR_YO_ID, OGR_OKUL_NO, OGR_ADI+' '+OGR_SOYADI, YO_OKUL_DEGIS_TARIHI 
ORDER BY OGR

When i execute this sql query in my local computer, i take this error: 

 Msg 102, Level 15, State 1, Line 5
 Incorrect syntax near '*='.

But i run same query in related server, this query works without any error.I use sql server 2012 express edition in my local computer and there is sql server 2005 in related server. Why ? Thanks in advance.

Grijesh Chauhan
  • 57,103
  • 20
  • 141
  • 208
rockenpeace
  • 1,011
  • 5
  • 18
  • 29
  • 3
    http://stackoverflow.com/questions/6269845/what-is-this-operand-star-equals-in-sql-server-2000. Apparently it's used as shorthand in SQL Server for a join. I've actually never seen that and it seems like an accident waiting to happen. Perhaps they removed it in 2012? – ChicagoRedSox Sep 21 '13 at 05:11
  • @ChicagoRedSox: AFAIK they were inherited from Sybase which is an ancestor of SQL Server. – mu is too short Sep 21 '13 at 05:28
  • @muistooshort - interesting. I mostly use Oracle and MySQL so I probably just haven't been around SQL Server enough to have encountered it. – ChicagoRedSox Sep 21 '13 at 05:31
  • @ChicagoRedSox: I mostly use PostgreSQL but I'm still recovering from Sybase-induced PTSD from over a decade ago :) The `*=` things were used for implicit outer joins back in the before times when no one used explicit join conditions in the FROM clause. – mu is too short Sep 21 '13 at 05:36

1 Answers1

3

*= was the ANSI-89 syntax for a left join. It is now deprecated which is why you get the error.

Similarly =* is analogous to right join.

You need to move the join clauses from the where clause to the join syntax, but without knowing which tables the fields come from, I can't be more specific

ie 

YSB_YIL_KOD *= ODR_YIL_KOD

becomes

ysbtable 
    left join odrtable 
        on YSB_YIL_KOD = ODR_YIL_KOD

and 

ODR_OGR_ID =* OGR_ID

becomes

odrtable 
    right join ogrtable
        on ODR_OGR_ID = OGR_ID

You may be able to make the query work on your local machine by changing the database compatibility level ( http://technet.microsoft.com/en-us/library/bb510680.aspx ) but I would advise rewriting it to the current syntax.

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • Is this left join or left outer join ? I have tried this but it does not work: – rockenpeace Sep 21 '13 at 09:22
  • SELECT ODR_YO_ID, OGR_OKUL_NO, OGR_ADI+' '+OGR_SOYADI OGR, COUNT(ODR_YO_ID) SAYI FROM YIL_OGRENCI,OGRENCI, YIL_SUBE LEFT OUTER JOIN (OGRENCI_DAVRANIS RIGHT OUTER JOIN ON ODR_OGR_ID = OGR_ID) ON YSB_YIL_KOD = ODR_YIL_KOD WHERE ODR_OLUMLU = 1 AND YO_OGR_ID = OGR_ID AND ODR_YO_ID = YO_ID AND YO_AKTIF = 1 AND YO_YSB_ID = YSB_ID AND YSB_ID = 2183 GROUP BY ODR_YO_ID, OGR_OKUL_NO, OGR_ADI+' '+OGR_SOYADI, YO_OKUL_DEGIS_TARIHI ORDER BY OGR – rockenpeace Sep 21 '13 at 10:35
  • Left join and left outer join are synonymous. How doesn't it work? – podiluska Sep 21 '13 at 12:29
  • I run this query in sql server management studio, but there is no data. Original query gets 19 rows,but my query gets 0 row. – rockenpeace Sep 22 '13 at 16:37
  • @rockenpeace Without knowing which tables the fields come from, I can't tell you where the error is. – podiluska Sep 22 '13 at 17:01