|
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 may 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 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-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. A cross join is equivalent to an INNER JOIN ON TRUE.
Tip: FROM T1
CROSS JOIN T2 is equivalent to FROM T1, T2.
- Qualified joins
-
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 T2
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 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.
To put this together, assume we have tables t1
num | name
-----+------
1 | a
2 | b
3 | c
and t2
num | value
-----+-------
1 | xxx
3 | yyy
5 | zzz
then we get the following results for the various joins:
=> SELECT * FROM t1 CROSS JOIN t2;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
1 | a | 3 | yyy
1 | a | 5 | zzz
2 | b | 1 | xxx
2 | b | 3 | yyy
2 | b | 5 | zzz
3 | c | 1 | xxx
3 | c | 3 | yyy
3 | c | 5 | zzz
(9 rows)
=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)
=> SELECT * FROM t1 INNER JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)
=> SELECT * FROM t1 NATURAL INNER JOIN t2;
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)
=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
(3 rows)
=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
2 | b |
3 | c | yyy
(3 rows)
=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
| | 5 | zzz
(3 rows)
=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
| | 5 | zzz
(4 rows)
The join condition specified with ON can also contain
conditions that do not relate directly to the join. This can prove useful for some queries
but needs to be thought out carefully. For example:
=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | |
(3 rows)
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.
To create a table alias, write
FROM table_reference AS alias
or
FROM table_reference alias
The AS key word is noise. alias
can be any identifier.
A typical application of table aliases is to assign short identifiers to long table
names to keep the join clauses readable. For example:
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
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;
which 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 my_table AS a CROSS JOIN my_table AS b ...
Additionally, an alias is required if the table reference is a subquery (see Section
4.2.1.3).
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 ...
Another form of table aliasing also gives temporary names to the columns of the table:
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,
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.
Subqueries specifying a derived table must be enclosed in parentheses and must be assigned a table alias name. (See Section
4.2.1.2.) For example:
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.
The syntax of the WHERE clause is
WHERE search_condition
where search_condition is any value expression as
defined in Section
1.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 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 > 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 products. 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 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. 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 compute aggregates that apply to these groups. For
instance:
=> SELECT * FROM test1;
x | y
---+---
a | 3
c | 2
b | 5
a | 1
(4 rows)
=> SELECT x FROM test1 GROUP BY x;
x
---
a
b
c
(3 rows)
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 known constant value per 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:
=> SELECT x, sum(y) FROM test1 GROUP BY x;
x | sum
---+-----
a | 4
b | 5
c | 2
(3 rows)
Here sum() is an aggregate function that computes a single value
over the entire group. More information about the available aggregate functions can be found
in Section 6.14.
Tip: Grouping without aggregate expressions effectively calculates the set of
distinct values in a column. This can also be achieved using the DISTINCT
clause (see Section
4.3.3).
Here is another example: sum(sales) on a table grouped by
product code gives the total sales for each product, not the total sales on all products.
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
FROM products p LEFT JOIN sales s USING (product_id)
GROUP BY product_id, p.name, p.price;
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 may 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 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 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).
Example:
=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
x | sum
---+-----
a | 4
b | 5
(2 rows)
=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
x | sum
---+-----
a | 4
b | 5
(2 rows)
Again, a more realistic example:
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
FROM products p LEFT JOIN sales s USING (product_id)
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY product_id, 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. Note that the aggregate expressions do
not necessarily need to be the same everywhere.
|
