Use this clause to specify the tables or views involved in a SELECT statement.
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
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. See Subqueries in expressions.
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.
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 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.
Send feedback about this page using email.
|Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1|