# SQL Server Rule: SRP0013
| | |
|----|----|
| Assembly | SqlServer.Rules |
| Namespace | SqlServer.Rules.Performance |
| Class | AvoidOuterJoinsRule |
## Rule Information
| | |
|----|----|
| Id | SRP0013 |
| Friendly Name | Existence tested with JOIN |
| Category | Performance |
| Ignorable | true |
| Applicable Types | Procedure |
| | Scalar Function |
| | Table Valued Function |
| | View |
## Description
Consider replacing the OUTER JOIN with EXISTS.
## Summary
Consider replacing the OUTER JOIN with EXISTS
### Examples
SHOULD FLAG AS PROBLEM:
```sql
SELECT a.*
FROM a
LEFT JOIN b ON a.id = b.id
WHERE b.id IS NULL
```
SHOULD NOT FLAG AS PROBLEM:
```sql
SELECT a.*, b.*
FROM a
LEFT JOIN b ON a.id = b.id
WHERE b.id IS NULL
```
```sql
SELECT a.*, b.*
FROM a
LEFT JOIN b ON a.id = b.id
```
### Remarks
<para>Requirements:
<list type="table">
<listheader>
<term>Requirement</term>
<description>Example</description>
</listheader>
<item><term>OUTER JOIN to relation</term><description><c>FROM a LEFT OUTER JOIN b</c> or <c>FROM a RIGHT JOIN b</c></description></item>
<item><term>OUTER reference<c>b</c> <b>is not</b> referenced in SELECT </term><description> <c>SELECT a.*</c></description></item>
<item><term>OUTER reference <b>has</b> an <c>IS NULL</c> filter on JOIN member</term><description><c>WHERE b.id IS NULL</c></description></item>
</list>
</para>
<para>Description:
The traditional method of checking for row existence is to use a LEFT JOIN and checking the null-ability of a LEFT JOIN'ed column in the WHERE clause.
This method causes SQL Server to load all of the rows from the OUTER JOIN'ed table.
In cases where the matched rows are significantly less than the total rows, it is unnecessary work for SQL Server.
</para>
<para>
Alternatively checking for existence is using the EXISTS predicate function.
This is preferably to the LEFT JOIN method, since it allows SQL Server to find a row and quit(using a row count spool), avoiding unnecessary row loading.
</para>
<para>
Counter Indications:
<list type="bullet">
<item>If there are joins in the <c>EXISTS (subquery)</c>
SQL Server will favor performing loop joins through the tables, hoping to find a row quickly.
In certain cases, loop joins may be inefficient.</item>
<item>If the SQL optimizer underestimates the <c>rowcount</c> from the table in the <c>EXISTS (subquery)</c>
The query plan may show an optimal plan but the query will perform much worse.
In these cases, it is better to resort to using a LEFT JOIN and null check.</item>
</list>
</para>
<sub><sup>Generated by a tool</sup></sub>
