SR0010: Avoid using deprecated syntax when you join tables or views

One or more joins between tables and views are using deprecated syntax (such as =, *=, or =* in a WHERE clause) instead of current syntax.

Joins that use the deprecated syntax fall into two categories:

Inner Join

For an inner join, the values in the columns that are being joined are compared by using a comparison operator such as =, <, >=, and so forth. Inner joins return rows only if at least one row from each table matches the join condition.

Outer Join

Outer joins return all rows from at least one of the tables or views specified in the FROM clause, as long as those rows meet any WHERE or HAVING search condition. If you use *= or =* to specify an outer join, you are using deprecated syntax.

To fix a violation in an inner join, use the INNER JOIN syntax, as the example later in this topic shows. For more information, see this page on the Microsoft Web site: Using Inner Joins.

To fix a violation in an outer join, use the appropriate OUTER JOIN syntax, as the examples later in this topic show. You have the following options:

• LEFT OUTER JOIN or LEFT JOIN

• RIGHT OUTER JOIN or RIGHT JOIN

  Note
  Transact-SQL supports FULL OUTER JOIN and FULL JOIN, but that type of join had no previous syntax.

For more information, see this page on the Microsoft Web site: Using Outer Joins.

You should not suppress this warning. You should fix all instances because the deprecated syntax might not work in future releases of SQL Server.

The six examples demonstrate the following options:

1. Example 1 demonstrates the deprecated syntax for an inner join.

2. Example 2 demonstrates how you can update Example 1 to use current syntax.

3. Example 3 demonstrates the deprecated syntax for a left outer join.

4. Example 4 demonstrates how you can update Example 2 to use current syntax.

5. Example 5 demonstrates the deprecated syntax for a right outer join.

6. Example 6 demonstrates how you can update Example 5 to use current syntax.

-- 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] = [T2].[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]

Concepts