Specifies the database tables or views involved in a SELECT statement.
FROM table-expression, ...
[ [ AS ] correlation-name ]
| ( SELECT-expression )
[ AS ] derived-table-name ( column-name, ... )
| table-expression join-operator table-expression [ ON search-condition ]
, | CROSS JOIN | INNER JOIN | LEFT OUTER JOIN | JOIN
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:
SELECT * FROM sales_order CROSS JOIN sales_order_items, sales_order CROSS JOIN employee SELECT * 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:
SELECT * FROM Person HUSBAND, Person 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.
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:
LEFT OUTER JOIN
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.
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 ( customer.id = sales_order_counts.cust_id ) WHERE number_of_orders > 3