Reference Manual
   CHAPTER 9. SQL Statements     

FROM clause

Function 

To specify the database tables or views involved in a SELECT or UPDATE 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

Permissions 

Must be connected to the database.

Side effects 

None.

See also 

SELECT statement

UPDATE statement

Joins: Retrieving Data from Several Tables

Description 

The SELECT and UPDATE statements require a table list, to specify which tables will be 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 user ID. Tables owned by groups to which the current user belongs will be found by default without specifying the user ID (see Referring to tables owned by groups).

The correlation name is used to give a temporary name to the table for this SQL statement only. This is useful when referencing columns from a table with a long name. The correlation name is also necessary to distinguish between table instances if you reference the same table more than once in the same query. If no correlation name is specified, 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 listed only 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, this is equivalent to:

SELECT *
FROM sales_order_items
KEY JOIN sales_order
KEY JOIN employee

Whereas:

SELECT *
FROM Person HUSBAND, Person WIFE

would be treated as two instances of the Person table, with different correlation names HUSBAND and WIFE.

You can supply SELECT statements instead of table or view names in the FROM clause. This allows you to use groups on groups, or joins with groups, without creating a view. The tables that you create in this way are derived tables.

Standards and compatibility 

• SQL/92     Entry level feature.

• Sybase     The JOIN clause is not supported in Adaptive Server Enterprise. Instead, you must use the WHERE clause to build joins.

Examples 

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

• 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