The FROM clause specifies one or more source tables for a SELECT statement.

Syntax

FROM source [, ...]

The following table describes the available parameters for source.

Parameter Description
table_name[@dblink ] The name of an existing table or view. The name can be schema-qualified. dblink is the name of a database link that identifies a remote database. For more information about database links, see the CREATE DATABASE LINK command topic.
alias A substitute name for the FROM item that contains the alias. An alias is used for brevity or to eliminate ambiguity for self-joins (where the same table is scanned multiple times). If you specify an alias for a table or function, the alias hides the actual name of the table or function. For example, if FROM foo AS f is specified, the remainder of the SELECT statement must refer to the FROM item as f rather than foo.
select You can nest a SELECT statement in the FROM clause. This creates a derived table for the duration of the SELECT statement. You must enclose the nested SELECT statement in parentheses and specify an alias for it.
join_type The following join types are available:
  • [ INNNER ] JOIN
  • LEFT [ OUTER ] JOIN
  • RIGHT [ OUTER ] JOIN
  • FULL [ OUTER ] JOIN
  • CROSS JOIN

For the INNER and OUTER join types, a join condition must be specified. In other words, the join condition is one of NATURAL, ON join_condition, or USING (join_column [, ...] ). The following paragraphs describe the join types. For CROSS JOIN, none of these clauses appear.

A JOIN clause combines two FROM items. You can use parentheses to determine the order of nesting. In the absence of parentheses, JOIN clauses nest from left to right. The JOIN clause binds tighter than the commas separating FROM items.

CROSS JOIN and INNER JOIN produce a simple Cartesian product. The result is the same as that of listing the two tables at the top level of FROM, but is restricted by the join condition. CROSS JOIN is equivalent to INNER JOIN ON (TRUE). No rows are removed by qualification. The listed join types are for notational convenience. You can use the FROM and WHERE clauses to perform all operations that you can perform by using join types.

LEFT OUTER JOIN returns all rows in the qualified Cartesian product. The qualified Cartesian product contain all combined rows that pass the join condition. LEFT OUTER JOIN also returns the left-side rows that do not have a matching right-side row. Each left-side row that does not have a matching right-side row is extended to the full width of the joined table by inserting null values for the right-side columns. Note that only the condition of the JOIN clause is considered when whether rows have matches is decided. Then, outer conditions are applied.

RIGHT OUTER JOIN returns all the matching rows and the right-side rows that do not have a matching left-side row. Each right-side row is extended with null values on the left. This is a notational convenience. You can convert it to a LEFT OUTER JOIN by switching the left and right inputs.

FULL OUTER JOIN returns all the matching rows, one row for each unmatched left-side row, and one row for each unmatched right-side row. The left-side row is extended with null values on the right. The right-side row is extended with null values on the left.

ON join_condition join_condition is an expression resulting in a value of the BOOLEAN type (similar to a WHERE clause) that specifies which rows in a join are considered to match.
USING (join_column [, ...] ) A clause of the USING (a, b, ... ) form is short for ON left_table.a = right_table.a AND left_table.b = right_table.b ... In addition, USING indicates that only one of each pair of equivalent columns is included in the join output.
NATURAL NATURAL is short for a USING list that includes all columns in the two tables that have the same names.

If multiple sources are specified, the result is the Cartesian product (cross join) of all the sources. In most cases, qualification conditions are added to restrict the returned rows to a small subset of the Cartesian product.

Examples

The following example selects all of the entries from the dept table:

SELECT * FROM dept;
deptno |  dname      |  loc
-------+-------------+-----------
    10 |  ACCOUNTING |  NEW YORK
    20 |  RESEARCH   |  DALLAS
    30 |  SALES      |  CHICAGO
    40 | OPERATIONS  |  BOSTON
 (4 rows)