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

RuleId

SR0010

Category

Microsoft.Design

Breaking Change

Non-breaking

Cause

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

Rule Description

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.

How to Fix Violations

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.

When to Suppress Warnings

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

Example

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]

See Also

Concepts

Analyzing Database Code to Improve Code Quality