FROM Clause

Syntax

... FROM table-expression [, …]

Parameters

• table-expression: – { table-spec | table-expression join-type table-spec [ ON condition ] | ( table-expression [, …] ) }
• table-spec: – { [ userid.] table-name [ [ AS ] correlation-name ] | select-statement [ AS correlation-name ( column-name [, …] ) ] }
• join-type: – { CROSS JOIN | [ NATURAL | KEY ] JOIN | [ NATURAL | KEY ] INNER JOIN | [ NATURAL | KEY ] LEFT OUTER JOIN | [ NATURAL | KEY ] RIGHT OUTER JOIN | [ NATURAL | KEY ] FULL OUTER JOIN }

Examples

• Example 1 – Valid FROM clauses:

  ...
  FROM Employees
  ...
  ...
  FROM Employees NATURAL JOIN Departments
  ...
  ...
  FROM Customers
  KEY JOIN SalesOrders
  KEY JOIN SalesOrderItems
  KEY JOIN Products
  ...

• Example 2 – 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.cust_id )
  WHERE number_of_orders > 3

Usage

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.

A SELECT statement can also return a result set from a procedure. Note that CIS functional compensation performance considerations apply. For syntax and an example, see SQL Anywhere 11.0.1 > SQL Anywhere Server – SQL Reference > Using SQL > SQL statements > SQL statements (E-O) > FROM clause.

For information on the contains-expression used in the FROM clause for full text searches, see Unstructured Data Analytics in Sybase IQ.

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.

For information on writing Transact-SQL compatible joins, see Reference: Building Blocks, Tables, and Procedures > Compatibility with Other Sybase Databases.

Tables owned by a different user can be qualified by specifying the userid. Tables owned by groups 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

You can supply a SELECT statement instead of one or more tables or views in the FROM clause, letting you use groups on groups, or joins with groups, without creating a view. This use of SELECT statements is called derived tables.

Join columns require like data types for optimal performance.

Depending on the query, 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.

See also Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Search Conditions and System Administration Guide: Volume 2 > Using OLAP.

Standards

Permissions

Must be connected to the database.