FROM clause [UltraLite]

Use this clause to specify the tables or views involved in a SELECT statement.

Note

This statement is not supported by UltraLite Java edition databases.

Syntax

FROM  table-expression, ...

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

join-operator :
,
| INNER JOIN
| CROSS JOIN 
| LEFT OUTER JOIN 
| JOIN

Parameters

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 the table from elsewhere in the statement. For example, in the following statement, a is defined as the correlation name for the Contacts table, and b is the correlation name for the Customers table.

SELECT * FROM Contacts a, Customers b WHERE a.CustomerID=b.ID;

derived-table-name A derived table is a nested SELECT statement in the FROM clause.
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 tables from within the SELECT statement.
join-operator Specify the type of join. If you specify a comma (,), or CROSS JOIN, you cannot specify an ON subclause. If you specify JOIN, you must specify an ON subclause. For INNER JOIN and LEFT OUTER JOIN, the ON clause is optional.

Remarks

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

Note

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 specified tables are in the result set, and the number of combinations is usually reduced by JOIN conditions and/or WHERE conditions.

If you do not specify the type of join, and instead list the tables as a comma-separated list, a CROSS JOIN is used, by default.

For INNER joins, restricting results of the join using an ON clause or WHERE clause returns equivalent results. For OUTER joins, they are not equivalent.

Note

UltraLite does not support KEY JOINS nor NATURAL joins.

Example

The following are valid FROM clauses:

...
FROM Employees
...

...
FROM Customers
CROSS JOIN SalesOrders
CROSS JOIN SalesOrderItems
CROSS JOIN Products
...

The following query uses a derived table to return the names of the customers in the Customers table who have more than three orders in the SalesOrders table:

SELECT Surname, GivenName, number_of_orders
FROM Customers JOIN
     ( SELECT CustomerID, COUNT(*)
       FROM SalesOrders
       GROUP BY CustomerID )
     AS sales_order_counts( CustomerID, number_of_orders )
ON ( Customers.id = sales_order_counts.CustomerID )
WHERE number_of_orders > 3;

Discuss this page in DocCommentXchange.