What is OUTER JOIN syntax

Outer joins are expressed using the keywords LEFT, RIGHT, or FULL preceding OUTER JOIN. The purpose of the keyword is to indicate which table (on which side of the keyword JOIN) should be preserved and have all its rows displayed; match, or no match.

When using LEFT, RIGHT, or FULL to define a join, you can omit the OUTER keyword as shown here:

SQLCopy

SELECT emp.FirstName, ord.Amount
FROM HR.Employee AS emp
LEFT JOIN Sales.SalesOrder AS ord
    ON emp.EmployeeID = ord.EmployeeID;

However, like the INNER keyword, it is often helpful to write code that is explicit about the kind of join being used.

When writing queries using OUTER JOIN, consider the following guidelines:

• As you have seen, table aliases are preferred not only for the SELECT list, but also for the ON clause.
• As with an INNER JOIN, an OUTER JOIN may be performed on a single matching column or on multiple matching attributes.
• Unlike an INNER JOIN, the order in which tables are listed and joined in the FROM clause does matter with OUTER JOIN, as it will determine whether you choose LEFT or RIGHT for your join.
• Multi-table joins are more complex when an OUTER JOIN is present. The presence of NULLs in the results of an OUTER JOIN may cause issues if the intermediate results are then joined to a third table. Rows with NULLs may be filtered out by the second join’s predicate.
• To display only rows where no match exists, add a test for NULL in a WHERE clause following an OUTER JOIN predicate.
• A FULL OUTER JOIN is used rarely. It returns all the matching rows between the two tables, plus all the rows from the first table with no match in the second, plus all the rows in the second without a match in the first.
• There is no way to predict the order the rows will come back without an ORDER BY clause. There’s no way to know if the matched or unmatched rows will be returned first.