SQL Server 2000 join query to SQL Server 2012

Question

I have the following SQL Server 2000 query:

select distinct 
    m.[Index], m.part as [Part No] 
from 
    Ctable c, MTable m 
where 
    m.part *= c.part

Is the corresponding SQL Server 2012 query for it this?

select distinct 
    m.[Index], m.part as [Part No] 
from 
    Ctable c 
right join  
    MTable m on m.part = c.part

So basically Asterisk= is left join and =Asterisk is right join? I had to do right join on the above query since MTable is the right table on the 2012 query. Please advice.

Can you improve your title please? Make it so that it provides a summary of your issue, and don't include tags in your title. — rory.ap, Dec 18 '15 at 15:51
This isn't so much a SQL 2000 vs SQL 2012 thing as it is a difference in ANSI JOIN syntax. I'm not familiar with the nuances of `*=` or `=*` syntax without looking it up, but your second query is the "current" joining syntax, and as far as I know is valid on any SQL Server back to 2000. See here for more: http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax — Peter Tirrell, Dec 18 '15 at 16:20
That is REALLY old syntax for left and right joins. That join syntax was deprecated with ANSI-89 and is no longer supported. Most people tend to do left joins and think that way. Your right join appears to be correct or you could move it around to a left join by making MTable be the from and left join to CTable. — Sean Lange, Dec 18 '15 at 17:25

score 1 · Accepted Answer · answered Dec 18 '15 at 22:20

First congrats on getting rid of those old joins, they were not even accurate in SQL server 2000 as they could be misinterpreted as cross joins. That may even be why the distinct was put in (or it could have something to with your data model, without understanding the data you have I can't tell).

I believe your conversion to the right join is correct, but check the results of both queries and make sure they are returning the same results. Then try removing the distinct and check those results to determine if it is still needed. If it was added to get rid of the possibility of cross joining, it may no longer be necessary and it is an expensive operation, so if it can be dispensed with, that is best.

score 0 · Answer 2 · answered Dec 18 '15 at 21:09

non ANSI standard outer joins were deprecated in 2000 and de-supported in 2005. See https://msdn.microsoft.com/en-us/library/ms144262(v=sql.90).aspx Users could still change db_compat level to get temporary relief for a while but the operators were completely removed in 2012 so you have no alternative.

Modify your queries to use ANSI standard outer joins, they'll work on current and legacy versions, including 2000.

score 0 · Answer 3 · answered Dec 23 '15 at 02:29

First thing is, "Asterisk= is left join and =Asterisk is right join", then, or your SQL 2012 query is incorrect or you want to change the join of the first query.

Well, if you want to move your query to SQL 2012, you need to re-write this query according with new SQL Standard... In my opinion, you could try...

This query is according your SQL query 2005

SELECT DISTINCT(m.[index]), m.part AS "Part No"
FROM
Ctable AS C
OUTER LEFT JOIN
Mtable as M ON
m.part = c.part

This query is about your question

SELECT DISTINCT(m.[index]), m.part AS "Part No"
FROM
Ctable AS C
OUTER RIGHT JOIN
Mtable as M ON
m.part = c.part

SQL Server 2000 join query to SQL Server 2012

3 Answers3