| 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.
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, equi-join and cartesian products are available.
Join Types - Cartesian Products
T1 CROSS JOIN T2 Cartesian product also known as cross join is a combination of rows from
T1 and
T2, such that 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.
- 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 WHERE clause. 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.
To write a query that performs an outer join of T1 and T2 and
returns all the rows from table T1, use the
LEFT [OUTER] JOIN syntax in the
FROM clause, or apply the outer join
operator (+) to all columns of T2 in the
join condition in the WHERE clause.
- 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.
To write a query that performs an outer join of T1 and T2 and
returns all rows from table T2, use the
RIGHT [OUTER] JOIN syntax in the
FROM clause, or apply the outer join
operator (+) to all columns of T2 in the
join condition in the WHERE clause.
- 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
or
FROM table_reference alias
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 emp e, dept d WHERE e.deptno = d.deptno;
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 emp e WHERE emp.deptno > 20;
is not valid SQL syntax.
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 6.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 6.2.1.2.) For
example:
FROM (SELECT * FROM emp) AS alias_name
This example is equivalent to FROM emp 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 clause is
WHERE search_condition
where search_condition is any value
expression as defined in Section 3.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 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.
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 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 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 8.11.
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 6.3.3).
Here is another example: it calculates the total salary for all
the employees in each department.
SELECT deptno, SUM(sal) AS salary
FROM emp
GROUP BY deptno;
As a general rule all the columns referenced in the query select
list must be must be included in the GROUP BY clause.
The column sal does not have to be in the GROUP
BY list since it is only used in an aggregate expression
(sum(...)), which represents the salary
of an employee. For each department, the query returns a summary row about
the total salaries of all the employees in that department.
In strict SQL, GROUP BY can only group by columns of
the source table but EnterpriseDB 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 deptno, MIN(sal) AS "Minimum Salary", MAX(sal) AS "Maximum Salary"
FROM emp
GROUP BY deptno
HAVING MIN(sal) < 5000;
In the example above, the GROUP BY clause is grouping all
rows by the department that they belong to, while the HAVING
clause restricts the output to groups with a minimum salary of less than
5000. Note that the aggregate expressions do not necessarily need
to be the same in all parts of the query.
| |
---|