Specifies the database tables or views involved in a SELECT statement.
...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 BY { expression | 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
This query illustrates a valid FROM clause where the two references to the same table T are treated as two different instances of the same table T.
SELECT * FROM T, my_proc(TABLE(SELECT T.Z, T.X FROM T) OVER(PARTITION BY T.Z));
Table Parameterized Function (TPF) Example—This query illustrates a valid FROM clause.
SELECT * FROM R, SELECT * FROM my_udf(1); SELECT * FROM my_tpf(1, TABLE(SELECT c1, c2 FROM t)) (my_proc(R.X, TABLE T OVER PARTITION BY T.X)) AS XX;
PARTITION BY clause also specifies how the input data must be partitioned such that each invocation of the function will process exactly one partition of data. The function must be invoked the number of times equal to the number of partitions. For TPF, the parallelism characteristics are established through dynamic negotiation between the server and the UDF at the runtime. If the TPF can be executed in parallel, for N input partitions, the function can be instantiated M times, with M <=N. Each instantiation of the function can be invoked more than once, each invocation consuming exactly one partition.
You can specify only one TABLE input parameter for PARTITION BY expression-list or PARTITION BY ANY clause. For all other TABLE input parameters you must specify, explicit or implicit PARTITION BY NONE clause.
SELECT TOP 3 *
FROM ( SELECT Description,
Quantity,
UnitPrice,
RANK() OVER ( ORDER BY UnitPrice ASC )
AS Rank
FROM Products ) AS MyDerivedTable
ORDER BY Rank;
| Keyword | Description |
|---|---|
| CROSS JOIN | Returns the Cartesian product (cross product) of the two source tables |
| NATURAL JOIN | Compares for equality all corresponding columns with the same names in two tables (a special case equijoin; columns are of same length and data type) |
| KEY JOIN | Restricts foreign-key values in the first table to be equal to the primary-key values in the second table |
| INNER JOIN | Discards all rows from the result table that do not have corresponding rows in both tables |
| LEFT OUTER JOIN | Preserves unmatched rows from the left table, but discards unmatched rows from the right table |
| RIGHT OUTER JOIN | Preserves unmatched rows from the right table, but discards unmatched rows from the left table |
| FULL OUTER JOIN | Retains unmatched rows from both the left and the right tables |
Do not mix comma-style joins and keyword-style joins in the FROM clause. The same query can be written two ways, each using one of the join styles. The ANSI syntax keyword style join is preferable.
This query uses a comma-style join:
SELECT * FROM Products pr, SalesOrders so, SalesOrderItems si WHERE pr.ProductID = so.ProductID AND pr.ProductID = si.ProductID;
The same query can use the preferable keyword-style join:
SELECT * FROM Products pr INNER JOIN SalesOrders so ON (pr.ProductID = so.ProductID) INNER JOIN SalesOrderItems si ON (pr.ProductID = si.ProductID);
The ON clause filters the data of inner, left, right, and full joins. Cross joins do not have an ON clause. In an inner join, the ON clause is equivalent to a WHERE clause. In outer joins, however, the ON and WHERE clauses are different. The ON clause in an outer join filters the rows of a cross product and then includes in the result the unmatched rows extended with nulls. The WHERE clause then eliminates rows from both the matched and unmatched rows produced by the outer join. You must take care to ensure that unmatched rows you want are not eliminated by the predicates in the WHERE clause.
You cannot use subqueries inside an outer join ON clause.
... FROM Employees ... ... FROM Employees NATURAL JOIN Departments ... ... FROM Customers KEY JOIN SalesOrders KEY JOIN SalesOrderItems KEY JOIN Products ...
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.cust_id )
WHERE number_of_orders > 3
The SELECT statement requires a table list to specify which tables are used by the statement.
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.
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.