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

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 :
, | 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.

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:

• ,

• CROSS JOIN

• INNER JOIN

• LEFT OUTER JOIN

• 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.

• Joins: Retrieving Data from Several Tables
• UltraLite DELETE statement
• UltraLite SELECT statement
• UltraLite UPDATE statement

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