FROM clause

FROM  table-expression, ...

table-expression :
table-name
| view-name
| procedure-name
| derived-table
| lateral-derived-table
| join-expression 
| ( table-expression, ... )
| openstring-expression
| apply-expression
| contains-expression
| dml-derived-table

table-name :
[ userid.]table-name ]
[ [ AS ] correlation-name ]
[ WITH ( hint [...] ) ]
[ 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 :
( select-statement ) 
[ AS ] correlation-name [ ( column-name, ... ) ]

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

join-expression :
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 ]

hint :
table-hint | index-hint

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

index-hint : 
NO INDEX 
| INDEX ( [ PRIMARY KEY | FOREIGN KEY ] index-name [, ...] ) [ INDEX ONLY { ON | OFF } ]

openstring-expression : 
OPENSTRING ( { FILE | VALUE } string-expression ) 
WITH ( rowset-schema ) 
 [ OPTION ( scan-option ...  ) ]
 [ AS ] correlation-name

apply-expression :
table-expression { CROSS | OUTER } APPLY table-expression

contains-expression : 
{ table-name  | view-name } CONTAINS ( column-name [,...], contains-query ) [ [ AS ] score-correlation-name ]

rowset-schema :
column-schema-list
| TABLE [owner.]table-name [ ( column-list ) ]

column-schema-list :
{ column-name user-or-base-type |  filler( ) } [ , ... ]

column-list :
{ column-name | filler( ) } [ , ... ]

scan-option :
BYTE ORDER MARK { ON | OFF }
| COMMENTS INTRODUCED BY comment-prefix
| DELIMITED BY string      
| ENCODING encoding
| ESCAPE CHARACTER character
| ESCAPES { ON | OFF }
| FORMAT { TEXT  | BCP 
| HEXADECIMAL { ON | OFF }
| QUOTE string
| QUOTES { ON | OFF }
| ROW DELIMITED BY string
| SKIP integer
| STRIP { ON | OFF | LTRIM | RTRIM | BOTH }

contains-query : string

dml-derived-table :
( dml-statement  ) REFERENCING ( [ table-version-names  | NONE ] )

dml-statement :
insert-statement
delete-statement
update-statement
merge-statement

table-version-names :
OLD [ AS ] correlation-name [ FINAL [ AS ] correlation-name ]
| FINAL [ AS ] correlation-name

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.
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, these hints have no effect.

procedure-name A stored procedure that returns a result set. This clause applies to the FROM clause of SELECT statements only. 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;

When you create a procedure without a RESULT clause and the procedure returns a variable result set, a DESCRIBE of the SELECT statement referencing the procedure may fail. To prevent the failure of the DESCRIBE, it is recommended that you include a WITH clause that describes the expected result set schema.

derived-table You can supply a SELECT statement instead of table or view name in the FROM clause. A SELECT statement used in this way is called a derived table, and it must be given an alias. For example, the following statement contains a derived table, MyDerivedTable, which ranks products in the Products table by UnitPrice.

SELECT TOP 3 * FROM ( SELECT Description, Quantity, UnitPrice, RANK() OVER ( ORDER BY UnitPrice ASC ) AS Rank FROM Products ) AS MyDerivedTable ORDER BY Rank;

For more information about derived tables, see Querying derived tables.

lateral-derived-table A derived table, stored procedure, or joined table that may include references to objects in the parent statement (outer references). You must use a lateral derived table if you want 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 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).

openstring-expression Specify an OPENSTRING clause to query within a file or a BLOB, treating the content of these sources as a set of rows. When doing so, you also specify information about the schema of the file or BLOB for the result set to be generated, since you are not querying a defined structure such as a table or view. This clause applies to the FROM clause of a SELECT statement. It is not supported for UPDATE or DELETE statements.
The ROWID function is supported over the result set of a table generated by an OPENSTRING expression.

The following subclauses and parameters of the OPENSTRING clause are used to define and query data within files and BLOBs:
- FILE and VALUE clauses Use the FILE clause to specify the file to query. Use the VALUE clause to specify the BLOB expression to query. The data type for the BLOB expression is assumed to be LONG BINARY. You can specify the READ_CLIENT_FILE function as a value to the VALUE clause.
  If neither the FILE nor VALUE keyword is specified, VALUE is assumed.
  
  When using FORMAT SHAPEFILE, only FILE is assumed. See Support for ESRI shapefiles.
- WITH clause Use this clause to specify the rowset schema (column names and data types) of the data being queried. You can specify the columns directly (for example, WITH ( Surname CHAR(30), GivenName CHAR(30) )). You can also use the TABLE subclause to reference a table to use to obtain schema information from (for example, WITH TABLE dba.Employees ( Surname, GivenName )). You must own or have SELECT permissions on the table you specify.
  When specifying columns, you can specify filler( ) for columns you want to skip in the input data (for example, WITH ( filler( ), Surname CHAR(30), GivenName CHAR(30) )). For more information about the use of filler( ), see LOAD TABLE statement.
- OPTION clause Use the OPTION clause to specify parsing options to use for the input file, such as escape characters, delimiters, encoding, and so on. Supported options comprise those options for the LOAD TABLE statement that control the parsing of an input file. See LOAD TABLE statement.
scan-option For a description of each scan option, see the load options described in LOAD TABLE statement.
apply-expression Use this clause to specify a join condition where the right table-expression is evaluated for every row in the left table-expression. For example, you can use an apply expression to evaluate a function, procedure, or derived table for each row in a table expression. See Joins resulting from apply expressions.
contains-expression Use the CONTAINS clause following a table name to filter the table and return only those rows matching the full text query specified with contains-query. Every matching row of the table is returned together with a score column that can be referred to using score-correlation-name, if it is specified. If score-correlation-name is not specified, then the score column can be referred to by the default correlation name, contains.
With the exception of the optional correlation name argument, the CONTAINS clause takes the same arguments as those of the CONTAINS search condition. See CONTAINS search condition.

There must be a text index on the columns listed in the CONTAINS clause. See How to manage text indexes.

The contains-query cannot be NULL or an empty string. If the text configuration settings cause all of the terms in the contains-query to be dropped, rows from the base table referenced by the contains-expression are not returned. For additional information on text configuration object settings, see Text configuration object settings. For more information about how the contains-query-string is interpreted, see Example text configuration objects.

correlation-name Use correlation-name to specify a substitute name for a table or view in the FROM clause. The substitute name can then be referenced from elsewhere in the statement. For example, emp and dep are correlation names for the Employees and Departments tables, respectively:

SELECT Surname, GivenName, DepartmentName FROM Employees emp, Departments dep, WHERE emp.DepartmentID=dep.DepartmentID;

dml-statement Use dml-statement to specify the DML statement (INSERT, DELETE, UPDATE, or MERGE) from which you want to select rows. During execution, the DML statement specified in dml-derived-table is executed first, and the rows affected by that DML are materialized into a temporary table whose columns are described by the REFERENCING clause. The temporary table represents the result set of dml-derived-table.

Use REFERENCING ( ) or REFERENCING ( NONE ) if the results do not need to be materialized into a temporary table because you are not referencing them in the query.

If you specify REFERENCING ( ) or REFERENCING ( NONE ), the updated rows are not materialized into a temporary table that represents the result set of dml-derived-table because they are not being referenced in the query. The temporary table in this case is an empty table. You can use this feature if you want dml-statement to be executed before the main statement is executed.

In the results, OLD columns contain the values as seen by the scan that finds the rows to include in the update operation. FINAL columns contain the values after referential integrity checks have been made, computed and default columns have been updated, and all triggers have fired (excluding AFTER triggers of type FOR STATEMENT).

Statement	Supported table versions
INSERT	FINAL
DELETE	OLD
UPDATE	FINAL and/or OLD
MERGE	FINAL and/or OLD

When specifying both OLD and FINAL names, two correlation names are used; however, these are not true correlations since they both refer to the same result set. If you specify REFERENCING (OLD AS O FINAL AS F ), there is an implicit join predicate: O.rowid = F.rowid.

The INSERT statement only supports FINAL. Consequently the values of updated rows that are modified by an INSERT ON EXISTING UPDATE statement do not appear in the result set of the derived table. Instead, use the MERGE statement to perform the insert-else-update processing.

The dml-derived-table statement can only reference one updatable table; updates over multiple tables return an error. Also, selecting from dml-statement is not allowed if the DML statement appears inside a correlated subquery or common table expression because the semantics of these constructs can be unclear.

For more information, see Executing a SELECT over a DML statement and Data modification statements.

WITH table-hint clause 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 for base tables, temporary tables, and materialized views.

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.

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 that is used only for the specified tables, 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:

Table hint	Description
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, write-locked rows. This table hint can only be used with isolation level 1. The READPAST hint is respected only when the correlation name in the FROM clause refers to a base or globally shared temporary table. In other situations (views, proxy tables, and table functions) the READPAST hint is ignored. Queries within views may utilize READPAST as long as the hint is specified for a correlation name that is a base table. The use of the READPAST table hint can lead to anomalies due to the interaction of locking and predicate evaluation within the server. In addition, you cannot use the READPAST hint against tables that are the targets of a DELETE, INSERT or UPDATE statement.
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.

WITH ( index-hint ) clause The WITH ( index-hint ) clause allows you to specify index hints that override the query optimizer plan selection algorithms, and tell the optimizer exactly how to access the table using indexes. Index hints can be used for base tables, temporary tables, and materialized views.
- NO INDEX Use this clause to force a sequential scan of the table (indexes are not used). Sequential scans may be very costly.
- INDEX ( [ PRIMARY KEY | FOREIGN KEY ] index-name [,... ] ) Use this clause to specify up to four indexes that the optimizer must use to satisfy the query.
  If any of the specified indexes cannot be used, an error is returned.
  
  You can specify PRIMARY KEY or FOREIGN KEY to remove ambiguity in the cases where the PRIMARY KEY index and FOREIGN KEY index on a table have the same name.
  
  index-name can be qualified by specifying the user ID and the table name of the index.
  
  The indexes specified in the INDEX clause must be indexes defined for that table; otherwise, an error is returned. For example, FROM Products WITH( INDEX (Products.xx)) returns an error if the index xx is not defined for the Products table. Likewise, FROM Products WITH( INDEX (sales_order_items.sales_order_items)) returns an error because the sales_order_items.sales_order_items index exists but is not defined for the Products table.
- INDEX ONLY { ON | OFF } Use this clause to control whether an index-only retrieval of data is performed. If the INDEX ( index-name... ) clause is specified with INDEX ONLY ON, the database server attempts an index-only retrieval using the specified indexes. If any of the specified indexes cannot be used in satisfying an index-only retrieval, an error is returned (for example, if there are no indexes, or if the existing indexes cannot satisfy the query).
  Specify INDEX ONLY OFF to prevent an index-only retrieval.
FORCE INDEX ( index-name ) The FORCE INDEX ( index-name ) syntax is provided for compatibility, and does not support specifying more than one index. Use this clause to specify the index that the optimizer must use to find rows in the table that satisfy the query.

Subqueries are not allowed as arguments to a store procedures in the FROM clause. For example, the following statement returns an error:

SELECT *, ( SELECT 12 x ) D
FROM sa_rowgenerator( 1,( SELECT 12 x ) ):

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.

The FILE clause of openstring-expression requires either DBA or READFILE authority.

The TABLE clause of openstring-expression requires the user, to own or have SELECT permissions on, the specified table.

None.

SQL/2008 The FROM clause is a fundamental part of the SQL/2008 standard. The complexity of the FROM clause means that you should check individual components of a FROM clause against the appropriate portions of the standard. The following is a non-exhaustive list of optional SQL/2008 language features supported in SQL Anywhere:
- CROSS JOIN, FULL OUTER JOIN, and NATURAL JOIN constitute optional SQL/2008 feature F401.
- INTERSECT and INTERSECT ALL constitute optional SQL/2008 feature F302.
- EXCEPT ALL is optional language feature F304.
- derived tables are SQL/2008 language feature F591.
- procedures in the FROM clause (table functions) are feature T326. Note that the SQL/2008 standard requires the keyword TABLE to identify the output of a procedure as a table expression, whereas in SQL Anywhere the TABLE keyword is unnecessary.
- common table expressions are optional SQL/2008 language feature T121. Using a common table expression in a derived table nested within another common table expression is language feature T122.
- recursive table expressions are feature T131. Using a recursive table expression in a derived table nested within a common table expression is optional SQL/2008 language feature T132.
The following components of the FROM clause are vendor extensions:
- KEY JOIN.
- CROSS APPLY and OUTER APPLY.
- OPENSTRING.
- a table-expression using CONTAINS (full text search).
- specifying a dml-statement as a derived table.
- all table hints, including the use of WITH, FORCE INDEX, READPAST and isolation level hints.
- LATERAL ( table-expression ), which is a vendor extension. LATERAL ( select-statement ) is in the SQL/2008 standard as optional SQL language feature T491.

The following are valid FROM clauses:

...
FROM Employees
...

...
FROM Employees NATURAL JOIN Departments
...

...
FROM Customers
KEY JOIN SalesOrders
KEY JOIN SalesOrderItems
KEY JOIN Products
...

...
FROM Employees CONTAINS ( Street, ' Way ' )
...

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;

The following example illustrates how to perform a query using the OPENSTRING clause to query a file. The CREATE TABLE statement creates a table called testtable with two columns, column1 and columns2. The UNLOAD statement creates a file called testfile.dat by unloading rows from the RowGenerator table. The SELECT statement uses the OPENSTRING clause in a FROM clause to query testfile.dat using the schema information from both the testtable and RowGenerator tables. The query returns one row with the value 49.

CREATE TABLE testtable( column1 CHAR(10), column2 INT );
UNLOAD SELECT * FROM RowGenerator TO 'testfile.dat'; 
SELECT A.column2 
  FROM OPENSTRING( FILE 'testfile.dat' ) 
  WITH ( TABLE testtable( column2 ) ) A, RowGenerator B
  WHERE A.column2 = B.row_num
  AND A.column2 < 50
  AND B.row_num > 48;

The following example illustrates how to perform a query using the OPENSTRING clause to query a string value. The SELECT statement uses the OPENSTRING clause in a FROM clause to query a string value using the schema information provided in the WITH clause. The query returns two columns with three rows.

SELECT *
  FROM OPENSTRING( VALUE '1,"First"$2,"Second"$3,"Third"')
  WITH (c1 INT, c2 VARCHAR(30))
  OPTION ( DELIMITED BY ',' ROW DELIMITED BY '$')
  AS VALS

The following example illustrates how to perform a query to select the rows modified by a data modification statement. In this example, a warning is issued when the stock of blue items drops by more than half.

SELECT old_products.name, old_products.quantity, final_products.quantity 
FROM 
( UPDATE Products SET quantity = quantity - 10 WHERE color = 'Blue' ) 
REFERENCING ( OLD AS old_products FINAL AS final_products )
WHERE final_products.quantity < 0.5 * old_products.quantity;

FROM clause

Syntax

Parameters

Caution

Using READPAST with MobiLink synchronization

Remarks

Views and derived tables

Permissions

Side effects

See also

Standards and compatibility

Example