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.
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.
FROM Contacts a, Customers b
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.
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.
When there is no FROM clause, the expressions in the SELECT statement must be a constant expression.
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.
UltraLite does not support KEY JOINS nor NATURAL joins.
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(*)
GROUP BY CustomerID )
AS sales_order_counts( CustomerID, number_of_orders )
ON ( Customers.id = sales_order_counts.CustomerID )
WHERE number_of_orders > 3;