query SELECT The previous chapters explained how to create tables, how to fill them with data, and how to manipulate that data. Now we finally discuss how to retrieve the data out of the database. The process of retrieving or the command to retrieve data from a database is called a query. 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 form SELECT * FROM table1; 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, while client libraries will offer functions to extract individual values from the query result.) The select list specification * means all columns that the table expression happens to provide. A select list can also select a subset of the available columns or make calculations using the columns. For example, if table1 has columns named a, b, and c (and perhaps others) you can make the following query: SELECT a, b + c FROM table1; (assuming that b and c are of a numerical data type). See for more details. FROM table1 is a particularly simple kind of table expression: it reads just one table. 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 expression 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. ONLY 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 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. join 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. Cross join join cross 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. FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2. It is also equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). Qualified joins join outer outer join 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. join natural natural join 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 join left left 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 join right right 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) alias in the FROM clause label alias 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 ). 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. subquery Subqueries specifying a derived table must be enclosed in parentheses and must be assigned a table alias name. (See .) 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. table function function in the FROM clause 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 may 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 is named like the function. If the function returns a composite type, the result columns get the same names as the individual attributes of the type. A table function may be aliased in the FROM clause, but it also may 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. Some examples: CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS ' SELECT * FROM foo WHERE fooid = $1; ' LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1; SELECT * FROM foo WHERE foosubid IN (select foosubid from getfoo(foo.fooid) z where z.fooid = foo.fooid); CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo; 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: SELECT * FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; The dblink function executes a remote query (see contrib/dblink). 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. WHERE The syntax of the WHERE clause is WHERE search_condition where search_condition is any value expression as defined in 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. 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. GROUP BY grouping 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 . Grouping without aggregate expressions effectively calculates the set of distinct values in a column. This can also be achieved using the DISTINCT clause (see ). Here is another example: it calculates the total sales for each product (rather than 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 sales of a product. For each product, the query returns a summary row 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. HAVING 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 (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. SELECT select list 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, etc. 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 ). 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 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.b FROM ... When working with multiple tables, it can also be useful to ask for all the columns of a particular table: SELECT tbl1.*, tbl2.a FROM ... (See also .) 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 result 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. alias in the select list The entries in the select list can be assigned names for further processing. The further processing in this case is an optional sort specification and the client application (e.g., column headers for display). For example: SELECT a AS value, b + c AS sum FROM ... If no output column name is specified using AS, the system assigns a default name. For simple column references, this is the name of the referenced column. For function calls, this is the name of the function. For complex expressions, the system will generate a generic name. The naming of output columns here is different from that done in the FROM clause (see ). This pipeline will in fact allow you to rename the same column twice, but the name chosen in the select list is the one that will be passed on. DISTINCT duplicates After the select list has been processed, the result table may optionally be subject to the elimination of duplicates. The DISTINCT key word is written directly after the SELECT to enable this: SELECT DISTINCT select_list ... (Instead of DISTINCT the word ALL can be used to select the default behavior of retaining all rows.) null valuein DISTINCT Obviously, two rows are considered distinct if they differ in at least one column value. Null values are considered equal in this comparison. Alternatively, an arbitrary expression can determine what rows are to be considered distinct: SELECT DISTINCT ON (expression , expression ...) select_list ... Here expression is an arbitrary value expression that is evaluated for all rows. A set of rows for which all the expressions are equal are considered duplicates, and only the first row of the set is kept in the output. Note that the first row of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at the DISTINCT filter. (DISTINCT ON processing occurs after ORDER BY sorting.) The DISTINCT ON clause is not part of the SQL standard and is sometimes considered bad style because of the potentially indeterminate nature of its results. With judicious use of GROUP BY and subqueries in FROM the construct can be avoided, but it is often the most convenient alternative. UNION INTERSECT EXCEPT set union set intersection set difference set operation 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 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 UNION ALL is used. INTERSECT returns all rows that are both in the result of query1 and in the result of query2. Duplicate rows are eliminated unless INTERSECT ALL is used. EXCEPT returns all rows that are in the result of query1 but not in the result of query2. (This is sometimes called the difference between two queries.) Again, duplicates are eliminated unless EXCEPT ALL is used. 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 . sorting ORDER BY 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, etc., refer to select list columns. These can be either the output name of a column (see ) 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; But these extensions do not work in queries involving UNION, INTERSECT, or EXCEPT, and are not portable to other SQL databases. Each column specification may be followed by an optional ASC or DESC to set the sort direction to ascending or descending. ASC order is the default. Ascending order puts smaller values first, where smaller is defined in terms of the < operator. Similarly, descending order is determined with the > operator. Actually, PostgreSQL uses the default B-tree operator class for the column's data type to determine the sort ordering for ASC and DESC. Conventionally, data types will be set up so that the < and > operators correspond to this sort ordering, but a user-defined data type's designer could choose to do something different. 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 columns. LIMIT OFFSET LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of the query: SELECT select_list FROM table_expression LIMIT { number | ALL } OFFSET number If a limit count is given, no more than that many rows will be returned (but possibly less, if the query itself yields less rows). LIMIT ALL is the same as omitting the LIMIT clause. OFFSET says to skip that many rows before beginning to return rows. OFFSET 0 is the same as omitting the 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 important 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.