Queries

A query is the command to retrieve data from a database or the process of retrieving that data. In SQL, the SELECT command is used to specify queries. The general syntax of the SELECT command is:
SELECT select_list FROM table_expression [sort_specification]
The following sections describe the details of the select list, the table expression, and the sort specification. The simplest kind of query has the following form:
SELECT * FROM table1;
The select list specification * means all columns that the table expression happens to provide. Assuming that there is a table called table1, this command would retrieve all rows and all columns from table1. The method of retrieval depends on the client application. For example, the psql program will display an ASCII-art table on the screen, client libraries will offer functions to retrieve individual rows and columns.

A select list can also select a subset of the available columns or even make calculations on the columns before retrieving them; see the Section called Select Lists. For example, if table1 has columns named a, b, and c (and perhaps others) you can make the following query (assuming that b and c are of a numeric data type):
SELECT a, b + c FROM table1;

FROM table1 is a particularly simple kind of table expression. In general, table expressions can be complex constructs of base tables, joins, and subqueries. But you can also omit the table expression entirely and use the SELECT command as a calculator:
SELECT 3 * 4;
This is more useful if the expressions in the select list return varying results. For example, you could call a function this way.
SELECT random();

Table Expressions

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.

FROM clause

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.

Joined Tables

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

CROSS JOIN
T1 CROSS JOIN T2

For each combination of rows from tables 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 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.

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:

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

Subqueries specifying a derived table must be enclosed in parentheses and must be named using an AS clause. (See the Section called Table and Column Aliases.)

FROM (SELECT * FROM table1) AS alias_name

This example is equivalent to FROM table1 AS alias_name. More interesting cases, which cannot be reduced to a plain join, arise when the subquery involves grouping or aggregation.

Table and Column Aliases

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—in strict SQL, it would be 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. However, referring to the table by the original name accepted by PostgreSQL. Be aware, however, that this extension to the SQL standard may not be portable to other products.

Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, for example,
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 optional.

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. The correct statement would be:
SELECT c.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

Examples

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 optional and can also be omitted.

WHERE clause

The syntax of the WHERE clause is
WHERE search_condition
where search_condition is any value expression (as defined in
the Section called Value Expressions) 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.

Before the implementation of the JOIN syntax in Postgres, 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 products. For outer joins there is no choice in any case: they must be done in the FROM clause. An outer join's ON/USING clause 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.

GROUP BY and HAVING clauses

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 (this differs from 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 eliminates redundancy in the output and/or obtains 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, you cannot know which row in the group the column should 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_expression
If a table has been grouped using a GROUP BY clause, but contains only some groups that are of interest, the HAVING clause can be used (much like a WHERE clause) to eliminate groups from a grouped table.

Note

PostgreSQL allows you to use a HAVING clause without using a GROUP BY. In such a case, the HAVING clause acts like another WHERE clause, but there is not much need to use a HAVING clause in that way. A good guideline to follow is you should use a HAVING condition when you need to 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.

Select Lists

As shown in the previous section, the table expression in the SELECT command constructs an intermediate virtual table by possibly combining tables, views, eliminating rows, grouping, and so on. This table is finally passed on to processing by the select list. The select list determines which columns of the intermediate table are actually output. The simplest kind of select list is * which emits all columns that the table expression produces. Otherwise, a select list is a comma-separated list of value expressions (as defined in the Section called Value Expressions). For instance, it could be a list of column names:
SELECT a, b, c FROM ...
The columns names a, b, and c are either the actual names of the columns of tables referenced in the FROM clause, or the aliases given to them as explained in the Section called Table and Column Aliases. The name space available in the select list is the same as in the WHERE clause (unless grouping is used, in which case it is the same as in the HAVING clause). If more than one table has a column of the same name, the table name must also be given, as in
SELECT tbl1.a, tbl2.a, tbl1.a FROM ...
(see also the Section called WHERE clause).

If an arbitrary value expression is used in the select list, it conceptually adds a new virtual column to the returned table. The value expression is evaluated once for each retrieved row, with the row's values substituted for any column references. But the expressions in the select list do not have to reference any columns in the table expression of the FROM clause; they could be constant arithmetic expressions as well, for instance.

Conditional Expressions

This section describes the SQL-compliant conditional expressions available in PostgreSQL.

Tip

If your needs go beyond the capabilities of these conditional expressions you might want to write a stored procedure in a more expressive programming language.

CASE

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other languages. CASE clauses can be used wherever an expression is valid. condition is an expression that returns a boolean result. If the result is true then the value of the CASE expression is result. If the result is false any subsequent WHEN clauses are searched in the same manner. If no WHEN condition is true then the value of the case expression is the result in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is NULL.

The data types of all the result expressions must be coercible to a single output type. See the Section called UNION and CASE Constructs in Chapter 2 for more detail.

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

This CASE expression (referred to in the SQL Standard as the "simple case") is a specialized variant of the general form above. The expression is computed and compared to all the values in the WHEN clauses until one is found that is equal. If no match is found, the result in the ELSE clause (or NULL) is returned. This is similar to the switch statement in C.

COALESCE

  
COALESCE(value[, ...])

The COALESCE function returns the first of its arguments that is not NULL. This is often useful to substitute a default value for NULL values when data is retrieved for display, for example:
SELECT COALESCE(description, short_description, '(none)') ...

NULLIF

  
NULLIF(value1, value2)

The NULLIF function returns NULL if and only if value1 and value2 are equal. Otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:
SELECT NULLIF(value, '(none)') ...

Tip

COALESCE and NULLIF are just shorthand for CASE expressions. They are actually converted into CASE expressions at a very early stage of processing, and subsequent processing thinks it is dealing with CASE. Thus an incorrect COALESCE or NULLIF usage may draw an error message that refers to CASE.

Sorting Rows

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in random order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

The ORDER BY clause specifies the sort order:
SELECT select_list FROM table_expression ORDER BY column1 [ASC | DESC] [, column2 [ASC | DESC] ...]
column1, and so on, refer to select list columns. These can be either the output name of a column (see
the Section called Column Labels) or the number of a column. Some examples:
SELECT a, b FROM table1 ORDER BY a;
SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, sum(b) FROM table1 GROUP BY a ORDER BY 1;

As an extension to the SQL standard, PostgreSQL also allows ordering by arbitrary expressions:
SELECT a, b FROM table1 ORDER BY a + b;
References to column names in the FROM clause that are renamed in the select list are also allowed:
SELECT a AS b FROM table1 ORDER BY a;
These extensions do not work in queries involving UNION, INTERSECT, or EXCEPT, and are not portable to other products.

Each column specification may be followed by an optional ASC or DESC to set the sort direction. ASC is default. Ascending order puts smaller values first, where "smaller" is defined in terms of the < operator. Similarly, descending order is determined with the > operator.

If more than one sort column is specified, the later entries are used to sort rows that are equal under the order imposed by the earlier sort specifications.

LIMIT and OFFSET

SELECT select_list FROM table_expression [ORDER BY sort_spec] [LIMIT { number | ALL }] [OFFSET number]

LIMIT allows you to retrieve just a portion of the rows that are generated by the rest of the query. If a limit count is given, no more than that many rows will be returned. LIMIT ALL is the same as omitting a LIMIT clause.

OFFSET says to skip that many rows before beginning to return rows to the client. OFFSET 0 is the same as omitting an OFFSET clause. If both OFFSET and LIMIT appear, then OFFSET rows are skipped before starting to count the LIMIT rows that are returned.

When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows. You may be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? The ordering is unknown, unless you specified ORDER BY.

The query optimizer takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

Combining Queries

The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is
query1 UNION [ALL] query2
query1 INTERSECT [ALL] query2
query1 EXCEPT [ALL] query2
query1 and query2 are union compatible queries that can use any of the features discussed up to this point. Set operations can also be nested and chained, for example
query1 UNION query2 UNION query3
which really says
(query1 UNION query2) UNION query3

UNION effectively appends the result of query2 to the result of query1 although there is no guarantee that this is the order in which the rows are actually returned. Furthermore, it eliminates all duplicate rows, in the sense of DISTINCT, unless ALL is specified.

INTERSECT returns all rows that are both in the result of query1 and in the result of query2. Duplicate rows are eliminated unless ALL is specified.

EXCEPT returns all rows that are in the result of query1 but not in the result of query2. Again, duplicates are eliminated unless ALL is specified.

In order to calculate the union, intersection, or difference of two queries, the two queries must be "union compatible", which means that they both return the same number of columns, and that the corresponding columns have compatible data types, as described in the Section called UNION and CASE Constructs in Chapter 2.