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
.
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. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |