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
...	...	...

Discuss this page in DocCommentXchange.