FROM clause

Description

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

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

The following are valid FROM clauses:

...
FROM employee
...
...
FROM employee NATURAL JOIN department
...
...
FROM customer
KEY JOIN sales_order
KEY JOIN sales_order_items
KEY JOIN product
...

Example 2

The following query illustrates how to use derived tables in a query:

SELECT lname, fname, number_of_orders
FROM customer JOIN
     ( SELECT cust_id, count(*)
       FROM sales_order
        GROUP BY cust_id )
     AS sales_order_counts ( cust_id, 
                             number_of_orders )
ON ( customer.id = sales_order_counts.cust_id )
WHERE number_of_orders > 3

Usage

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

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.

The join-type keywords are described in Table 6-8.

**Table 6-8: FROM clause join-type keywords**
join-type 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 equi-join; 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.

The following query uses a comma style join:

SELECT *
  FROM product pr, sales_order so, sales_order_items si
  WHERE pr.prod_id = so.prod_id
    AND pr.prod_id = si.prod_id;

The same query can use the preferable keyword style join:

SELECT *
  FROM product pr INNER JOIN sales_order so
    ON (pr.prod_id = so.prod_id)
  INNER JOIN sales_order_items si
    ON (pr.prod_id = si.prod_id);

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 will then eliminate 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.

Subqueries cannot be used inside an outer join ON clause.

For information on writing Transact-SQL compatible joins, see Appendix A, “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 sales_order
KEY JOIN sales_order_items,
sales_order
KEY JOIN employee

The two instances of the sales_order table are treated as one instance, and is equivalent to:

SELECT *
FROM sales_order_items
KEY JOIN sales_order
KEY JOIN employee

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. This allows you to 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 a maximum of between 16 and 64 tables in the FROM clause with the optimizer turned on; however, performance may suffer if you have more than 16 to 18 tables in the FROM clause in very complex queries.

If you omit the FROM clause, or if all tables in the query are in the SYSTEM dbspace, the query is processed by Adaptive Server Anywhere instead of Sybase IQ and may behave differently, especially with respect to syntactic and semantic restrictions and the effects of option settings. See the Adaptive Server Anywhere documentation for rules that may apply to processing.

If you have a query that does not require a FROM clause, you can force the query to be processed by 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.