Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## FROM clause

> Specifies one or more table references. Each table reference resolves to one table (either a table stored in the database or a virtual table resulting from processing the table reference) whose rows the query expression uses to create the result table.

> #### Syntax

>     FROM table_ref [, table_ref ] ... [ { NO REORDER } ]

> ``table_ref``

> There are three forms of table references:

> - A direct reference to a table, view, or synonym.
> - A derived table specified by a query expression in the `FROM` clause.
> - A joined table that combines rows and columns from multiple tables.

> If there are multiple table references, SQL joins the tables to form an intermediate result table that is used as the basis for evaluating all other clauses in the query expression. That intermediate result table is the Cartesian product of rows in the tables in the `FROM` clause, formed by concatenating every row of every table with all other rows in all tables, as shown in the following syntax:

> Syntax
>     table_name [ AS ] [ alias [ ( column_alias [ ... ] ) ] ]
>       | ( query_expression ) [ AS ] alias [ (column_alias [ ... ] ) ]
>       | [ ( ] joined_table [ ) ]

> `FROM table_name [ AS ] [ alias [ ( column_alias [ ... ] ) ] ]`

> Explicitly names a table. The name can be a table name, a view name, or a synonym.

> ``alias``

> A name used to qualify column names in other parts of the query expression. Aliases are also called correlation names.

> If you specify an alias, you must use it, and not the table name, to qualify column names that refer to the table. Query expressions that join a table with itself must use aliases to distinguish between references to column names.

> Similar to table aliases, the column_alias provides an alternative name to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in table_name. Also, if you specify column aliases in the `FROM` clause, you must use them, and not the column names, in references to the columns.

> `FROM ( query_expression ) [ AS ] [ alias [ ( column_alias [ ... ] ) ] ]`

> Specifies a derived table through a query expression. With derived tables, you must specify an alias to identify the derived table.

> Derived tables can also specify column aliases. Column aliases provide alternative names to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in the result table of the query expression. Also, if you specify column aliases in the `FROM` clause, you must use them, and not the column names, in references to the columns.

> `FROM [ ( ] joined_table [ ) ]`

> Combines data from two table references by specifying a join condition, as shown in the following syntax:

> Syntax
>     { table_ref CROSS JOIN table_ref
>       | table_ref [ INNER | LEFT [ OUTER ] ] JOIN
>         table_ref ON search_condition
>     }

> The syntax currently allowed in the `FROM` clause supports only a subset of possible join conditions:

> - `CROSS` `JOIN` specifies a Cartesian product of rows in the two tables. Every row in one table is joined to every row in the other table.
> - `INNER` `JOIN` specifies an inner join using the supplied search condition.
> - `LEFT OUTER` `JOIN` specifies a left outer join using the supplied search condition.
> - `LEFT JOIN` specifies the same conditions as an inner join.

> You can also specify these and other join conditions in the `WHERE` clause of a query expression.

> `{ NO REORDER }`

> Disables join order optimization for the `FROM` clause. Use `NO` `REORDER` when you choose to override the join order chosen by the optimizer. The braces are part of the syntax for this optional clause.

> Example

> For customers with orders, retrieve their names and order info, as shown in the following example:

>     SELECT Customer.CustNum,Customer.Name, Order.OrderNum,Order.OrderDate
>          FROM Customer, Order
>          WHERE Customer.CustNum = Order.CustNum;

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095