<!-- doc/src/sgml/queries.sgml -->

<chapter id="queries">
 <title>Queries</title>

 <indexterm zone="queries">
  <primary>query</primary>
 </indexterm>

 <indexterm zone="queries">
  <primary>SELECT</primary>
 </indexterm>

 <para>
  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 from the database.
 </para>


 <sect1 id="queries-overview">
  <title>Overview</title>

  <para>
   The process of retrieving or the command to retrieve data from a
   database is called a <firstterm>query</firstterm>.  In SQL the
   <link linkend="sql-select"><command>SELECT</command></link> command is
   used to specify queries.  The general syntax of the
   <command>SELECT</command> command is
<synopsis>
<optional>WITH <replaceable>with_queries</replaceable></optional> SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable> <optional><replaceable>sort_specification</replaceable></optional>
</synopsis>
   The following sections describe the details of the select list, the
   table expression, and the sort specification.  <literal>WITH</literal>
   queries are treated last since they are an advanced feature.
  </para>

  <para>
   A simple kind of query has the form:
<programlisting>
SELECT * FROM table1;
</programlisting>
  Assuming that there is a table called <literal>table1</literal>,
  this command would retrieve all rows and all user-defined columns from
  <literal>table1</literal>.  (The method of retrieval depends on the
  client application.  For example, the
  <application>psql</application> 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 <literal>*</literal> 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
  <literal>table1</literal> has columns named <literal>a</literal>,
  <literal>b</literal>, and <literal>c</literal> (and perhaps others) you can make
  the following query:
<programlisting>
SELECT a, b + c FROM table1;
</programlisting>
  (assuming that <literal>b</literal> and <literal>c</literal> are of a numerical
  data type).
  See <xref linkend="queries-select-lists"/> for more details.
 </para>

 <para>
  <literal>FROM table1</literal> is a 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 <command>SELECT</command> command as a calculator:
<programlisting>
SELECT 3 * 4;
</programlisting>
  This is more useful if the expressions in the select list return
  varying results.  For example, you could call a function this way:
<programlisting>
SELECT random();
</programlisting>
  </para>
 </sect1>


 <sect1 id="queries-table-expressions">
  <title>Table Expressions</title>

  <indexterm zone="queries-table-expressions">
   <primary>table expression</primary>
  </indexterm>

  <para>
   A <firstterm>table expression</firstterm> computes a table.  The
   table expression contains a <literal>FROM</literal> clause that is
   optionally followed by <literal>WHERE</literal>, <literal>GROUP BY</literal>, and
   <literal>HAVING</literal> 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.
  </para>

  <para>
   The optional <literal>WHERE</literal>, <literal>GROUP BY</literal>, and
   <literal>HAVING</literal> clauses in the table expression specify a
   pipeline of successive transformations performed on the table
   derived in the <literal>FROM</literal> clause.  All these transformations
   produce