Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Usage » Querying and Modifying Data » Joins: Retrieving data from several tables


Explicit join conditions (the ON clause)

Instead of, or along with, a key or natural join, you can specify a join using an explicit join condition. You specify a join condition by inserting an ON clause immediately after the join. The join condition always refers to the join immediately preceding it. The ON clause applies a restriction to the rows in a join, in much the same way that the WHERE clause applies restrictions to the rows of a query.

The ON clause allows you to construct more useful joins than the CROSS JOIN. For example, you can apply the ON clause to a join of the SalesOrders and Employees table to retrieve only those rows for which the SalesRepresentative in the SalesOrders table is the same as the one in the Employees table in every row of the result. Then each row contains information about an order and the sales representative responsible for it.

For example, in the following query, the first ON clause is used to join SalesOrders to Customers. The second ON clause is used to join the table expression (SalesOrders JOIN Customers) to the base table SalesOrderItems.

FROM SalesOrders JOIN Customers
    ON SalesOrders.CustomerID = Customers.ID
  JOIN SalesOrderItems
    ON SalesOrderItems.ID = SalesOrders.ID;
Tables that can be referenced

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:


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:


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.

Generated joins and the ON clause
Types of explicit join conditions
Using the WHERE clause for join conditions