Alternative to *= for SQL Server 2012

Question

Is there any alternative to use *= for queries in SQL Server 2012? Queries with *= work fine in SQL Server 2008 and SQL Server 2005 but do not work in SQL Server 2012.

SELECT
  T.DOC_TYPE, T.DOC_NO, T.DOC_DATE, T.LR_NO, T.LR_DATE,
  T.DOC_REF, T.USER_ID, T.TRUCK_NO, P.PARTY_NAME,  TA.WBSLIP_NO, 
  TA.RATE, TA.SHORTAGE, TA.OTHER, TA.SERTAX, TA.PAPAMT, TA.BROK,
  I.IM_CHLQTY, I.IM_RXDQTY, I.IM_QTY, I.IM_REF,
  (SELECT PA.PARTY_NAME 
   FROM PARTYMST PA, TRINVS 
   WHERE TRINVS.DOC_TYPE = ?DOCTYPE 
     AND TRINVS.DOC_NO BETWEEN ?FDOCNO AND ?TDOCNO 
     AND PA.PARTY_ST + PA.PARTY_NO = TRINVS.TRNP_ST + TRINVS.TRNP_NO) TRNP_NAME
FROM 
  TRINVS T, TRINVSA TA, IMTRANS I, IMTRANSA IA, PARTYMST P 
WHERE
  T.DOC_TYPE = '   30' 
  AND T.DOC_NO = 'byrod125378' I.DOC_TYPE + I.DOC_NO = T.DOC_TYPE + T.DOC_NO 
  AND IA.DOC_TYPE + IA.DOC_NO = I.DOC_TYPE + I.DOC_NO 
  AND I.DOC_TYPE *= TA.DOC_NO 
  AND P.PARTY_ST + P.PARTY_NO = T.PARTY_ST + T.PARTY_NO 
ORDER BY 
  T.DOC_NO

I got error:

incorrect syntax near *=.

Why can I not use *= for joins in SQL Server 2012?? Query works fine in SQL Server 2008 and SQL Server 2005. Is there any alternative to *= for SQL Server 2012?

Usually [LEFT JOIN](http://stackoverflow.com/questions/2541004/question-on-sql-syntax). — Joachim Isaksson, Dec 25 '13 at 13:31
Since the **ANSI / ISO SQL Standard 92** (yes - **1992** - more than 20 years ago!) - the proper JOIN syntax for **any** relational database is to use `INNER JOIN` or `LEFT OUTER JOIN` etc — marc_s, Dec 25 '13 at 13:32
*= works w. SQL Server 2008 only in compatibility mode (SQL 2000). — Bogdan Sahlean, Dec 25 '13 at 13:38
You could use this as an introduction to the proper join syntax: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html. Once you understand the basic concept, I believe you'll see how to use it. — Andriy M, Dec 25 '13 at 13:41

Joachim Isaksson · Accepted Answer · 2013-12-25T13:59:30.933

2

Ok, I'll take a stab, although the *= syntax is not very obvious when it comes to rewriting them as a LEFT OUTER JOIN so you'll really want to test that it's equivalent;

...
FROM TRINVS T
INNER JOIN PARTYMST P      
  ON P.PARTY_ST + P.PARTY_NO = T.PARTY_ST + T.PARTY_NO
INNER JOIN IMTRANS I       
  ON I.DOC_TYPE + I.DOC_NO = T.DOC_TYPE + T.DOC_NO 
INNER JOIN IMTRANSA IA     
  ON IA.DOC_TYPE + IA.DOC_NO = I.DOC_TYPE + I.DOC_NO
LEFT OUTER JOIN TRINVSA TA 
  ON I.DOC_TYPE = TA.DOC_NO
WHERE T.DOC_TYPE = '   30' AND T.DOC_NO = 'byrod125378'
ORDER BY T.DOC_NO

As a sadly somewhat incomplete summary, what you want to start with to move the join conditions from the WHERE clause to the ON clause of a JOIN is;

*= comparisons should be written as a LEFT OUTER JOIN with an = comparison in the ON clause.
=* comparisons should be rewritten as RIGHT OUTER JOIN with an = comparison in the ON clause.
= comparisons not involving a left- or right joined table should be rewritten as INNER JOIN with the comparison moved to the ON clause.
= comparisons involving right- or left joined tables are normally moved to the corresponding LEFT OUTER JOIN or RIGHT OUTER JOIN ON clause, but this is really where the mapping is no longer straight forward and may need further analysis/testing to be equivalent.

edited Dec 25 '13 at 13:59

answered Dec 25 '13 at 13:41

Joachim Isaksson

176,943
25
281
294

1

Where the `JOIN` alone could also be written (more clearly, in my opinion) as `INNER JOIN` (and `LEFT JOIN` could be also spelled out as `LEFT OUTER JOIN` - that way it's clear right from the get go that you're using `INNER` and `OUTER` joins here) – marc_s Dec 25 '13 at 13:42
1

Seconding @marc_s, as the OP is new to the concept, it would be better to show them the difference properly. – Andriy M Dec 25 '13 at 13:43
@marc_s Point taken, although I personally find the longer syntax harder to read. – Joachim Isaksson Dec 25 '13 at 13:46
i have bunch of queries. this is a sample query. so is there any standard solution? – neel desai Dec 25 '13 at 14:13
@neeldesai The closest I have seen to a tutorial on this is [here](http://www.sommarskog.se/Become-an-ANSI-star.doc) (DOC format), but I don't know of any tool that helps. – Joachim Isaksson Dec 25 '13 at 14:34

Alternative to *= for SQL Server 2012

1 Answers1