ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Inner and outer joins
      Outer joins

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 $60 000.

CREATE VIEW V AS
SELECT employee.emp_id, dept_name
  FROM employee JOIN department
    ON employee.dept_id = department.dept_id
  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 sales_order is null-supplying.

SELECT DISTINCT V.emp_id, region, V.dept_name
  FROM V LEFT OUTER JOIN sales_order
    ON V.emp_id = sales_order.sales_rep