Unix/Linux Go Back    


RedHat 9 (Linux i386) - man page for select (redhat section 7)

Linux & Unix Commands - Search Man Pages
Man Page or Keyword Search:   man
Select Man Page Set:       apropos Keyword Search (sections above)


SELECT(7)				   SQL Commands 				SELECT(7)

NAME
       SELECT - retrieve rows from a table or view

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

       where from_item can be:

       [ ONLY ] table_name [ * ]
	   [ [ AS ] alias [ ( column_alias_list ) ] ]
       |
       ( select )
	   [ AS ] alias [ ( column_alias_list ) ]
       |
       table_function_name ( [ argument [, ...] ] )
	   [ AS ] alias [ ( column_alias_list | column_definition_list ) ]
       |
       table_function_name ( [ argument [, ...] ] )
	   AS ( column_definition_list )
       |
       from_item [ NATURAL ] join_type from_item
	   [ ON join_condition | USING ( join_column_list ) ]

   INPUTS
       expression
	      The name of a table's column or an expression.

       output_name
	      Specifies  another name for an output column using the AS clause. This name is pri-
	      marily used to label the column for display. It can also be used to  refer  to  the
	      column's value in ORDER BY and GROUP BY clauses. But the output_name cannot be used
	      in the WHERE or HAVING clauses; write out the expression instead.

       from_item
	      A table reference, sub-SELECT, table  function,  or  JOIN  clause.  See  below  for
	      details.

       condition
	      A  Boolean  expression  giving a result of true or false.  See the WHERE and HAVING
	      clause descriptions below.

       select A select statement with all features except the ORDER  BY,  LIMIT/OFFSET,  and  FOR
	      UPDATE clauses (even those can be used when the select is parenthesized).

       FROM items can contain:

       table_name
	      The  name  (optionally  schema-qualified) of an existing table or view.  If ONLY is
	      specified, only that table is scanned. If ONLY is not specified, the table and  all
	      its  descendant tables (if any) are scanned. * can be appended to the table name to
	      indicate that descendant tables are to be scanned, but in the current version, this
	      is  the  default behavior. (In releases before 7.1, ONLY was the default behavior.)
	      The default behavior can be modified by changing the SQL_INHERITANCE  configuration
	      option.

       alias  A  substitute  name  for	the FROM item containing the alias.  An alias is used for
	      brevity or to eliminate ambiguity for self-joins (where the same table  is  scanned
	      multiple	times). When an alias is provided, it completely hides the actual name of
	      the table or table function; for example given FROM foo AS f, the remainder of  the
	      SELECT must refer to this FROM item as f not foo.  If an alias is written, a column
	      alias list can also be written to provide substitute names for one or more  columns
	      of the table.

       select A  sub-SELECT  can  appear  in the FROM clause. This acts as though its output were
	      created as a temporary table for the duration of this single SELECT  command.  Note
	      that  the  sub-SELECT  must be surrounded by parentheses, and an alias must be pro-
	      vided for it.

       table function
	      A table function can appear in the FROM clause. This acts as though its output were
	      created  as  a  temporary  table for the duration of this single SELECT command. An
	      alias may also be used. If an alias is written, a column alias  list  can  also  be
	      written  to provide substitute names for one or more columns of the table function.
	      If the table function has been defined as returning the record data type, an alias,
	      or  the  keyword	AS,  must be present, followed by a column definition list in the
	      form ( column_name data_type [, ... ] ).	The column definition list must match the
	      actual number and types of columns returned by the function.

       join_type
	      One  of  [  INNER ] JOIN, LEFT [ OUTER ] JOIN, RIGHT [ OUTER ] JOIN, FULL [ OUTER ]
	      JOIN, or CROSS JOIN.  For INNER and OUTER join types, exactly one  of  NATURAL,  ON
	      join_condition,  or USING ( join_column_list ) must appear. For CROSS JOIN, none of
	      these items may appear.

       join_condition
	      A qualification condition. This is similar to the WHERE condition  except  that  it
	      only applies to the two from_items being joined in this JOIN clause.

       join_column_list
	      A  USING column list ( a, b, ... ) is shorthand for the ON condition left_table.a =
	      right_table.a AND left_table.b = right_table.b ...

   OUTPUTS
       Rows   The complete set of rows resulting from the query specification.

       count  The count of rows returned by the query.

DESCRIPTION
       SELECT will return rows from one or more tables.  Candidates for selection are rows  which
       satisfy	the  WHERE  condition;	if WHERE is omitted, all rows are candidates.  (See WHERE
       Clause [select(7)].)

       Actually, the returned rows are not directly the rows  produced	by  the  FROM/WHERE/GROUP
       BY/HAVING  clauses;  rather,  the  output  rows	are formed by computing the SELECT output
       expressions for each selected row.  * can be written in the output list as a shorthand for
       all the columns of the selected rows. Also, one can write table_name.*  as a shorthand for
       the columns coming from just that table.

       DISTINCT will eliminate duplicate rows from the result.	ALL (the default) will return all
       candidate rows, including duplicates.

       DISTINCT  ON eliminates rows that match on all the specified expressions, keeping only the
       first row of each set of duplicates. The DISTINCT ON expressions are interpreted using the
       same  rules  as for ORDER BY items; see below.  Note that the ``first row'' of each set is
       unpredictable unless ORDER BY is used to ensure that the desired row  appears  first.  For
       example,

	       SELECT DISTINCT ON (location) location, time, report
	       FROM weatherReports
	       ORDER BY location, time DESC;

       retrieves  the  most recent weather report for each location. But if we had not used ORDER
       BY to force descending order of time values for each location, we'd have gotten	a  report
       of unpredictable age for each location.

       The  GROUP BY clause allows a user to divide a table into groups of rows that match on one
       or more values.	(See GROUP BY Clause [select(7)].)

       The HAVING clause allows selection of only those groups of rows meeting the specified con-
       dition.	(See HAVING Clause [select(7)].)

       The  ORDER  BY clause causes the returned rows to be sorted in a specified order. If ORDER
       BY is not given, the rows are returned in whatever order the system finds cheapest to pro-
       duce.  (See ORDER BY Clause [select(7)].)

       SELECT queries can be combined using UNION, INTERSECT, and EXCEPT operators. Use parenthe-
       ses if necessary to determine the ordering of these operators.

       The UNION operator computes the collection of  rows  returned  by  the  queries	involved.
       Duplicate rows are eliminated unless ALL is specified.  (See UNION Clause [select(7)].)

       The  INTERSECT operator computes the rows that are common to both queries.  Duplicate rows
       are eliminated unless ALL is specified.	(See INTERSECT Clause [select(7)].)

       The EXCEPT operator computes the rows returned by the  first  query  but  not  the  second
       query.	Duplicate  rows  are  eliminated  unless  ALL  is  specified.  (See EXCEPT Clause
       [select(7)].)

       The LIMIT clause allows a subset of the rows produced by the query to be returned  to  the
       user.  (See LIMIT Clause [select(7)].)

       The  FOR  UPDATE clause causes the SELECT statement to lock the selected rows against con-
       current updates.

       You must have SELECT privilege to a table to read its values (See the GRANT/REVOKE  state-
       ments).	Use of FOR UPDATE requires UPDATE privilege as well.

   FROM CLAUSE
       The  FROM  clause specifies one or more source tables for the SELECT.  If multiple sources
       are specified, the result is conceptually the Cartesian product of all the rows in all the
       sources	---  but usually qualification conditions are added to restrict the returned rows
       to a small subset of the Cartesian product.

       When a FROM item is a simple table name,  it  implicitly  includes  rows  from  sub-tables
       (inheritance  children)	of the table.  ONLY will suppress rows from sub-tables of the ta-
       ble. Before PostgreSQL 7.1, this was the default result, and adding sub-tables was done by
       appending  *  to  the  table  name.   This  old	behavior is available via the command SET
       SQL_Inheritance TO OFF.

       A FROM item can also be a parenthesized sub-SELECT (note that an alias clause is  required
       for a sub-SELECT!). This is an extremely handy feature since it's the only way to get mul-
       tiple levels of grouping, aggregation, or sorting in a single query.

       A FROM item can be a table function (typically, a  function  that  returns  multiple  rows
       and/or  columns,  though actually any function can be used).  The function is invoked with
       the given argument value(s), and then its output is scanned as though it were a table.

       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 pseudo-type record. When such a function is used in FROM, it  must  be  fol-
       lowed  by  an  alias,  or the keyword AS alone, and then by a parenthesized list of column
       names and types. This provides a query-time composite type definition. The composite  type
       definition  must  match	the actual composite type returned from the function, or an error
       will be reported at run-time.

       Finally, a FROM item can be a JOIN clause, which combines two  simpler  FROM  items.  (Use
       parentheses if necessary to determine the order of nesting.)

       A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same as you get from listing
       the two items at the top level of FROM.	CROSS JOIN is equivalent to INNER JOIN ON (TRUE),
       that is, no rows are removed by qualification. These join types are just a notational con-
       venience, since they do nothing you couldn't do with plain FROM and WHERE.

       LEFT OUTER JOIN returns all rows in the qualified Cartesian product  (i.e.,  all  combined
       rows  that  pass  its  ON condition), plus one copy of each row in the left-hand table for
       which there was no right-hand row that passed the ON  condition.  This  left-hand  row  is
       extended to the full width of the joined table by inserting null values for the right-hand
       columns.  Note that only the JOIN's own ON or USING condition is considered while deciding
       which rows have matches. Outer ON or WHERE conditions are applied afterwards.

       Conversely,  RIGHT OUTER JOIN returns all the joined rows, plus one row for each unmatched
       right-hand row (extended with nulls on the left).  This is just a notational  convenience,
       since you could convert it to a LEFT OUTER JOIN by switching the left and right inputs.

       FULL OUTER JOIN returns all the joined rows, plus one row for each unmatched left-hand row
       (extended with nulls on the right),  plus  one  row  for  each  unmatched  right-hand  row
       (extended with nulls on the left).

       For all the JOIN types except CROSS JOIN, you must write exactly one of ON join_condition,
       USING ( join_column_list ), or NATURAL. ON is the most general case:  you  can  write  any
       qualification  expression involving the two tables to be joined.  A USING column list ( a,
       b, ... ) is shorthand for the ON condition left_table.a = right_table.a AND left_table.b =
       right_table.b  ...   Also,  USING implies that only one of each pair of equivalent columns
       will be included in the JOIN output, not both. NATURAL is shorthand for a USING list  that
       mentions all similarly-named columns in the tables.

   WHERE CLAUSE
       The optional WHERE condition has the general form:

       WHERE boolean_expr

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

	    expr cond_op expr

       or

	    log_op expr

       where cond_op can be one of: =, <, <=, >, >= or <>, a conditional operator like ALL,  ANY,
       IN,  LIKE,  or a locally defined operator, and log_op can be one of: AND, OR, NOT.  SELECT
       will ignore all rows for which the WHERE condition does not return TRUE.

   GROUP BY CLAUSE
       GROUP BY specifies a grouped table derived by the application of this clause:

       GROUP BY expression [, ...]

       GROUP BY will condense into a single row all selected rows that share the same values  for
       the  grouped  columns. Aggregate functions, if any, are computed across all rows making up
       each group, producing a separate value for each group (whereas without GROUP BY, an aggre-
       gate  produces  a  single  value  computed across all the selected rows). When GROUP BY is
       present, it is not valid for the SELECT output expression(s) to refer to ungrouped columns
       except  within  aggregate  functions, since there would be more than one possible value to
       return for an ungrouped column.

       A GROUP BY item can be an input column name, or the name or ordinal number  of  an  output
       column  (SELECT expression), or it can be an arbitrary expression formed from input-column
       values. In case of ambiguity, a GROUP BY name will be interpreted as an input-column  name
       rather than an output column name.

   HAVING CLAUSE
       The optional HAVING condition has the general form:

       HAVING boolean_expr

       where boolean_expr is the same as specified for the WHERE clause.

       HAVING specifies a grouped table derived by the elimination of group rows that do not sat-
       isfy the boolean_expr.  HAVING is different from  WHERE:  WHERE	filters  individual  rows
       before application of GROUP BY, while HAVING filters group rows created by GROUP BY.

       Each  column  referenced  in boolean_expr shall unambiguously reference a grouping column,
       unless the reference appears within an aggregate function.

   ORDER BY CLAUSE
       ORDER BY expression [ ASC | DESC | USING operator ] [, ...]

       An ORDER BY item can be the name or ordinal number of an  output  column  (SELECT  expres-
       sion),  or  it  can be an arbitrary expression formed from input-column values. In case of
       ambiguity, an ORDER BY name will be interpreted as an output-column name.

       The ordinal number refers to the ordinal (left-to-right) position of  the  result  column.
       This  feature  makes  it possible to define an ordering on the basis of a column that does
       not have a unique name.	This is never absolutely necessary because it is always  possible
       to assign a name to a result column using the AS clause, e.g.:

       SELECT title, date_prod + 1 AS newlen FROM films ORDER BY newlen;

       It  is  also possible to ORDER BY arbitrary expressions (an extension to SQL92), including
       fields that do not appear in the SELECT result list.   Thus  the  following  statement  is
       legal:

       SELECT name FROM distributors ORDER BY code;

       A limitation of this feature is that an ORDER BY clause applying to the result of a UNION,
       INTERSECT, or EXCEPT query may only specify an  output  column  name  or  number,  not  an
       expression.

       Note  that if an ORDER BY item is a simple name that matches both a result column name and
       an input column name, ORDER BY will interpret it as the result column name.  This  is  the
       opposite  of  the choice that GROUP BY will make in the same situation. This inconsistency
       is mandated by the SQL92 standard.

       Optionally one may add the key word DESC (descending) or ASC (ascending) after each column
       name in the ORDER BY clause. If not specified, ASC is assumed by default. Alternatively, a
       specific ordering operator name may be specified. ASC is equivalent to USING < and DESC is
       equivalent to USING >.

       The null value sorts higher than any other value in a domain. In other words, with ascend-
       ing sort order nulls sort at the end and with descending sort  order  nulls  sort  at  the
       beginning.

       Data  of  character  types is sorted according to the locale-specific collation order that
       was established when the database cluster was initialized.

   UNION CLAUSE
       table_query UNION [ ALL ] table_query
	   [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
	   [ LIMIT { count | ALL } ]
	   [ OFFSET start ]

       where table_query specifies any select expression without  an  ORDER  BY,  LIMIT,  or  FOR
       UPDATE  clause.	(ORDER BY and LIMIT can be attached to a sub-expression if it is enclosed
       in parentheses. Without parentheses, these clauses will be taken to apply to the result of
       the UNION, not to its right-hand input expression.)

       The UNION operator computes the collection (set union) of the rows returned by the queries
       involved.  The two SELECT statements that represent the direct operands of the UNION  must
       produce	the  same number of columns, and corresponding columns must be of compatible data
       types.

       The result of UNION does not contain any duplicate rows unless the ALL  option  is  speci-
       fied. ALL prevents elimination of duplicates.

       Multiple  UNION operators in the same SELECT statement are evaluated left to right, unless
       otherwise indicated by parentheses.

       Currently, FOR UPDATE may not be specified either for a UNION result or for the inputs  of
       a UNION.

   INTERSECT CLAUSE
       table_query INTERSECT [ ALL ] table_query
	   [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
	   [ LIMIT { count | ALL } ]
	   [ OFFSET start ]

       where  table_query  specifies  any  select  expression  without an ORDER BY, LIMIT, or FOR
       UPDATE clause.

       INTERSECT is similar to UNION, except that it produces only rows that appear in both query
       outputs, rather than rows that appear in either.

       The result of INTERSECT does not contain any duplicate rows unless the ALL option is spec-
       ified. With ALL, a row that has m duplicates in L  and  n  duplicates  in  R  will  appear
       min(m,n) times.

       Multiple  INTERSECT  operators  in  the same SELECT statement are evaluated left to right,
       unless parentheses dictate otherwise.  INTERSECT binds more tightly than  UNION	---  that
       is,  A UNION B INTERSECT C will be read as A UNION (B INTERSECT C) unless otherwise speci-
       fied by parentheses.

   EXCEPT CLAUSE
       table_query EXCEPT [ ALL ] table_query
	   [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
	   [ LIMIT { count | ALL } ]
	   [ OFFSET start ]

       where table_query specifies any select expression without  an  ORDER  BY,  LIMIT,  or  FOR
       UPDATE clause.

       EXCEPT  is  similar  to	UNION,	except that it produces only rows that appear in the left
       query's output but not in the right query's output.

       The result of EXCEPT does not contain any duplicate rows unless the ALL option  is  speci-
       fied.  With ALL, a row that has m duplicates in L and n duplicates in R will appear max(m-
       n,0) times.

       Multiple EXCEPT operators in the same SELECT statement are evaluated left to right, unless
       parentheses dictate otherwise.  EXCEPT binds at the same level as UNION.

   LIMIT CLAUSE
	   LIMIT { count | ALL }
	   OFFSET start

       where count specifies the maximum number of rows to return, and start specifies the number
       of rows to skip before starting to return rows.

       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. If an
       offset is given, that many rows will be skipped before starting to return rows.

       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? You don't know what ordering unless you specify ORDER BY.

       As of PostgreSQL 7.0, 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 use 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.

   FOR UPDATE CLAUSE
	   FOR UPDATE [ OF tablename [, ...] ]

       FOR  UPDATE causes the rows retrieved by the query to be locked as though for update. This
       prevents them from being modified or deleted  by  other	transactions  until  the  current
       transaction  ends;  that is, other transactions that attempt UPDATE, DELETE, or SELECT FOR
       UPDATE of these rows will be blocked until the  current	transaction  ends.  Also,  if  an
       UPDATE,	DELETE,  or  SELECT  FOR  UPDATE  from	another  transaction has already locked a
       selected row or rows, SELECT FOR UPDATE will wait for the other transaction  to	complete,
       and  will  then	lock  and return the updated row (or no row, if the row was deleted). For
       further discussion see the concurrency chapter of the User's Guide.

       If specific tables are named in FOR UPDATE, then only rows coming from  those  tables  are
       locked; any other tables used in the SELECT are simply read as usual.

       FOR UPDATE cannot be used in contexts where returned rows can't be clearly identified with
       individual table rows; for example it can't be used with aggregation.

       FOR UPDATE may appear before LIMIT for compatibility with pre-7.3  applications.  However,
       it effectively executes after LIMIT, and so that is the recommended place to write it.

USAGE
       To join the table films with the table distributors:

       SELECT f.title, f.did, d.name, f.date_prod, f.kind
	   FROM distributors d, films f
	   WHERE f.did = d.did

		  title 	  | did |   name	   | date_prod	| kind
       ---------------------------+-----+------------------+------------+----------
	The Third Man		  | 101 | British Lion	   | 1949-12-23 | Drama
	The African Queen	  | 101 | British Lion	   | 1951-08-11 | Romantic
	Une Femme est une Femme   | 102 | Jean Luc Godard  | 1961-03-12 | Romantic
	Vertigo 		  | 103 | Paramount	   | 1958-11-14 | Action
	Becket			  | 103 | Paramount	   | 1964-02-03 | Drama
	48 Hrs			  | 103 | Paramount	   | 1982-10-22 | Action
	War and Peace		  | 104 | Mosfilm	   | 1967-02-12 | Drama
	West Side Story 	  | 105 | United Artists   | 1961-01-03 | Musical
	Bananas 		  | 105 | United Artists   | 1971-07-13 | Comedy
	Yojimbo 		  | 106 | Toho		   | 1961-06-16 | Drama
	There's a Girl in my Soup | 107 | Columbia	   | 1970-06-11 | Comedy
	Taxi Driver		  | 107 | Columbia	   | 1975-05-15 | Action
	Absence of Malice	  | 107 | Columbia	   | 1981-11-15 | Action
	Storia di una donna	  | 108 | Westward	   | 1970-08-15 | Romantic
	The King and I		  | 109 | 20th Century Fox | 1956-08-11 | Musical
	Das Boot		  | 110 | Bavaria Atelier  | 1981-11-11 | Drama
	Bed Knobs and Broomsticks | 111 | Walt Disney	   |		| Musical
       (17 rows)

       To sum the column len of all films and group the results by kind:

       SELECT kind, SUM(len) AS total FROM films GROUP BY kind;

	  kind	 | total
       ----------+-------
	Action	 | 07:34
	Comedy	 | 02:58
	Drama	 | 14:28
	Musical  | 06:42
	Romantic | 04:38
       (5 rows)

       To  sum the column len of all films, group the results by kind and show those group totals
       that are less than 5 hours:

       SELECT kind, SUM(len) AS total
	   FROM films
	   GROUP BY kind
	   HAVING SUM(len) < INTERVAL '5 hour';

	kind	 | total
       ----------+-------
	Comedy	 | 02:58
	Romantic | 04:38
       (2 rows)

       The following two examples are identical ways of sorting the individual results	according
       to the contents of the second column (name):

       SELECT * FROM distributors ORDER BY name;
       SELECT * FROM distributors ORDER BY 2;

	did |	    name
       -----+------------------
	109 | 20th Century Fox
	110 | Bavaria Atelier
	101 | British Lion
	107 | Columbia
	102 | Jean Luc Godard
	113 | Luso films
	104 | Mosfilm
	103 | Paramount
	106 | Toho
	105 | United Artists
	111 | Walt Disney
	112 | Warner Bros.
	108 | Westward
       (13 rows)

       This  example  shows  how  to  obtain  the  union  of  the tables distributors and actors,
       restricting the results to those that begin with letter W in  each  table.  Only  distinct
       rows are wanted, so the ALL keyword is omitted:

       distributors:		   actors:
	did |	  name		    id |     name
       -----+--------------	   ----+----------------
	108 | Westward		     1 | Woody Allen
	111 | Walt Disney	     2 | Warren Beatty
	112 | Warner Bros.	     3 | Walter Matthau
	...			    ...

       SELECT distributors.name
	   FROM   distributors
	   WHERE  distributors.name LIKE 'W%'
       UNION
       SELECT actors.name
	   FROM   actors
	   WHERE  actors.name LIKE 'W%';

	     name
       ----------------
	Walt Disney
	Walter Matthau
	Warner Bros.
	Warren Beatty
	Westward
	Woody Allen

       This  example shows how to use a table function, both with and without a column definition
       list.

       distributors:
	did |	  name
       -----+--------------
	108 | Westward
	111 | Walt Disney
	112 | Warner Bros.
	...

       CREATE FUNCTION distributors(int)
	 RETURNS SETOF distributors AS '
	 SELECT * FROM distributors WHERE did = $1;
	 ' LANGUAGE SQL;

       SELECT * FROM distributors(111);
	did |	 name
       -----+-------------
	111 | Walt Disney
       (1 row)

       CREATE FUNCTION distributors_2(int)
	 RETURNS SETOF RECORD AS '
	 SELECT * FROM distributors WHERE did = $1;
	 ' LANGUAGE SQL;

       SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
	f1  |	  f2
       -----+-------------
	111 | Walt Disney
       (1 row)

COMPATIBILITY
   EXTENSIONS
       PostgreSQL allows one to omit the FROM clause from a query. This feature was retained from
       the  original PostQUEL query language. It has a straightforward use to compute the results
       of simple expressions:

       SELECT 2+2;

	?column?
       ----------
	       4

       Some other SQL databases cannot do this except by introducing a dummy one-row table to  do
       the  select  from.  A  less  obvious use is to abbreviate a normal select from one or more
       tables:

       SELECT distributors.* WHERE distributors.name = 'Westward';

	did | name
       -----+----------
	108 | Westward

       This works because an implicit FROM item is added for each table that is referenced in the
       query  but  not mentioned in FROM. While this is a convenient shorthand, it's easy to mis-
       use. For example, the query

       SELECT distributors.* FROM distributors d;

       is probably a mistake; most likely the user meant

       SELECT d.* FROM distributors d;

       rather than the unconstrained join

       SELECT distributors.* FROM distributors d, distributors distributors;

       that he will actually get. To help detect this sort of mistake, PostgreSQL 7.1  and  later
       will  warn  if the implicit-FROM feature is used in a query that also contains an explicit
       FROM clause.

       The table-function feature is a PostgreSQL extension.

   SQL92
   SELECT CLAUSE
       In the SQL92 standard, the optional keyword AS is just noise and can  be  omitted  without
       affecting  the  meaning.  The PostgreSQL parser requires this keyword when renaming output
       columns because the type extensibility features lead to parsing ambiguities in  this  con-
       text. AS is optional in FROM items, however.

       The DISTINCT ON phrase is not part of SQL92.  Nor are LIMIT and OFFSET.

       In SQL92, an ORDER BY clause may only use result column names or numbers, while a GROUP BY
       clause may only use input column names.	PostgreSQL extends each of these clauses to allow
       the  other  choice  as well (but it uses the standard's interpretation if there is ambigu-
       ity).  PostgreSQL also allows both clauses to specify  arbitrary  expressions.  Note  that
       names  appearing  in  an  expression  will  always  be taken as input-column names, not as
       result-column names.

   UNION/INTERSECT/EXCEPT CLAUSE
       The SQL92 syntax for UNION/INTERSECT/EXCEPT allows an additional CORRESPONDING BY option:

       table_query UNION [ALL]
	   [CORRESPONDING [BY (column [,...])]]
	   table_query

       The CORRESPONDING BY clause is not supported by PostgreSQL.

SQL - Language Statements		    2002-11-22					SELECT(7)
Unix & Linux Commands & Man Pages : ©2000 - 2018 Unix and Linux Forums


All times are GMT -4. The time now is 07:50 AM.