Search code examples
sqlsql-serversql-server-2012sql-server-2000

SQL Server 2000 join query to SQL Server 2012

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.

Solution

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