In SQL Server, sometimes it is necessary to retrieve, in addition to the matching rows, the unmatched rows from one or both of the tables. Such an operation is called an outer join.
Examples 1 and 2 show the difference between a natural join and the corresponding outer join. (All examples in this section use the employee_enh table.)
EXAMPLE 1
Get full details of all employees, including the location of their department, who live and work in the same city:
The result is:
Example 1 uses a natural join to display the result set of rows. If you would like to know all other existing living places of employees, you have to use the (left) outer join. This is called a left outer join because all rows from the table on the left side of the operator are returned, whether or not they have a matching row in the table on the right. In other words, if there are no matching rows in the table on the right side, the outer join will still return a row from the table on the left side, with NULL in each column of the other table (see Example 2). The Database Engine uses the operator LEFT OUTER JOIN to specify the left outer join.
A right outer join is similar, but it returns all rows of the table on the right of the symbol. The Database Engine uses the operator RIGHT OUTER JOIN to specify the right outer join.
EXAMPLE 2
Get full details of all employees, including the location of their department, for all cities that are either the living place only or both the living and working place of employees:
The result is:
As you can see, when there is no matching row in the table on the right side (department, in this case), the left outer join still returns the rows from the table on the left side (employee_enh), and the columns of the other table are populated by NULL values. Example 3 shows the use of the right outer join operation.
EXAMPLE 3
Get full details of all departments, as well as all living places of their employees, for all cities that are either the location of a department or the living and working place of an employee:
The result is:
In addition to the left and right outer joins, there is also the full outer join, which is defined as the union of the left and right outer joins. In other words, all rows from both tables are represented in the result set. If there is no corresponding row in one of the tables, its columns are returned with NULL values. This operation is specified using the FULL OUTER JOIN operator.
Every outer join operation can be simulated using the UNION operator plus the NOT EXISTS function. Example 4 is equivalent to the example with the left outer join (Example 2).
EXAMPLE 4
Get full details of all employees, including the location of their department, for all cities that are either the living place only or both the living and working place of employees:
The first SELECT statement in the union specifies the natural join of the tables employee_enh and department with the join columns domicile and location. This SELECT statement retrieves all cities that are at the same time the living places and working places of each employee. The second SELECT statement in the union retrieves, additionally, all rows from the employee_enh table that do not match the condition in the natural join.
Leave a Reply