Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

FORWARD TO statement Next Page

FROM clause

Use this clause to specify the database tables or views involved in a SELECT, UPDATE, or DELETE statement.

Syntax

FROM table-expression, ...

table-expression:
table-name
| view-name
| procedure-name
| derived-table-name
| lateral-derived-table-name
| joined-table-name
| ( table-expression, ... )

table-name :
[ userid.]table-name
[ [ AS ] correlation-name ]
[ WITH ( table-hint | NO INDEX | INDEX ( index-name ) ) | FORCE INDEX ( index-name ) ]

view-name :
[ userid.] view-name [ [ AS ] correlation-name ]
[ WITH ( table-hint ) ]

procedure-name :
[ owner.]procedure-name ( [ parameter, ... ] )
[ WITH( column-name data-type, ... ) ]
[ [ AS ] correlation-name ]

derived-table-name :
( select-statement )
[ AS ] correlation-name [ ( column-name, ... ) ]

lateral-derived-table-name :
LATERAL ( select-statement | table-expression )
[ AS ] correlation-name [ ( column-name, ... ) ]

joined-table-name :
table-expression join-operator table-expression
[ ON join-condition ]

join-operator :
[ KEY | NATURAL ] [ join-type ] JOIN
| CROSS JOIN

join-type:
INNER
| LEFT [ OUTER ]
| RIGHT [ OUTER ]
| FULL [ OUTER ]

table-hint:
HOLDLOCK
| NOLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| SERIALIZABLE
| UPDLOCK
| XLOCK
| FASTFIRSTROW

Parameters

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;
Advanced feature

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.

Caution    

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.

Remarks

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.

Permissions

None.

Side effects

None.

See also
Standards and compatibility
Example

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';