SELECT

Practical PostgreSQL
Prev		Next

Synopsis

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    expression [ AS output_name ] [, ...]
    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ { UNION | INTERSECT | EXCEPT [ALL] } select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
    [ FOR UPDATE [ OF tablename [, ...] ] ]
    LIMIT { count | ALL } [ { OFFSET | , } start ]

where from_item can be:
[ONLY] table_name[*]
   [[AS] alias[(column_alias_list]] |
          select
   [[AS] alias[(column_alias_list] |
          from_item
    [NATURAL] join_typefrom_item
   [ ON join_condition | USING (join_column_list

Parameters

expression: Use this parameter to specify a column name within a table, or alternatively a valid expression.
output_name: Use this parameter to specify an alternate name for an output column when using the AS clause. This name will then be used during display of the output and can be used to reference the column within ORDER BY and GROUP BY clauses in the same SELECT statement. However, this name does not apply to the WHERE or HAVING clauses; you will need to use the correct column name for them.
from_item: Use this command to specify the name of a table, a sub-select, or a JOIN clause that you wish to retrieve data from.
condition: Use this parameter to specify an expression that returns either true or false.
select: You can use this parameter to specify a full select statement; the only limitations are that you cannot use ORDER BY, FOR UPDATE, or LIMIT unless the statement is enclosed in parenthesis.

FROM items can contain:

table_name

This parameter takes the name of an existing table or view you wish to retrieve rows from. If you do not specify ONLY, all descendant tables will be searched as well. You can also add an asterisk (*) to indicate a wildcard after the table name to have descendant tables searched.

select

You can use this parameter to enter a sub-SELECT statement within the FROM clause of a SELECT statement; doing so will create a temporary table, from which rows can be pulled for the duration of the command. Aliases must be provided for sub-SELECTs, and they must also be surrounded in parentheses.

alias

Use this parameter to specify a substitute name for the table referenced before it.

join_type

Use this parameter to specify the join type, where the type is one of the following:

Table 13-1. Join type options

Join type
`[ INNER ] JOIN`
`LEFT [ OUTER ] JOIN`
`RIGHT [ OUTER ] JOIN`
`FULL [ OUTER ] JOIN`
`CROSS JOIN`

INNER and OUTER join types require that you additionally specify one of the following: NATURAL, ON, or USING. Use the following format for specifying these additional parameters: NATURAL, ON join_condition, USING (column_list). When using CROSS JOIN, do not specify any additional parameters.

join_condition

Use this parameter to specify a qualification condition. Essentially this is the same as using WHERE, but it will only be applied to the two objects being joined.

join_column_list

Use this parameter to list the columns for the USING clause. This list is a shortened way of specifying ON to state the equivalency of columns within the tables of a join.

Results

All rows: The primary result of SELECT is a list of rows that matched your specifications.
Count: SELECT displays the number of rows matched at the end of the row listing.

Description

Use the SELECT command to retrieve rows of data from a singular table or multiple tables. Use the WHERE to state the condition you want rows to meet; they will not be retrieved if they don't meet the condition. If you do not specify any conditions with WHERE, all rows will be retrieved. Refer to the WHERE Clause further in this document for more information on its use.

There are multiple clauses usable within a SELECT statement. See Table 13-2 for a brief listing of these clauses and their descriptions, then read further to find out more detailed instructions on their use.

Table 13-2. SELECT Clauses

Clause	Description
`DISTINCT`	This clause will retrieve only unique rows. Duplicates will not be shown. `SELECT` uses the `ALL` clause by default. `ALL` retrieves all rows that match the given condition, including dupliates.
`DISTINCT ON`	This clause allows you to specify an expression and retrieve only the first row of any set of rows that match on that expression. Use `ORDER BY` to ensure that the first row is the row you are intending to retrieve with the `SELECT` statement.
`EXCEPT`	This clause is used to retrieve rows produced by the left-side query, but not rows retrieved by the right-side query. Read further for more information on using the EXCEPT Clause.
`FOR UPDATE`	Use this clause to set exclusive locking on all rows retrieved by the `SELECT` statement.
`FROM`	This clause specifies the source tables to retrieve rows from. Read further for more information on FROM Clause
`GROUP BY`	Use this clause to break up the list of retrieved rows into groups based on matching values. Read further for more information on the GROUP BY Clause.
`HAVING`	Use this clause to select only the rows that match a given expression. Read further for more information on the HAVING Clause.
`INTERSECT`	This clause retrieves rows that are found in both the lef-side query and the right-side query. Read further for more information on the INTERSECT Clause.
`LIMIT`	This clause returns a specified portion of the retrieved results. Read further for more information on the LIMIT Clause.
`ORDER BY`	This clause sorts retrieved results by a specified column. Read further for more information on the ORDER BY Clause
`UNION`	This clause retrieves collected rows of the queries supplied. Read further for more information on the UNION Clause.

FROM Clause

Use this clause to specify which source table(s) you are retrieving rows from. This can be a single table, or it can be multiple tables. If you've specified multiple tables, the resulting rows available are the Cartesian product of all rows from the source tables.

By default, all sub-tables of a table specified with the FROM clause will be queried in a SELECT statement. There are two ways to avoid this result: either use the ONLY keyword, or use SET SQL_Inheritance TO OFF; to stop the database from defaulting to this behavior.

Instead of a table name, the FROM clause can use the result of a sub-SELECT within parentheses as source for the rest of its operations. To do this you will also need to use an alias for the sub-SELECT (specified with the ALIAS clause).

You can also specify a JOIN clause as the source of data. When doing so, you may have to use parentheses to specify nesting order.

The different join types are mentioned in the synopsis, but not given much of an explanation.

A CROSS JOIN or INNER JOIN types are both simple Cartesian products; the resulting rows from this join would be equivalent to referencing two tables within the FROM clause. A CROSS JOIN is also the same as using an INNER JOIN ON (TRUE) type join, as both types of join have no qualifications for rows, so all rows are retrieved. Essentially, this will return the combined rows of the joined tables.
A LEFT OUTER JOIN returns rows that pass the supplied ON condition. In addition, it returns any rows from the left-hand specified table which do not have a condition-passing row within the right-hand specified table. A NULL will be entered into any blank column for these rows.
A RIGHT OUTER JOIN works the same way as a LEFT OUTER JOIN, except it returns rows from the right-hand specified table that do not have condition-matching rows within the left-hand specified table.
A FULL OUTER JOIN retrieves all condition-matching rows, and also rows on both the left and right- hand sides that do not have condition-matching rows on their respective opposite sides. As with RIGHT/LEFT OUTER JOIN types, rows taken from the left and right-hand sides without rows on their opposite tables will be extended with nulls.

All join types (except CROSS JOIN) require one of the following conditions: ON join_condition, USING ( join_column_list), or NATURAL. Typically, ON will be used, as it allows you to supply a qualification expression suitable for most types of joins you will be using. The join_column_list referenced with USING represents a list of columns. This is shorthand for explicitly stating that the left-hand table's column b is equivalent to the right hand table's column b. The columns specified this way must exist within both the left and right-hand tables. NATURAL is essentially a USING join that specifies all like-named columns between tables.

WHERE Clause

The optional WHERE condition has the general form:

WHERE boolean_expr

The boolean_expr can consist of any expression which evaluates to a boolean value. In many cases, this expression will be one of:

```
     expr cond_op expr
    
```
```
     log_op expr
    
```

The cond_op can be one of:

=, <, <=, >, >= or <>
a conditional operator like ALL, ANY, IN, LIKE
a locally-defined operator

The possible options for log_op are: AND, OR, NOT. SELECT will ignore all rows for which the WHERE condition does not return TRUE.

GROUP BY Clause

Use this clause to condense all selected rows that share the same value for the grouped columns into a singular row. Aggregate functions are then computed across all rows that make up each group. You can specify an input column name, name, or the order number of a column outputted by SELECT. By default, the GROUP BY name will be taken as an input column name.

	Warning
	When using `GROUP BY`, `SELECT` output expressions must refer to ungrouped columns using only within aggregate functions, as there would be more than one posslbe value to return.

HAVING Clause

Use this clause to retrieve a grouped table created by removing all rows that do not satisy the specified boolean expression ( boolean_expr). The difference between HAVING and WHERE is that WHERE cannot be used with aggregate functions while HAVING can.

ORDER BY Clause

Use this clause to specify a column the selected rows should be ordered by, and whether the order should be ascending or descending. The expression supplied can be an output column or an expression formed from input column values. By default, the expression will be taken as an output column name.

You may also specify the order number (left to the right) of a column within a table, which makes it possible to use a column that does not have a proper name. Alternatively, you an use the AS clause to give one of these columns an output name and use that within ORDER BY.

An ORDER BY clause that is being applied to the resulting rows of a UNION, INTERSECT, or EXCEPT query can only provide an output column name or order number.

	Note
	If the expression given matches both an input and output column name, it will be interpreted as the output column name.

Use the DESC (descending) or ASC (ascending) options after the column name to specify whether you want a descending or asecnding order. This is optional. Ascending is chosen by default.

UNION Clause

The table_query should be a SELECT expression without an ORDER BY, FOR UPDATE, or LIMIT clause, though ORDER BY and LIMIT can be used as sub-expressions if located within parentheses. .

A UNION produces a table that is a collection of the rows retrieved by the supplied left and right-hand queries.

By default, the table resulting from a UNION clause will not contain duplicate rows, unelss you specified ALL.

INTERSECT Clause

The table_query should be a select expression that does not contain an ORDER BY, FOR UPDATE, or LIMIT clause.

An INTERSECT produces only rows that appear in both the supplied query outputs.

The resulting table from an INTERSECT will not contain duplicate rows, unless the ALL option is specified.

EXCEPT Clause