What is Use outer joins

While not as common as inner joins, the use of outer joins in a multi-table query can provide an alternative view of your business data. As with inner joins, you will express a logical relationship between the tables. However, you will retrieve not only rows with matching attributes, but also all rows present in one or both of the tables, whether or not there is a match in the other table.

Previously, you learned how to use an INNER JOIN to find matching rows between two tables. As you saw, the query processor builds the results of an INNER JOIN query by filtering out rows that don’t meet the conditions expressed in the ON clause predicate. The result is that only rows with a matching row in the other table are returned. With an OUTER JOIN, you can choose to display all the rows that have matching rows between the tables, plus all the rows that don’t have a match in the other table. Let’s look at an example, then explore the process.

First, examine the following query, written with an INNER JOIN:

SQLCopy

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

These rows represent a match between HR.Employee and Sales.SalesOrder. Only those EmployeeID values that are in both tables will appear in the results.

Now, let’s examine the following query, written as LEFT OUTER JOIN:

SQLCopy

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

This example uses a LEFT OUTER JOIN operator, which directs the query processor to preserve all rows from the table on the left (HR.Employee) and displays the Amount values for matching rows in Sales.SalesOrder. However, all employees are returned, whether or not they have taken a sales order. In place of the Amount value, the query will return NULL for employees with no matching sales orders.