Outer joins of views and derived tables

Outer joins can also be specified for views and derived tables.

The statement

SELECT *
FROM V LEFT OUTER JOIN A ON (V.x = A.x);

can be interpreted as follows:

  • Compute the view V.

  • Join all the rows from the computed view V with A by preserving all the rows from V, using the join condition V.x = A.x.

Example

The following example defines a view called V that returns the employee IDs and department names of women who make over $60000.

CREATE VIEW V AS
SELECT Employees.EmployeeID, DepartmentName
  FROM Employees JOIN Departments
    ON Employees.DepartmentID = Departments.DepartmentID
  WHERE Sex = 'F' and Salary > 60000;

Next, use this view to add a list of the departments where the women work and the regions where they have sold. The view V is preserved and SalesOrders is null-supplying.

SELECT DISTINCT V.EmployeeID, Region, V.DepartmentName
  FROM V LEFT OUTER JOIN SalesOrders
    ON V.EmployeeID = SalesOrders.SalesRepresentative;
EmployeeID Region DepartmentName
243 (NULL) R & D
316 (NULL) R & D
529 (NULL) R & D
902 Eastern Sales
... ... ...