Search code examples
sqlsql-servert-sqlouter-join

What does the old-style JOIN syntax mean in T-SQL?


I need to edit old-styled T-SQL code.

  • What does =* mean?

  • What does *= mean?

I think, that they are JOIN, but I don't know, what JOIN type is each of them.


Solution

  • I found answer!

    -- Example 1: Deprecated syntax for an inner join
    SELECT [T2].[c3], [T1].[c3] 
    FROM [dbo].[Table2] T2, [dbo].[Table1] T1 
    WHERE [T1].[ID] = [T2].[ID]
    
    -- Example 2: Current syntax for an inner join
    SELECT [T2].[c3], [T1].[c3] 
    FROM [dbo].[Table2] AS T2
    INNER JOIN [dbo].[Table1] as T1
    ON [T2].[ID] = [T1].[ID]
    
    -- Example 3: Deprecated syntax for a left outer join
    SELECT [T2].[c3], [T1].[c3] 
    FROM [dbo].[Table2] T2, [dbo].[Table1] T1 
    WHERE [T1].[ID] *= [T2].[ID]
    
    -- Example 4: Fixed syntax for a left outer join
    SELECT [T2].[c3], [T1].[c3] 
    FROM [dbo].[Table2] AS T2
    LEFT OUTER JOIN [dbo].[Table1] as T1
    ON [T2].[ID] = [T2].[ID]
    
    -- Example 5: Deprecated syntax for a right outer join
    SELECT [T2].[c3], [T1].[c3] 
    FROM [dbo].[Table2] T2, [dbo].[Table1] T1 
    WHERE [T1].[ID] =* [T2].[ID]
    
    -- Example 6: Fixed syntax for a right outer join
    SELECT [T2].[c3], [T1].[c3] 
    FROM [dbo].[Table2] AS T2
    RIGHT OUTER JOIN [dbo].[Table1] as T1
    ON [T2].[ID] = [T2].[ID]
    

    https://msdn.microsoft.com/en-us/library/dd172122(v=vs.100).aspx