query.sgml

PostgreSQL7.4.6 for Linux

       because there is no matching entry in the
       <classname>cities</classname> table for Hayward, so the join
       ignores the unmatched rows in the weather table.  We will see
       shortly how this can be fixed.
      </para>
     </listitem>

     <listitem>
      <para>
       There are two columns containing the city name.  This is
       correct because the lists of columns of the
       <classname>weather</classname> and the
       <classname>cities</classname> table are concatenated.  In
       practice this is undesirable, though, so you will probably want
       to list the output columns explicitly rather than using
       <literal>*</literal>:
<programlisting>
SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather, cities
    WHERE city = name;
</programlisting>
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <formalpara>
    <title>Exercise:</title>

    <para>
     Attempt to find out the semantics of this query when the
     <literal>WHERE</literal> clause is omitted.
    </para>
   </formalpara>

   <para>
    Since the columns all had different names, the parser
    automatically found out which table they belong to, but it is good
    style to fully qualify column names in join queries:

<programlisting>
SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;
</programlisting>
   </para>

   <para>
    Join queries of the kind seen thus far can also be written in this
    alternative form:

<programlisting>
SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);
</programlisting>

    This syntax is not as commonly used as the one above, but we show
    it here to help you understand the following topics.
   </para>

   <para>
    <indexterm><primary>join</primary><secondary>outer</secondary></indexterm>

    Now we will figure out how we can get the Hayward records back in.
    What we want the query to do is to scan the
    <classname>weather</classname> table and for each row to find the
    matching <classname>cities</classname> row.  If no matching row is
    found we want some <quote>empty values</quote> to be substituted
    for the <classname>cities</classname> table's columns.  This kind
    of query is called an <firstterm>outer join</firstterm>.  (The
    joins we have seen so far are inner joins.)  The command looks
    like this:

<programlisting>
SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)
</programlisting>

    This query is called a <firstterm>left outer
    join</firstterm> because the table mentioned on the left of the
    join operator will have each of its rows in the output at least
    once, whereas the table on the right will only have those rows
    output that match some row of the left table.  When outputting a
    left-table row for which there is no right-table match, empty (null)
    values are substituted for the right-table columns.
   </para>

   <formalpara>
    <title>Exercise:</title>

    <para>
     There are also right outer joins and full outer joins.  Try to
     find out what those do.
    </para>
   </formalpara>

   <para>
    <indexterm><primary>join</primary><secondary>self</secondary></indexterm>
    <indexterm><primary>alias</primary><secondary>for table name in query</secondary></indexterm>

    We can also join a table against itself.  This is called a
    <firstterm>self join</firstterm>.  As an example, suppose we wish
    to find all the weather records that are in the temperature range
    of other weather records.  So we need to compare the
    <structfield>temp_lo</> and <structfield>temp_hi</> columns of
    each <classname>weather</classname> row to the
    <structfield>temp_lo</structfield> and
    <structfield>temp_hi</structfield> columns of all other
    <classname>weather</classname> rows.  We can do this with the
    following query:

<programlisting>
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;

     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)
</programlisting>     

    Here we have relabeled the weather table as <literal>W1</> and
    <literal>W2</> to be able to distinguish the left and right side
    of the join.  You can also use these kinds of aliases in other
    queries to save some typing, e.g.:
<programlisting>
SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;
</programlisting>
    You will encounter this style of abbreviating quite frequently.
   </para>
  </sect1>


  <sect1 id="tutorial-agg">
   <title>Aggregate Functions</title>

   <indexterm zone="tutorial-agg">
    <primary>aggregate function</primary>
   </indexterm>

   <para>
    <indexterm><primary>average</primary></indexterm>
    <indexterm><primary>count</primary></indexterm>
    <indexterm><primary>max</primary></indexterm>
    <indexterm><primary>min</primary></indexterm>
    <indexterm><primary>sum</primary></indexterm>

    Like  most  other relational database products, 
    <productname>PostgreSQL</productname> supports
    aggregate functions.
    An aggregate function computes a single result from multiple input rows.
    For example, there are aggregates to compute the
    <function>count</function>, <function>sum</function>,
    <function>avg</function> (average), <function>max</function> (maximum) and
    <function>min</function> (minimum) over a set of rows.
   </para>

   <para>
    As an example, we can find the highest low-temperature reading anywhere
    with

<programlisting>
SELECT max(temp_lo) FROM weather;
</programlisting>

<screen>
 max
-----
  46
(1 row)
</screen>
   </para>

   <para>
    <indexterm><primary>subquery</primary></indexterm>

    If we wanted to know what city (or cities) that reading occurred in,
    we might try

<programlisting>
SELECT city FROM weather WHERE temp_lo = max(temp_lo);     <lineannotation>WRONG</lineannotation>
</programlisting>

    but this will not work since the aggregate
    <function>max</function> cannot be used in the
    <literal>WHERE</literal> clause.  (This restriction exists because
    the <literal>WHERE</literal> clause determines the rows that will
    go into the aggregation stage; so it has to be evaluated before
    aggregate functions are computed.)
    However, as is often the case
    the query can be restated to accomplish the intended result, here
    by using a <firstterm>subquery</firstterm>:

<programlisting>
SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
</programlisting>

<screen>
     city
---------------
 San Francisco
(1 row)
</screen>

    This is OK because the subquery is an independent computation
    that computes its own aggregate separately from what is happening
    in the outer query.
   </para>

   <para>
    <indexterm><primary>GROUP BY</primary></indexterm>
    <indexterm><primary>HAVING</primary></indexterm>

    Aggregates are also very useful in combination with <literal>GROUP
    BY</literal> clauses.  For example, we can get the maximum low
    temperature observed in each city with

<programlisting>
SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;
</programlisting>

<screen>
     city      | max
---------------+-----
 Hayward       |  37
 San Francisco |  46
(2 rows)
</screen>

    which gives us one output row per city.  Each aggregate result is
    computed over the table rows matching that city.
    We can filter these grouped
    rows using <literal>HAVING</literal>:

<programlisting>
SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;
</programlisting>

<screen>
  city   | max
---------+-----
 Hayward |  37
(1 row)
</screen>

    which gives us the same results for only the cities that have all
    <literal>temp_lo</> values below 40.  Finally, if we only care about
    cities whose
    names begin with <quote><literal>S</literal></quote>, we might do

<programlisting>
SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'<co id="co.tutorial-agg-like">
    GROUP BY city
    HAVING max(temp_lo) < 40;
</programlisting>
   <calloutlist>
    <callout arearefs="co.tutorial-agg-like">
     <para>
      The <literal>LIKE</literal> operator does pattern matching and
      is explained in <xref linkend="functions-matching">.
     </para>
    </callout>
   </calloutlist>
   </para>

   <para>
    It is important to understand the interaction between aggregates and
    <acronym>SQL</acronym>'s <literal>WHERE</literal> and <literal>HAVING</literal> clauses.
    The fundamental difference between <literal>WHERE</literal> and
    <literal>HAVING</literal> is this: <literal>WHERE</literal> selects
    input rows before groups and aggregates are computed (thus, it controls
    which rows go into the aggregate computation), whereas
    <literal>HAVING</literal> selects group rows after groups and
    aggregates are computed.  Thus, the
    <literal>WHERE</literal> clause must not contain aggregate functions;
    it makes no sense to try to use an aggregate to determine which rows
    will be inputs to the aggregates.  On the other hand,
    <literal>HAVING</literal> clause always contains aggregate functions.
    (Strictly speaking, you are allowed to write a <literal>HAVING</literal>
    clause that doesn't use aggregates, but it's wasteful: The same condition
    could be used more efficiently at the <literal>WHERE</literal> stage.)
   </para>

   <para>
    Observe that we can apply the city name restriction in
    <literal>WHERE</literal>, since it needs no aggregate.  This is
    more efficient than adding the restriction to <literal>HAVING</literal>,
    because we avoid doing the grouping and aggregate calculations
    for all rows that fail the <literal>WHERE</literal> check.
   </para>
  </sect1>


  <sect1 id="tutorial-update">
   <title>Updates</title>

   <indexterm zone="tutorial-update">
    <primary>UPDATE</primary>
   </indexterm>

   <para>
    You can update existing rows using the
    <command>UPDATE</command> command. 
    Suppose you discover the temperature readings are
    all  off  by 2 degrees as of November 28.  You may update the
    data as follows:

<programlisting>
UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';
</programlisting>
   </para>

   <para>
    Look at the new state of the data:
<programlisting>
SELECT * FROM weather;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      41 |      55 |    0 | 1994-11-29
 Hayward       |      35 |      52 |      | 1994-11-29
(3 rows)
</programlisting>
   </para>
  </sect1>

  <sect1 id="tutorial-delete">
   <title>Deletions</title>

   <indexterm zone="tutorial-delete">
    <primary>DELETE</primary>
   </indexterm>

   <para>
    Suppose you are no longer interested in the weather of Hayward.
    Then you can do the following to delete those rows from the table.
    Deletions are performed using the <command>DELETE</command>
    command:
<programlisting>
DELETE FROM weather WHERE city = 'Hayward';
</programlisting>

    All weather records belonging to Hayward are removed.

<programlisting>
SELECT * FROM weather;
</programlisting>

<screen>
     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      41 |      55 |    0 | 1994-11-29
(2 rows)
</screen>
   </para>

   <para>
    One should be wary of statements of the form
<synopsis>
DELETE FROM <replaceable>tablename</replaceable>;
</synopsis>

    Without a qualification, <command>DELETE</command> will
    remove  <emphasis>all</>  rows from the given table, leaving it
    empty.  The system will not request confirmation before
    doing this!
   </para>
  </sect1>

 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->