Outer joins can also be specified for views and derived tables.
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.
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;
|243||(NULL)||R & D|
|316||(NULL)||R & D|
|529||(NULL)||R & D|