A table expression computes a table.
The table expression contains a FROM
clause that is optionally followed by WHERE, GROUP BY, and
HAVING clauses. Trivial table
expressions simply refer to a table on disk, a so-called base
table, but more complex expressions can be used to modify or
combine base tables in various ways.

The optional WHERE, GROUP BY, and HAVING
clauses in the table expression specify a pipeline of successive
transformations performed on the table derived in the FROM clause. All these transformations produce a
virtual table that provides the rows that are passed to the
select list to compute the output rows of the query.

The FROM
Clause derives a table from one or more other tables given
in a comma-separated table reference list.

FROM table_reference [, table_reference [, ...]]

A table reference can be a table name (possibly
schema-qualified), or a derived table such as a subquery, a
table join, or complex combinations of these. If more than one
table reference is listed in the FROM
clause they are cross-joined (see below) to form the
intermediate virtual table that can then be subject to
transformations by the WHERE,
GROUP BY, and HAVING clauses and is finally the result of the
overall table expression.

When a table reference names a table that is the parent of a
table inheritance hierarchy, the table reference produces rows
of not only that table but all of its descendant tables, unless
the key word ONLY precedes the table
name. However, the reference produces only the columns that
appear in the named table — any columns added in subtables are
ignored.

Instead of writing ONLY before the
table name, you can write * after the
table name to explicitly specify that descendant tables are
included. Writing * is not necessary
since that behavior is the default (unless you have changed the
setting of the sql_inheritance
configuration option). However writing * might be useful to emphasize that additional
tables will be searched.

A joined table is a table derived from two other (real or
derived) tables according to the rules of the particular join
type. Inner, outer, and cross-joins are available.

Join Types

Cross join

T1 CROSS JOIN T2

For each combination of rows from T1 and T2, the derived table will
contain a row consisting of all columns in T1 followed by all columns in
T2. If the tables have
N and M rows respectively, the joined table will have N
* M rows.

FROM T1 CROSS JOIN T2 is equivalent to
FROM T1, T2. It is also equivalent to
FROM T1 INNER JOIN T2 ON TRUE (see below).

The words INNER and
OUTER are optional in all
forms. INNER is the default;
LEFT, RIGHT, and FULL
imply an outer join.

The join condition is
specified in the ON or
USING clause, or implicitly by
the word NATURAL. The join
condition determines which rows from the two source
tables are considered to "match", as explained in detail
below.

The ON clause is the most
general kind of join condition: it takes a Boolean
value expression of the same kind as is used in a
WHERE clause. A pair of rows
from T1 and T2 match if the ON expression evaluates to true for
them.

USING is a shorthand
notation: it takes a comma-separated list of column
names, which the joined tables must have in common, and
forms a join condition specifying equality of each of
these pairs of columns. Furthermore, the output of a
JOIN USING has one column for
each of the equated pairs of input columns, followed by
all of the other columns from each table. Thus,
USING (a, b, c) is equivalent
to ON (t1.a = t2.a AND t1.b = t2.b
AND t1.c = t2.c) with the exception that if
ON is used there will be two
columns a, b, and c in the
result, whereas with USING
there will be only one of each.

Finally, NATURAL is a shorthand form of
USING: it forms a USING list consisting of exactly those
column names that appear in both input tables. As with
USING, these columns appear
only once in the output table.

The possible types of qualified join are:

INNER JOIN

For each row R1 of T1, the joined table has a
row for each row in T2 that satisfies the join
condition with R1.

LEFT OUTER JOIN

First, an inner join is performed. Then, for
each row in T1 that does not satisfy the join
condition with any row in T2, a joined row is
added with null values in columns of T2. Thus,
the joined table unconditionally has at least one
row for each row in T1.

RIGHT OUTER JOIN

First, an inner join is performed. Then, for
each row in T2 that does not satisfy the join
condition with any row in T1, a joined row is
added with null values in columns of T1. This is
the converse of a left join: the result table
will unconditionally have a row for each row in
T2.

FULL OUTER JOIN

First, an inner join is performed. Then, for
each row in T1 that does not satisfy the join
condition with any row in T2, a joined row is
added with null values in columns of T2. Also,
for each row of T2 that does not satisfy the join
condition with any row in T1, a joined row with
null values in the columns of T1 is added.

Joins of all types can be chained together or nested:
either or both of T1 and
T2 might be joined tables.
Parentheses can be used around JOIN
clauses to control the join order. In the absence of
parentheses, JOIN clauses nest
left-to-right.

The alias becomes the new name of the table reference for
the current query — it is no longer possible to refer to the
table by the original name. Thus:

SELECT * FROM my_table AS m WHERE my_table.a > 5;

is not valid according to the SQL standard. In
PostgreSQL this will draw an
error if the add_missing_from
configuration variable is off (as it
is by default). If it is on, an
implicit table reference will be added to the FROM clause, so the query is processed as if
it were written as:

SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;

That will result in a cross join, which is usually not
what you want.

Table aliases are mainly for notational convenience, but
it is necessary to use them when joining a table to itself,
e.g.:

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

Additionally, an alias is required if the table reference
is a subquery (see Section
7.2.1.3).

Parentheses are used to resolve ambiguities. In the
following example, the first statement assigns the alias
b to the second instance of
my_table, but the second statement
assigns the alias to the result of the join:

SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

Another form of table aliasing gives temporary names to
the columns of the table, as well as the table itself:

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

If fewer column aliases are specified than the actual
table has columns, the remaining columns are not renamed.
This syntax is especially useful for self-joins or
subqueries.

When an alias is applied to the output of a JOIN clause, using any of these forms, the
alias hides the original names within the JOIN. For example:

Table functions are functions that produce a set of rows,
made up of either base data types (scalar types) or composite
data types (table rows). They are used like a table, view, or
subquery in the FROM clause of a
query. Columns returned by table functions can be included in
SELECT, JOIN, or WHERE
clauses in the same manner as a table, view, or subquery
column.

If a table function returns a base data type, the single
result column is named like the function. If the function
returns a composite type, the result columns get the same
names as the individual attributes of the type.

A table function can be aliased in the FROM clause, but it also can be left
unaliased. If a function is used in the FROM clause with no alias, the function name
is used as the resulting table name.

In some cases it is useful to define table functions that
can return different column sets depending on how they are
invoked. To support this, the table function can be declared
as returning the pseudotype record.
When such a function is used in a query, the expected row
structure must be specified in the query itself, so that the
system can know how to parse and plan the query. Consider
this example:

The dblink function executes a
remote query (see contrib/dblink).
It is declared to return record since
it might be used for any kind of query. The actual column set
must be specified in the calling query so that the parser
knows, for example, what * should
expand to.

where search_condition is
any value expression (see Section 4.2) that returns a value of
type boolean.

After the processing of the FROM
clause is done, each row of the derived virtual table is
checked against the search condition. If the result of the
condition is true, the row is kept in the output table,
otherwise (that is, if the result is false or null) it is
discarded. The search condition typically references at least
some column of the table generated in the FROM clause; this is not required, but otherwise
the WHERE clause will be fairly
useless.

Note: The join condition of an inner join can be
written either in the WHERE clause
or in the JOIN clause. For
example, these table expressions are equivalent:

FROM a, b WHERE a.id = b.id AND b.val > 5

and:

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

or perhaps even:

FROM a NATURAL JOIN b WHERE b.val > 5

Which one of these you use is mainly a matter of style.
The JOIN syntax in the FROM clause is probably not as portable to
other SQL database management systems. For outer joins
there is no choice in any case: they must be done in the
FROM clause. An ON/USING clause of
an outer join is not equivalent to a
WHERE condition, because it
determines the addition of rows (for unmatched input rows)
as well as the removal of rows from the final result.

Here are some examples of WHERE
clauses:

SELECT ... FROM fdt WHERE c1 > 5
SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

fdt is the table derived in the
FROM clause. Rows that do not meet the
search condition of the WHERE clause
are eliminated from fdt. Notice the
use of scalar subqueries as value expressions. Just like any
other query, the subqueries can employ complex table
expressions. Notice also how fdt is
referenced in the subqueries. Qualifying c1 as fdt.c1 is only
necessary if c1 is also the name of a
column in the derived input table of the subquery. But
qualifying the column name adds clarity even when it is not
needed. This example shows how the column naming scope of an
outer query extends into its inner queries.

The GROUP
BY Clause is used to group together those rows in a table
that share the same values in all the columns listed. The order
in which the columns are listed does not matter. The effect is
to combine each set of rows sharing common values into one
group row that is representative of all rows in the group. This
is done to eliminate redundancy in the output and/or compute
aggregates that apply to these groups. For instance:

In the second query, we could not have written SELECT * FROM test1 GROUP BY x, because there is
no single value for the column y that
could be associated with each group. The grouped-by columns can
be referenced in the select list since they have a single value
in each group.

In general, if a table is grouped, columns that are not used
in the grouping cannot be referenced except in aggregate
expressions. An example with aggregate expressions is:

In this example, the columns product_id, p.name, and
p.price must be in the GROUP BY clause since they are referenced in the
query select list. (Depending on how exactly the products table
is set up, name and price might be fully dependent on the
product ID, so the additional groupings could theoretically be
unnecessary, but this is not implemented yet.) The column
s.units does not have to be in the
GROUP BY list since it is only used in
an aggregate expression (sum(...)),
which represents the sales of a product. For each product, the
query returns a summary row about all sales of the product.

In strict SQL, GROUP BY can only
group by columns of the source table but PostgreSQL extends this to also allow
GROUP BY to group by columns in the
select list. Grouping by value expressions instead of simple
column names is also allowed.

If a table has been grouped using a GROUP BY clause, but then only certain groups
are of interest, the HAVING clause can
be used, much like a WHERE clause, to
eliminate groups from a grouped table. The syntax is:

SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression

Expressions in the HAVING clause
can refer both to grouped expressions and to ungrouped
expressions (which necessarily involve an aggregate
function).

In the example above, the WHERE
clause is selecting rows by a column that is not grouped (the
expression is only true for sales during the last four weeks),
while the HAVING clause restricts the
output to groups with total gross sales over 5000. Note that
the aggregate expressions do not necessarily need to be the
same in all parts of the query.