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
JOIN construct, or complex combinations of
these. If more than one table reference is listed in the FROM clause, the tables are cross-joined (that is,
the Cartesian product of their rows is formed; see below). The
result of the FROM list is an 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. The general syntax of
a joined table is

T1join_typeT2 [join_condition]

Joins of all types can be chained together, or nested: either or
both T1 and T2 can 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.

Join Types

Cross join

T1 CROSS JOIN T2

For every possible combination of rows from T1 and T2
(i.e., a Cartesian product), the joined 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 INNER
JOIN T2 ON TRUE (see below).
It is also equivalent to FROM T1, T2.

Note: This latter equivalence does not hold exactly when
more than two tables appear, because JOIN
binds more tightly than comma. For example FROM
T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM T1, T2 INNER JOIN T3 ON condition because the condition can reference T1 in the first case but not the second.

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

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.

The USING clause is a shorthand that
allows you to take advantage of the specific situation where both
sides of the join use the same name for the joining column(s). It
takes a comma-separated list of the shared column names and forms a
join condition that includes an equality comparison for each one.
For example, joining T1 and
T2 with USING
(a, b) produces the join condition ON
T1.a = T2.a AND T1.b
= T2.b.

Furthermore, the output of JOIN USING
suppresses redundant columns: there is no need to print both of the
matched columns, since they must have equal values. While
JOIN ON produces all columns from
T1 followed by all columns from
T2, JOIN
USING produces one output column for each of the listed column
pairs (in the listed order), followed by any remaining columns from
T1, followed by any remaining
columns from T2.

Finally, NATURAL is a shorthand form of
USING: it forms a USING list consisting of all column names that
appear in both input tables. As with USING, these columns appear only once in the output
table. If there are no common column names, NATURAL JOIN behaves like JOIN
... ON TRUE, producing a cross-product join.

Note:USING is reasonably safe
from column changes in the joined relations since only the listed
columns are combined. NATURAL is
considerably more risky since any schema changes to either relation
that cause a new matching column name to be present will cause the
join to combine that new column as well.

This is because a restriction placed in the ON clause is processed before the join, while a restriction
placed in the WHERE clause is processed
after the join. That does
not matter with inner joins, but it matters a lot with outer
joins.

The alias becomes the new name of the table reference so far as
the current query is concerned — it is not allowed to refer to the
table by the original name elsewhere in the query. Thus, this is
not valid:

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

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, the alias hides the original name(s)
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 name matches the function name. 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
(part of the dblink module) executes a
remote query. 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.

Subqueries appearing in FROM can be
preceded by the key word LATERAL. This
allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently
and so cannot cross-reference any other FROM item.)

Table functions appearing in FROM can
also be preceded by the key word LATERAL,
but for functions the key word is optional; the function's
arguments can contain references to columns provided by preceding
FROM items in any case.

A LATERAL item can appear at top level
in the FROM list, or within a JOIN tree. In the latter case it can also refer to
any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

When a FROM item contains LATERAL cross-references, evaluation proceeds as
follows: for each row of the FROM item
providing the cross-referenced column(s), or set of rows of
multiple FROM items providing the columns,
the LATERAL item is evaluated using that
row or row set's values of the columns. The resulting row(s) are
joined as usual with the rows they were computed from. This is
repeated for each row or set of rows from the column source
table(s).

This is not especially useful since it has exactly the same
result as the more conventional

SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

LATERAL is primarily useful when the
cross-referenced column is necessary for computing the row(s) to be
joined. A common application is providing an argument value for a
set-returning function. For example, supposing that vertices(polygon) returns the set of vertices of
a polygon, we could identify close-together vertices of polygons
stored in a table with:

or in several other equivalent formulations. (As already
mentioned, the LATERAL key word is
unnecessary in this example, but we use it for clarity.)

It is often particularly handy to LEFT
JOIN to a LATERAL subquery, so that
source rows will appear in the result even if the LATERAL subquery produces no rows for them. For
example, if get_product_names()
returns the names of products made by a manufacturer, but some
manufacturers in our table currently produce no products, we could
find out which ones those are like this:

SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;

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 (i.e., if the result is
false or null) it is discarded. The search condition typically
references at least one 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, even though it is in the SQL standard.
For outer joins there is no choice: they must be done in the
FROM clause. The ON or USING clause of an
outer join is not
equivalent to a WHERE condition, because
it results in the addition of rows (for unmatched input rows) as
well as the removal of rows in 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
have 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 having common values into one group row that
represents 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 listed
in GROUP BY 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 (but see below). 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.

If the products table is set up so that, say, product_id is the primary key, then it would be
enough to group by product_id in the above
example, since name and price would be functionally dependent on the product ID, and so
there would be no ambiguity about which name and price value to
return for each product ID group.

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 GROUP
BY, but only certain groups are of interest, the HAVING clause can be used, much like a WHERE clause, to eliminate groups from the result.
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.

If a query contains aggregate function calls, but no GROUP BY clause, grouping still occurs: the result
is a single group row (or perhaps no rows at all, if the single row
is then eliminated by HAVING). The same is
true if it contains a HAVING clause, even
without any aggregate function calls or GROUP
BY clause.

If the query contains any window functions (see Section 3.5, Section 9.21 and Section 4.2.8),
these functions are evaluated after any grouping, aggregation, and
HAVING filtering is performed. That is, if
the query uses any aggregates, GROUP BY,
or HAVING, then the rows seen by the
window functions are the group rows instead of the original table
rows from FROM/WHERE.

When multiple window functions are used, all the window
functions having syntactically equivalent PARTITION BY and ORDER BY
clauses in their window definitions are guaranteed to be evaluated
in a single pass over the data. Therefore they will see the same
sort ordering, even if the ORDER BY does
not uniquely determine an ordering. However, no guarantees are made
about the evaluation of functions having different PARTITION BY or ORDER BY
specifications. (In such cases a sort step is typically required
between the passes of window function evaluations, and the sort is
not guaranteed to preserve ordering of rows that its ORDER BY sees as equivalent.)

Currently, window functions always require presorted data, and
so the query output will be ordered according to one or another of
the window functions' PARTITION
BY/ORDER BY clauses. It is not
recommended to rely on this, however. Use an explicit top-level
ORDER BY clause if you want to be sure the
results are sorted in a particular way.