Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Retrieving Rows with SELECT

The heart of all SQL queries is the SELECT command. SELECT is used to build queries (also known as SELECT statements). Queries are the only SQL instructions by which your data can be retrieved from tables and views. The data returned via a query is called a result set and consists of rows, with columns, similar to a table.

The columns of a result set are not stored on the disk in any fixed form. They are purely a temporary result of the query's requested data. A query on a table may return a result set with the same column structure as the table, or it may differ drastically. Result sets may even have columns which are drawn from several other tables by a single query.

Since it is central to PostgreSQL, SELECT is easily the most complicated single command, having the most available clauses and parameters. The following is the syntax for SELECT. The terms used are summarized and described in greater detail within the following sections. The term expression is used to refer to either a column name, or a general expression (such as a column being operated upon by a constant, or another column).

  SELECT [ ALL | DISTINCT [ ON ( 
expression
 [, ...] ) ] ]
         
target
 [ AS 
name
 ] [, ...]
         [ FROM 
source
 [, ...] ]
                [ [ NATURAL ] 
join_type
 
source

                [ ON 
condition
 | USING ( 
column_list
 ) ] ]
                [, ...]
         [ WHERE 
condition
 ]
         [ GROUP BY 
expression
 [, ...] ]
         [ HAVING 
condition
 [, ...] ]
         [ { UNION | INTERSECT | EXCEPT } [ ALL ] 
sub-query
 ]
         [ ORDER BY 
expression

                 [ ASC | DESC | USING 
operator
 ]
                 [, ...] ]
         [ FOR UPDATE [ OF 
table
 [, ...] ] ]
         [ LIMIT { 
count
 | ALL } [ { OFFSET | , } 
start
 ] ]

In this syntax diagram, source may be either a table name or a subselect. The syntax for these general forms is as follows:

  FROM { [ ONLY ] 
table
 [ [ AS ] 
alias
 [ ( 
column_alias
 [, ...] ) ] ] |
         ( 
query
 ) [ AS ] 
alias
 [ ( 
column_alias
 [, ...] ) ] }

ALL

The ALL keyword may be specified as a noise term to make it clear that all rows should be returned.

DISTINCT [ ON ( expression [, ...] ) ]

The DISTINCT clause specifies a column (or expression) for which to retrieve only one row per unique value of expression .

target [ AS name ] [, ...]

The SELECT targets are usually column names, though they can be constants, identifier, function or general expression. Each target requested must be separated by commas, and may be named dynamically to name via the AS clause. Supplying the asterisk symbol (*) as a target is shorthand for requesting all non-system columns, and may be listed along with other targets.

FROM source [, ...]

The FROM clause dictates the source that PostgreSQL will look in for the specified targets . The source , in this case, may be a table name or a sub-query. You can specify numerous sources, separated by commas. (This is roughly equivalent to a cross join). The syntax for the FROM clause is described in more detail later in this section.

[ NATURAL ] join_type source [ ON condition | USING ( column_list ) ]

The FROM sources may be joined together via the JOIN clause, which requires a join_type (e.g., INNER, FULL OUTER, CROSS) and may require a condition or column_list to further define the nature of the join, depending on the join_type .

WHERE condition

The WHERE clause constrains the result set from the SELECT statement to specified criteria, which are defined by condition . Conditions must return a single Boolean value (true or false), but may consist of several checks combined with logical operators (e.g., with AND, and OR) to indicate that available rows must meet all supplied conditions to be included in the statement's results.

GROUP BY expression [, ...]

The GROUP BY clause aggregates (groups) rows together by the criteria described in expression . This can be as simple as a column name (and often is) or an arbitrary expression applied to values of the result set.

HAVING condition [, ...]

The HAVING clause is similar to the WHERE clause, but checks its conditions on aggregated (grouped) sets instead of atomic rows.

{ UNION | INTERSECT | EXCEPT } [ ALL ] sub-query

Performs one of three set operations between the SELECT statement and a second query, returning their result sets in uniform column structure (which must be compatible).

UNION

Returns the set of collected rows.

INTERSECT

Returns the set of rows where the values of the two sets overlap.

EXCEPT

Returns the set of rows which are found in the SELECT statement, but not found in the secondary query.

ORDER BY expression

Sorts the results of the SELECT statement by expression .

[ ASC | DESC | USING operator ]

Determines whether or not the ORDER BY expression proceeds in ascending order (ASC), or descending order (DESC). An operator may alternatively be specified with the USING keyword (e.g., < or >).

FOR UPDATE [ OF table [, ...] ]

Allows for exclusive locking of the returned rows. When used within a transaction block, FOR UPDATE locks the rows of the specified table until the transaction is committed. While locked, the rows cannot be updated by other transactions.

LIMIT { count | ALL }

Limits the number of rows returned to a maximum of count , or explicitly allows ALL rows.

{ OFFSET | , } start

Instructs the LIMIT clause at what point to begin limiting the results. For example, a LIMIT with a count set to 100, and an OFFSET clause with a start value of 50 would return the rows from 50 to 150 (if there are that many results to return).

Terms used in the FROM clause's syntax description are as follows:

[ ONLY ] table

The table name specifies what table to use as a source for the SELECT statement. Specifying the ONLY clause causes the rows of any child's table to be omitted from the query.

[ AS ] alias

An alias may optionally be assigned to a FROM source, in order to simplify a query (e.g., books might be temporarily referenced with an alias of b). The AS term is considered noise, and is optional.

( query ) [ AS ] alias

Any valid SELECT statement may be placed in parentheses as the query . This causes the result set created by the query to be used as a FROM source, as if it had been a static table. This use of a sub-query requires a specified alias .

( column_alias [, ...] )

The FROM sources which have assigned aliases may also alias columns by specifying arbitrary column aliases. Each column_alias must be separated by commas, and grouped within parentheses following the FROM source's alias. These aliases must match the order of the defined columns in the table to which it is applied.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire