An extension to the ANSI/ISO SQL standard is that you can specify views or derived tables on either side of a natural join. In the following statement,
SELECT * FROM View1 NATURAL JOIN View2; |
the columns in View1 are compared to the columns in View2. If, for example, a column called EmployeeID is found to occur in
both views, and there are no other columns that have identical names, then the generated join condition is (View1.EmployeeID = View2.EmployeeID)
.
The following example illustrates that a view used in a natural join can include expressions, and not just columns, and they are treated the same way in the natural join. First, create the view V with a column called x, as follows:
CREATE VIEW V(x) AS SELECT R.y + 1 FROM R; |
Next, create a natural join of the view to a derived table. The derived table has a correlation name T with a column called x.
SELECT * FROM V NATURAL JOIN (SELECT P.y FROM P) as T(x); |
This join is equivalent to the following:
SELECT * FROM V JOIN (SELECT P.y FROM P) as T(x) ON (V.x = T.x); |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |