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
| lateral-derived-table
| join-expression 
| ( table-expression, ... )
| openstring-expression
| apply-expression
| contains-expression

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

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 ( index-name [, ...] ) [ INDEX ONLY { ON | OFF } ]
| FORCE INDEX ( index-name )

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 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 :
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
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.

  • 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. 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;

  • 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 are the subclauses and parameters of the OPENSTRING clause, and how to use them 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.

    • 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 on 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.

  • 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 Text indexes.

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

  • 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 [database].

  • WITH ( index-hint ) clause   The WITH ( index-hint ) clause allows you to 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). Note that sequential scans may be very costly.

  • INDEX ( 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.

  • 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 )   Use this clause to specify the index that the optimizer must use to find rows in the table that satisfy the query. The FORCE INDEX ( index-name ) syntax is provided for compatibility, and does not support specifying more than one index.

    Caution

    Index hints override the query optimizer's decision making logic, and so should be used only by experienced users. Using index hints may lead to suboptimal access plans and poor performance.

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.

For Syntax 2, if neither the FILE clause nor VALUE clause is specified, VALUE is assumed. That is, string-expression is assumed to be the value to be queried.

Permissions

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.

Side effects

None.

See also
Standards and compatibility

  • SQL/2003   Core feature, except for the list below. The complexity of the FROM clause means that you should check individual clauses against the standard.

    • KEY JOIN, which is a vendor extension
    • FULL OUTER JOIN and NATURAL JOIN, which are SQL/foundation features outside of core SQL
    • READPAST table hint, which is a vendor extension
    • LATERAL ( table-expression ), which is a vendor extension. Note that LATERAL ( select-statement ) 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

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;

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;
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0