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

SQL Anywhere 10.0.1 » UltraLite - Database Management and Reference » UltraLite SQL Statement Reference » UltraLite SELECT statement

UltraLite SELECT statement Next Page

FROM clause

Specifies the database tables or views involved in a SELECT statement.


FROM table-expression, ...

table-expression :
[ table-name
[ [ AS ] correlation-name ]
| ( SELECT-expression )
[ AS ] derived-table-name ( column-name, ... )
| (table-expression)
| table-expression join-operator table-expression [ ON search-condition ]

join-operator :


table-name    A base table or temporary table. Tables cannot be owned by different users in UltraLite. If you qualify tables with user ID, the ID is ignored.

correlation-name    An identifier to use when referencing an object elsewhere in the statement.

If the same correlation name is used twice for the same table in a table expression, that table is treated as if it were listed only once. For example, the following two SELECT statements are equivalent:

FROM sales_order
CROSS JOIN sales_order_items,
CROSS JOIN employee
FROM sales_order
CROSS JOIN sales_order_items
CROSS JOIN employee

Whereas the following would be treated as two instances of the Person table, with different correlation names HUSBAND and WIFE:


derived-table-name     A derived table is a table expression that you specify as a SELECT statement. Following the parenthesized SELECT statement, is the name of the derived tabled and the parenthesized list of derived column names. You must include a derived column name for each select expression in the derived table.

Items from the select list of the derived table are referenced by the (optional) derived table name followed by a period (.) and the column name. You can use the column name by itself if it is unambiguous.

You cannot reference derived table names within the SELECT statement. These references are sometimes called inner references. See Subqueries in expressions.


When there is no FROM clause, the expressions in the SELECT statement must be constant expressions.

Derived tables

Although this description refers to tables, it also applies to derived tables unless otherwise noted.

The FROM clause creates a result set consisting of all the columns from all the tables specified. Initially, all combinations of rows in the component tables are in the result set, and the number of combinations is usually reduced by JOIN conditions and/or WHERE conditions.

The join operator connects two tables based on common column names. Supported operators in UltraLite are:

These operators can take specific conditions as specified above. The ON condition is specified for a single join operation and indicates how the join is to create rows in the result set. The JOIN operator always requires an ON condition.

A WHERE clause is used to restrict the rows in the result set, after potential rows have been created by a join.

Comma joins are the same as a CROSS JOIN. You cannot use an ON phrase with either this operator.

For INNER joins restricting with an ON or WHERE is equivalent. For OUTER joins, they are not equivalent.


UltraLite does not support KEY JOINS nor NATURAL joins.

See also

The following are valid FROM clauses:

FROM employee
FROM employee NATURAL JOIN department
FROM customer
CROSS JOIN sales_order
CROSS JOIN sales_order_items
CROSS JOIN product

The following query illustrates how to use derived tables in a query:

SELECT lname, fname, number_of_orders
FROM customer JOIN
     ( SELECT cust_id, COUNT(*)
       FROM sales_order
        GROUP BY cust_id )
     AS sales_order_counts( cust_id,
                             number_of_orders )
ON ( = sales_order_counts.cust_id )
WHERE number_of_orders > 3