FROM Clause

Specifies the database tables or views involved in a SELECT statement.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

...FROM table-expression [,...]

table-expression - (back to Syntax)
   table-name
   | view-name
   | procedure-name
   | common-table-expression
   | (subquery) [[ AS ] derived-table-name [ column_name, ...) ]]
   | derived-table
   | join-expression 
   | ( table-expression , ... )
   | openstring-expression
   | apply-expression
   | contains-expression
   | dml-derived-table

table-name - (back to table-expression)
   [ userid.] table-name ]
   [ [ AS ] correlation-name ]
   [ FORCE INDEX ( index-name ) ]

view-name - (back to table-expression)
   [ userid.]view-name [ [ AS ] correlation-name ]

procedure-name - (back to table-expression)
   [  owner, ] procedure-name ([  parameter, ...])
   [  WITH(column-name datatype, )]
   [ [ AS ] correlation-name ]

parameter - (back to procedure-name)
   scalar-expression | table-parameter

table-parameter - (back to parameter)
   TABLE (select-statement) [ OVER ( table-parameter-over )]

table-parameter-over - (back to table-parameter)
   [ PARTITION BY {ANY
   | NONE| table-expression } ] 
   [ ORDER BYexpression | integer } 
   [ ASC | DESC ] [, ...] ]

derived-table - (back to table-expression)
   ( select-statement ) 
   	[ AS ] correlation-name [ ( column-name, ... ) ]

join-expression - (back to table-expression)
   table-expression join-operator table-expression
   	[ ON join-condition ]

join-operator - (back to join-expression)
   [ KEY | NATURAL ] [ join-type ] JOIN | CROSS JOIN

join-type - (back to join-operator)
   INNER
     | LEFT [ OUTER ]
     | RIGHT [ OUTER ]
     | FULL [ OUTER ]

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

apply-expression - (back to table-expression)
   table-expression { CROSS | OUTER } APPLY table-expression

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

rowset-schema - (back to openstring-expression)
   column-schema-list
	   | TABLE [owner.]table-name [ ( column-list ) ]

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

column-list - (back to rowset-schema)
   { column-name | filler( ) } [ , ... ]

scan-option - (back to openstring-expression)
   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 - (back to contains-expression)
   string

dml-derived-table - (back to table-expression)
   ( dml-statement  ) REFERENCING ( [ table-version-names  | NONE ] )

dml-statement - (back to dml-derived-table)
   insert-statement 
   update-statement
   delete-statement

table-version-names - (back to dml-derived-table)
   OLD [ AS ] correlation-name [ FINAL [ AS ] correlation-name ]
     | FINAL [ AS ] correlation-name

Parameters

(back to top)

Examples

(back to top)

Usage

(back to top)

The SELECT statement requires a table list to specify which tables are used by the statement.

Note: Although this description refers to tables, it also applies to views unless otherwise noted.

The FROM table list 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.

Tables owned by a different user can be qualified by specifying the userid. Tables owned by roles to which the current user belongs are found by default without specifying the user ID.

The correlation name is used to give a temporary name to the table for this SQL statement only. This is useful when referencing columns that must be qualified by a table name but the table name is long and cumbersome to type. The correlation name is also necessary to distinguish between table instances when referencing the same table more than once in the same query. If no correlation name is specified, then the table name is used as the correlation name for the current 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 only listed once. For example, in:

SELECT *
FROM SalesOrders
KEY JOIN SalesOrderItems,
SalesOrders
KEY JOIN Employees

The two instances of the SalesOrders table are treated as one instance that is equivalent to:

SELECT *
FROM SalesOrderItems
KEY JOIN SalesOrders
KEY JOIN Employees

By contrast, the following is treated as two instances of the Person table, with different correlation names HUSBAND and WIFE.

SELECT *
FROM Person HUSBAND, Person WIFE

Join columns require like data types for optimal performance.

  • Performance Considerations – Depending on the query, SAP Sybase IQ allows between 16 and 64 tables in the FROM clause with the optimizer turned on; however, performance might suffer if you have more than 16 to 18 tables in the FROM clause in very complex queries.
    Note: If you omit the FROM clause, or if all tables in the query are in the SYSTEM dbspace, the query is processed by SQL Anywhere instead of SAP Sybase IQ and might behave differently, especially with respect to syntactic and semantic restrictions and the effects of option settings.

    If you have a query that does not require a FROM clause, you can force the query to be processed by SAP Sybase IQ by adding the clause FROM iq_dummy, where iq_dummy is a one-row, one-column table that you create in your database.

Standards

(back to top)

  • SQL—ISO/ANSI SQL compliant.
  • SAP Sybase Database product—The JOIN clause is not supported in some versions of Adaptive Server. Instead, you must use the WHERE clause to build joins.

Permissions

(back to top)

Must be connected to the database.

Related reference
DELETE Statement
SELECT Statement