The tables that are referenced in an ON clause must be part of the join that the ON clause modifies. For example, the following is invalid:
FROM ( A KEY JOIN B ) JOIN ( C JOIN D ON A.x = C.x ) |
The problem is that the join condition A.x = C.x
references table A, which is not part of the join it modifies (in this case, C JOIN D
).
However, as of the ANSI/ISO standard SQL99 and Adaptive Server Anywhere 7.0, there is an exception to this rule: if you use commas between table expressions, an ON condition of a join can reference a table that precedes it syntactically in the FROM clause. Therefore, the following is valid:
FROM (A KEY JOIN B) , (C JOIN D ON A.x = C.x) |
For more information about commas, see Commas.
The following example joins the SalesOrders table with the Employees table. Each row in the result reflects rows in the SalesOrders table where the value of the SalesRepresentative column matched the value of the EmployeeID column of the Employees table.
SELECT Employees.Surname, SalesOrders.ID, SalesOrders.OrderDate FROM SalesOrders JOIN Employees ON SalesOrders.SalesRepresentative = Employees.EmployeeID; |
Surname | ID | OrderDate |
---|---|---|
Chin | 2008 | 4/2/2001 |
Chin | 2020 | 3/4/2001 |
Chin | 2032 | 7/5/2001 |
Chin | 2044 | 7/15/2000 |
Chin | 2056 | 4/15/2001 |
... | ... | ... |
Following are some notes about this example:
The results of this query contain only 648 rows (one for each row in the SalesOrders table). Of the 48,600 rows in the cross product, only 648 of them have the employee number equal in the two tables.
The ordering of the results has no meaning. You could add an ORDER BY clause to impose a particular order on the query.
The ON clause includes columns that are not included in the final result set.
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |