ASA SQL Reference
  SQL Statements

FROM clause

Description 

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
| view
| procedure
| derived-table
| lateral-derived-table
| joined table
| ( table-expression, ... )

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

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

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

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

joined table:
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:
  NOLOCK
| READUNCOMMITTED
| READCOMMITTED
| REPEATABLEREAD
| HOLDLOCK
| SERIALIZABLE
| FASTFIRSTROW

Parameters 

table or view    A base table, temporary table, or view. Tables owned by a different user can be qualified by specifying the user ID. Tables owned by groups to which the current user belongs will be found by default without specifying the user ID (see Referring to tables owned by groups).

procedure    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.customer, sp.quantity, product.name
FROM sp_customer_products( 149 ) WITH( customer int, quantity int ) sp
   JOIN product
ON sp.customer = product.id

derived-table    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    A derived table, stored procedure, or joined table that may include outer references. You must use a lateral derived table if you wish to use an outer reference in the FROM clause.

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

correlation-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, in:

SELECT *
FROM sales_order
KEY JOIN sales_order_items,
sales_order
KEY JOIN employee

the two instances of the sales_order table are treated as one instance, and so is equivalent to:

SELECT *
FROM sales_order
KEY JOIN sales_order_items
KEY JOIN employee

Whereas:

SELECT *
FROM Person HUSBAND, Person WIFE

would be treated as two instances of the Person table, with different correlation names HUSBAND and WIFE.

WITH table-hint    allows you to specify the behavior of Adaptive Server Anywhere to be used only for this table, and only for this statement. You can use WITH table-hint to change Adaptive Server Anywhere's 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    WITH table-hint 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 hints    The following table hints can be used to specify isolation level settings for tables. They specify a locking method to be used only for this table, and only for this statement.

  The table hints set the following isolation levels:

  Table hint	Isolation level
  NOLOCK	0
  READUNCOMMITTED	0
  READCOMMITTED	1
  REPEATABLEREAD	2
  HOLDLOCK	3
  SERIALIZABLE	3

• Optimization hints    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, Adaptive Server Anywhere chooses an access plan that is intended to reduce the time to fetch the first row of the query's result.

  For more information, see OPTIMIZATION_GOAL option [database].

Usage 

The SELECT, UPDATE, and DELETE statements require a table list, to specify which tables are used by the statement.

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 

DELETE statement

SELECT statement

UPDATE statement

Joins: Retrieving Data from Several Tables

Standards and compatibility 

• SQL/92    Entry-level feature. The complexity of the FROM clause means that you should check individual clauses against the standard.

• SQL/99    Core feature, except for KEY JOIN, which is a vendor extension; and FULL OUTER JOIN and NATURAL JOIN, which are SQL/foundation features outside of core SQL. The complexity of the FROM clause means that you should check individual clauses against the standard.

• Sybase    The ON phrase is not supported in Adaptive Server Enterprise prior to version 12. In earlier versions, you must use the WHERE clause to build joins.

Example 

The following are valid FROM clauses:

...
FROM employee
...

...
FROM employee NATURAL JOIN department
...

...
FROM customer
KEY JOIN sales_order
KEY JOIN sales_order_items
KEY JOIN product
...

The following query illustrates how to use derived tables in a query:

SELECT lname, fname, number_of_orders
FROM customer JOIN
     ( SELECT cust_id, count(*)
       FROM sales_order
        GROUP BY cust_id )
     AS sales_order_counts ( cust_id,
                             number_of_orders )
ON ( customer.id = sales_order_counts.cust_id )
WHERE number_of_orders > 3

The following query illustrates how to select rows from stored procedure result sets:

SELECT t.id, t.quantity_ordered AS q, p.name
FROM sp_customer_products( 149 ) t JOIN product p
ON t.id = p.id