Outer Join Examples

原创转载请注明出处：http://agilestyle.iteye.com/blog/1551914

 

To give you a better understanding of how outer joins work, here gives three more examples. These examples use the Departments and Employees tables shown at the top of this figure. In each case, the join condition joins the tables based on the values in their department_number columns.

The first SELECT statement performs a left outer join on these two tables. In the result set produced by this statement, you can see that department number 3(Operations) is included in the result set even though none of the employees in the Employees table work in that department. Because of that, a null value is assigned to the last_name column from that table.

 

The second SELECT statement uses a right outer join. In this case, all of the rows from the Employees table are included in the result set. Notice, however, that two of the employees, Locario and Watson, are assigned to a department that doesn't exist in the Departments table. Of course, if the department_number column in this table had been defined as a foreign key to the Departments table, this would not have been allowed. In this case, though, a foreign key wasn't defined, so null values are returned for the department_name column in these two rows.

 

The third SELECT statement in this figure illustrates a full outer join. If you compare the results of this query with the results of the queries that use a left and right outer join, you'll see that this is a combination of the two joins. In other words, each row in the Employees table. Because the department_number column from both tables is included in this example, you can clearly identify the row in the Departments table that doesn't have a matching row in the Employees table and the two rows in the Employees that don't have matching rows in the Departments table.