A table expression specifies 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. The derived table that is produced by all these transformations provides the input rows used to compute output rows as specified by the select list of column value expressions.
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 may be a table name 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 derived table that may 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 supertable of a table inheritance hierarchy, the table reference produces rows of not only that table but all of its subtable successors, unless the keyword 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.
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 JOIN are supported.
Join Types
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. A cross join is equivalent to an INNER JOIN ON TRUE.
Tip: FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2.
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2The words INNER and OUTER are optional for all joins. 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.
The possible types of qualified JOIN are:
For each row R1 of T1, the joined table has a row for each row in T2 that satisfies the join condition with R1.
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 returned with NULL values in columns of T2. Thus, the joined table unconditionally has at least one row for each row in T1.
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 returned 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.
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 returned 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 returned.
Joins of all types can be chained together or nested: either or both of T1 and T2 may be joined tables. Parentheses may be used around JOIN clauses to control the join order. In the absence of parentheses, JOIN clauses nest left-to-right.
Subqueries specifying a derived table must be enclosed in parentheses and must be named using an AS clause. (See Section 2.2.1.3.)
FROM (SELECT * FROM table1) AS alias_name
This example is equivalent to FROM table1 AS alias_name. More interesting cases, which can't be reduced to a plain join, arise when the subquery involves grouping or aggregation.
A temporary name can be given to tables and complex table references to be used for references to the derived table in further processing. This is called a table alias.
FROM table_reference AS alias
Here, alias can be any regular identifier. 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 SQL syntax. What will actually happen (this is a PostgreSQL extension to the standard) is that an implicit table reference is 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;
Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g.,
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
Additionally, an alias is required if the table reference is a subquery.
Parentheses are used to resolve ambiguities. The following statement will assign the alias b to the result of the join, unlike the previous example:
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
FROM table_reference alias
This form is equivalent to the previously treated one; the AS key word is noise.
FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )
In this form, in addition to renaming the table as described above, the columns of the table are also given temporary names for use by the surrounding query. 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,
SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
is valid SQL, but
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
is not valid: the table alias A is not visible outside the alias C.
FROM T1 INNER JOIN T2 USING (C) FROM T1 LEFT OUTER JOIN T2 USING (C) FROM (T1 RIGHT OUTER JOIN T2 ON (T1.C1=T2.C1)) AS DT1 FROM (T1 FULL OUTER JOIN T2 USING (C)) AS DT1 (DT1C1, DT1C2) FROM T1 NATURAL INNER JOIN T2 FROM T1 NATURAL LEFT OUTER JOIN T2 FROM T1 NATURAL RIGHT OUTER JOIN T2 FROM T1 NATURAL FULL OUTER JOIN T2 FROM (SELECT * FROM T1) DT1 CROSS JOIN T2, T3 FROM (SELECT * FROM T1) DT1, T2, T3
Above are some examples of joined tables and complex derived tables. Notice how the AS clause renames or names a derived table and how the optional comma-separated list of column names that follows renames the columns. The last two FROM clauses produce the same derived table from T1, T2, and T3. The AS keyword was omitted in naming the subquery as DT1. The keywords OUTER and INNER are noise that can be omitted also.
The syntax of the WHERE clause is
WHERE search_condition
where search_condition is any value expression as defined in Section 1.3 that returns a value of type boolean.
After the processing of the FROM clause is done, each row of the derived 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 in the table generated in the FROM clause; this is not required, but otherwise the WHERE clause will be fairly useless.
Note: Before the implementation of the JOIN syntax, it was necessary to put the join condition of an inner join in the WHERE clause. For example, these table expressions are equivalent:
FROM a, b WHERE a.id = b.id AND b.val > 5and
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5or perhaps even
FROM a NATURAL JOIN b WHERE b.val > 5Which 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 products. For outer joins there is no choice in any case: they must be done in the FROM clause. A 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.
FROM FDT WHERE
C1 > 5
FROM FDT WHERE
C1 IN (1, 2, 3)
FROM FDT WHERE
C1 IN (SELECT C1 FROM T2)
FROM FDT WHERE
C1 IN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10)
FROM FDT WHERE
C1 BETWEEN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10) AND 100
FROM FDT WHERE
EXISTS (SELECT C1 FROM T2 WHERE C2 > FDT.C1)In the examples above, 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 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. Qualifying the column name adds clarity even when it is not needed. This shows how the column naming scope of an outer query extends into its inner queries.
After passing the WHERE filter, the derived input table may be subject to grouping, using the GROUP BY clause, and elimination of group rows using the HAVING clause.
SELECT select_list
FROM ...
[WHERE ...]
GROUP BY grouping_column_reference [, grouping_column_reference]...The GROUP BY clause is used to group together 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 (as opposed to an ORDER BY clause). The purpose is to reduce each group 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 obtain aggregates that apply to these groups.
Once a table is grouped, columns that are not used in the grouping cannot be referenced except in aggregate expressions, since a specific value in those columns is ambiguous - which row in the group should it come from? The grouped-by columns can be referenced in select list column expressions since they have a known constant value per group. Aggregate functions on the ungrouped columns provide values that span the rows of a group, not of the whole table. For instance, a sum(sales) on a table grouped by product code gives the total sales for each product, not the total sales on all products. Aggregates computed on the ungrouped columns are representative of the group, whereas individual values of an ungrouped column are not.
Example:
SELECT pid, p.name, (sum(s.units) * p.price) AS sales FROM products p LEFT JOIN sales s USING ( pid ) GROUP BY pid, p.name, p.price;
In this example, the columns pid, p.name, and p.price must be in the GROUP BY clause since they are referenced in the query select list. 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 group of sales of a product. For each product, a summary row is returned 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 select columns in the query select list. Grouping by value expressions instead of simple column names is also allowed.
SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expressionIf 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. PostgreSQL allows a HAVING clause to be used without a GROUP BY, in which case it acts like another WHERE clause, but the point in using HAVING that way is not clear. A good rule of thumb is that a HAVING condition should refer to the results of aggregate functions. A restriction that does not involve an aggregate is more efficiently expressed in the WHERE clause.
Example:
SELECT pid AS "Products",
p.name AS "Over 5000",
(sum(s.units) * (p.price - p.cost)) AS "Past Month Profit"
FROM products p LEFT JOIN sales s USING ( pid )
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY pid, p.name, p.price, p.cost
HAVING sum(p.price * s.units) > 5000;In the example above, the WHERE clause is selecting rows by a column that is not grouped, while the HAVING clause restricts the output to groups with total gross sales over 5000.