SQL Syntax For Right Outer JOIN in SQL Server 2012

Question

We're having an issue with SQL Server 2012 due to the presence of =* (RIGHT OUTER JOIN) operator.

Can anyone tell me what is the correct syntax for SQL Server 2012 for the following SQL that worked correctly on SQL Server 2008?

  SELECT 
    ProcessCode, 
    ProcessDesc, 
    DisciplineDesc, 
    ValidProcessName .DisciplineCode 
 FROM 
    ValidName, ValidProcessName 
 WHERE
    ValidProcessName.DisciplineCode =* ValidName.DisciplineCode 
 ORDER BY 
    ProcessCode

Answer 1

So the first thing to note is that a RIGHT join is the same as a LEFT join, but with the table orders swapped around.

Personally I never use RIGHT joins for this reason and for readability (I read from left-to-right and therefore the query makes more sense to me).

So your query could become:

SELECT <missing_alias>.ProcessCode
     , <missing_alias>.ProcessDesc
     , <missing_alias>.DisciplineDesc
     , ValidProcessDiscipline.DisciplineCode
FROM   ValidProcessDiscipline
 LEFT
  JOIN ValidProcess
    ON ValidProcess.DisciplineCode = ValidProcessDiscipline.DisciplineCode
ORDER
    BY ProcessCode

Grab all the ValidProcessDiscipline records and any matching ValidProcess records.

Of course if you insist on using a RIGHT join then:

SELECT <missing_alias>.ProcessCode
     , <missing_alias>.ProcessDesc
     , <missing_alias>.DisciplineDesc
     , ValidProcessDiscipline.DisciplineCode
FROM   ValidProcess
 RIGHT
  JOIN ValidProcessDiscipline
    ON ValidProcessDiscipline.DisciplineCode = ValidProcess.DisciplineCode 
ORDER
    BY ProcessCode