Use this clause to specify the database tables or views involved in a SELECT, UPDATE, or DELETE statement.
FROM table-expression, ...
| ( table-expression, ... )
[ [ AS ] correlation-name ]
[ WITH ( table-hint | NO INDEX | INDEX ( index-name ) ) | FORCE INDEX ( index-name ) ]
[ userid.] view-name [ [ AS ] correlation-name ]
[ WITH ( table-hint ) ]
[ owner.]procedure-name ( [ parameter, ... ] )
[ WITH( column-name data-type, ... ) ]
[ [ AS ] correlation-name ]
( select-statement )
[ AS ] correlation-name [ ( column-name, ... ) ]
LATERAL ( select-statement | table-expression )
[ AS ] correlation-name [ ( column-name, ... ) ]
table-expression join-operator table-expression
[ ON join-condition ]
[ KEY | NATURAL ] [ join-type ] JOIN
| CROSS JOIN
| LEFT [ OUTER ]
| RIGHT [ OUTER ]
| FULL [ OUTER ]
table-name A base table or temporary table. Tables owned by a different user can be qualified by specifying the user ID. Tables owned by groups to which the current user belongs are found by default without specifying the user ID (see Referring to tables owned by groups).
The WITH ( INDEX ( index-name ) ) clause, and the equivalent FORCE INDEX ( index-name ) clause, specify index hints for the table. It overrides the query optimizer plan selection algorithms, requiring the optimized query to access the table using the specified index, regardless of other access plans that may be available. You can specify only one index hint per correlation name. You can specify index hints only on base tables or temporary tables.
The WITH ( NO INDEX ) clause forces a sequential scan of the table. For example, the following SELECT statement forces the select from the Customers table to be performed sequentially:
SELECT * FROM Customers WITH ( NO INDEX ) WHERE Customers.ID >= 500 ORDER BY Customers.ID DESC;
Index hints override the query optimizer, and so should be used only by experienced users. Using index hints may lead to suboptimal access plans and poor performance.
view-name Specifies a view to include in the query. As with tables, views owned by a different user can be qualified by specifying the user ID. Views owned by groups to which the current user belongs are found by default without specifying the user ID.
Although the syntax permits table hints on views, such hints have no effect.
procedure-name A stored procedure that returns a result set. Procedures can be used only in the FROM clause of SELECT statements, not UPDATE or DELETE statements. The parentheses following the procedure name are required even if the procedure does not take parameters. If the stored procedure returns multiple result sets, only the first is used.
The WITH clause provides a way of specifying column name aliases for the procedure result set. If a WITH clause is specified, the number of columns must match the number of columns in the procedure result set, and the data types must be compatible with those in the procedure result set. If no WITH clause is specified, the column names and types are those defined by the procedure definition. The following query illustrates the use of the WITH clause:
SELECT sp.ident, sp.quantity, Products.name FROM ShowCustomerProducts( 149 ) WITH ( ident int, description char(20), quantity int ) sp JOIN Products ON sp.ident = Products.ID;
See also: ProcCall algorithm, and Procedure statistics.
derived-table-name You can supply SELECT statements instead of table or view names in the FROM clause. This allows you to use groups on groups, or joins with groups, without creating a view. The tables that you create in this way are derived tables.
lateral-derived-table-name A derived table, stored procedure, or joined table that may include outer references. You must use a lateral derived table if you want to use an outer reference in the FROM clause. For information about outer references, see Outer references.
You can use outer references only to tables that precede the lateral derived table in the FROM clause. For example, you cannot use an outer reference to an item in the select-list.
The table and the outer reference must be separated by a comma. For example, the following queries (with outer references highlighted) are valid:
SELECT * FROM A, LATERAL( B LEFT OUTER JOIN C ON ( A.x = B.x ) ) LDT; SELECT * FROM A, LATERAL( SELECT * FROM B WHERE A.x = B.x ) LDT; SELECT * FROM A, LATERAL( procedure-name( A.x ) ) LDT;
Specifying LATERAL ( table-expression) is equivalent to specifying LATERAL ( SELECT * FROM table-expression ).
correlation-name-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 SalesOrders KEY JOIN SalesOrderItems, SalesOrders KEY JOIN Employees; SELECT * FROM SalesOrders KEY JOIN SalesOrderItems KEY JOIN Employees;
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;
WITH table-hint The WITH table-hint clause allows you to specify the behavior to be used only for this table, and only for this statement. Use this clause to change the behavior without changing the isolation level or setting a database or connection option. Table hints can be used only on base tables and temporary tables.
The WITH table-hint clause is an advanced feature that should be used only if needed, and only by experienced database administrators. In addition, the setting may not be respected in all situations.
Isolation level related table hints The isolation level table hints are used to specify isolation level behavior when querying tables. They specify a locking method to be used only for the specified table(s), and only for the current query. You cannot specify snapshot isolation levels as table hints.
Following is the list of supported isolation level related table hints:
|HOLDLOCK||Sets the behavior to be equivalent to isolation level 3. This table hint is synonymous with SERIALIZABLE.|
|NOLOCK||Sets the behavior to be equivalent to isolation level 0. This table hint is synonymous with READUNCOMMITTED.|
|READCOMMITTED||Sets the behavior to be equivalent to isolation level 1.|
|READPAST||Instructs the database server to ignore, instead of block on, rows that have write locks. Used with isolation level 1 (only). Results may vary depending on the optimization strategy used by the optimizer, particularly if the hint is specified on only a subset of the tables in the query.|
|READUNCOMMITTED||Sets the behavior to be equivalent to isolation level 0. This table hint is synonymous with NOLOCK.|
|REPEATABLEREAD||Sets the behavior to be equivalent to isolation level 2.|
|SERIALIZABLE||Sets the behavior to be equivalent to isolation level 3. This table hint is synonymous with HOLDLOCK.|
|UPDLOCK||Indicates that rows processed by the statement from the hinted table are locked using intent locks. The affected rows remain locked until the end of the transaction. UPDLOCK works at all isolation levels and uses intent locks. See Intent locks.|
|XLOCK||Indicates that rows processed by the statement from the hinted table are to be locked exclusively. The affected rows remain locked until the end of the transaction. XLOCK works at all isolation levels and uses write locks. See Write locks.|
For information about isolation levels, see Isolation levels and consistency.
Using READPAST with MobiLink synchronization
If you are writing queries for databases that participate in MobiLink synchronization, it is recommended that you do not use the READPAST table hint in your synchronization scripts.
For more information, see:
If you are considering READPAST because your application performs many updates that affect download performance, an alternative solution is to use snapshot isolation. See MobiLink isolation levels.
Optimization table hint (FASTFIRSTROW) The FASTFIRSTROW table hint allows you to set the optimization goal for the query without setting the optimization_goal option to First-row. When you use FASTFIRSTROW, SQL Anywhere chooses an access plan that is intended to reduce the time to fetch the first row of the query's result. See optimization_goal option [database].
The SELECT, UPDATE, and DELETE statements require a table list to specify which tables are used by the statement.
Views and derived tables
Although the FROM clause description refers to tables, it also applies to views and 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.
You cannot use an ON phrase with CROSS JOIN.
SQL/2003 Core feature, except for: KEY JOIN, which is a vendor extension; FULL OUTER JOIN and NATURAL JOIN, which are SQL/foundation features outside of core SQL; the READPAST table hint, which is a vendor extension; LATERAL ( table-expression ), which is a vendor extension (but LATERAL ( query-expression ) is in the ANSI SQL standard as feature T491)); derived tables are feature F591; procedures in the FROM clause (table functions) are feature T326; common table expressions are feature T121; recursive table expressions are feature T131. The complexity of the FROM clause means that you should check individual clauses against the standard.
The following are valid FROM clauses:
... FROM Employees ... ... FROM Employees NATURAL JOIN Departments ... ... FROM Customers KEY JOIN SalesOrders KEY JOIN SalesOrderItems KEY JOIN Products ...
The following query illustrates how to use derived tables in a query:
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;
The following query illustrates how to select rows from stored procedure result sets:
SELECT t.ID, t.QuantityOrdered AS q, p.name FROM ShowCustomerProducts( 149 ) t JOIN Products p ON t.ID = p.ID; SELECT * FROM Customers WITH( readpast ) WHERE State = 'NY';