File: tutorial-select.html

package info (click to toggle)
postgresql-9.1 9.1.15-0%2Bdeb8u1
links: PTS, VCS
area: main
in suites: jessie-kfreebsd
size: 109,092 kB
sloc: ansic: 575,877; sql: 43,887; yacc: 26,399; perl: 6,352; lex: 6,171; sh: 5,282; makefile: 3,772; asm: 65; sed: 15; python: 12
file content (452 lines) | stat: -rw-r--r-- 8,033 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Querying a Table</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.15 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="The SQL Language"
HREF="tutorial-sql.html"><LINK
REL="PREVIOUS"
TITLE="Populating a Table With Rows"
HREF="tutorial-populate.html"><LINK
REL="NEXT"
TITLE="Joins Between Tables"
HREF="tutorial-join.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2015-02-02T21:03:01"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.15 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Populating a Table With Rows"
HREF="tutorial-populate.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="tutorial-sql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 2. The <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Language</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Joins Between Tables"
HREF="tutorial-join.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="TUTORIAL-SELECT"
>2.5. Querying a Table</A
></H1
><P
>    
    

    To retrieve data from a table, the table is
    <I
CLASS="FIRSTTERM"
>queried</I
>.  An <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
>
    <TT
CLASS="COMMAND"
>SELECT</TT
> statement is used to do this.  The
    statement is divided into a select list (the part that lists the
    columns to be returned), a table list (the part that lists the
    tables from which to retrieve the data), and an optional
    qualification (the part that specifies any restrictions).  For
    example, to retrieve all the rows of table
    <TT
CLASS="STRUCTNAME"
>weather</TT
>, type:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM weather;</PRE
><P>
    Here <TT
CLASS="LITERAL"
>*</TT
> is a shorthand for <SPAN
CLASS="QUOTE"
>"all columns"</SPAN
>.
     <A
NAME="AEN721"
HREF="#FTN.AEN721"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
>
    So the same result would be had with:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT city, temp_lo, temp_hi, prcp, date FROM weather;</PRE
><P>

    The output should be:

</P><PRE
CLASS="SCREEN"
>     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)</PRE
><P>
   </P
><P
>    You can write expressions, not just simple column references, in the
    select list.  For example, you can do:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;</PRE
><P>
    This should give:
</P><PRE
CLASS="SCREEN"
>     city      | temp_avg |    date
---------------+----------+------------
 San Francisco |       48 | 1994-11-27
 San Francisco |       50 | 1994-11-29
 Hayward       |       45 | 1994-11-29
(3 rows)</PRE
><P>
    Notice how the <TT
CLASS="LITERAL"
>AS</TT
> clause is used to relabel the
    output column.  (The <TT
CLASS="LITERAL"
>AS</TT
> clause is optional.)
   </P
><P
>    A query can be <SPAN
CLASS="QUOTE"
>"qualified"</SPAN
> by adding a <TT
CLASS="LITERAL"
>WHERE</TT
>
    clause that specifies which rows are wanted.  The <TT
CLASS="LITERAL"
>WHERE</TT
>
    clause contains a Boolean (truth value) expression, and only rows for
    which the Boolean expression is true are returned.  The usual
    Boolean operators (<TT
CLASS="LITERAL"
>AND</TT
>,
    <TT
CLASS="LITERAL"
>OR</TT
>, and <TT
CLASS="LITERAL"
>NOT</TT
>) are allowed in
    the qualification.  For example, the following
    retrieves the weather of San Francisco on rainy days:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp &gt; 0.0;</PRE
><P>
    Result:
</P><PRE
CLASS="SCREEN"
>     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)</PRE
><P>
   </P
><P
>    

    You can request that the results of a query
    be returned in sorted order:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM weather
    ORDER BY city;</PRE
><P>

</P><PRE
CLASS="SCREEN"
>     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 Hayward       |      37 |      54 |      | 1994-11-29
 San Francisco |      43 |      57 |    0 | 1994-11-29
 San Francisco |      46 |      50 | 0.25 | 1994-11-27</PRE
><P>

    In this example, the sort order isn't fully specified, and so you
    might get the San Francisco rows in either order.  But you'd always
    get the results shown above if you do:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM weather
    ORDER BY city, temp_lo;</PRE
><P>
   </P
><P
>    
    

    You can request that duplicate rows be removed from the result of
    a query:

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT DISTINCT city
    FROM weather;</PRE
><P>

</P><PRE
CLASS="SCREEN"
>     city
---------------
 Hayward
 San Francisco
(2 rows)</PRE
><P>

    Here again, the result row ordering might vary.
    You can ensure consistent results by using <TT
CLASS="LITERAL"
>DISTINCT</TT
> and
    <TT
CLASS="LITERAL"
>ORDER BY</TT
> together:
     <A
NAME="AEN755"
HREF="#FTN.AEN755"
><SPAN
CLASS="footnote"
>[2]</SPAN
></A
>

</P><PRE
CLASS="PROGRAMLISTING"
>SELECT DISTINCT city
    FROM weather
    ORDER BY city;</PRE
><P>
   </P
></DIV
><H3
CLASS="FOOTNOTES"
>Notes</H3
><TABLE
BORDER="0"
CLASS="FOOTNOTES"
WIDTH="100%"
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN721"
HREF="tutorial-select.html#AEN721"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>       While <TT
CLASS="LITERAL"
>SELECT *</TT
> is useful for off-the-cuff
       queries, it is widely considered bad style in production code,
       since adding a column to the table would change the results.
      </P
></TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN755"
HREF="tutorial-select.html#AEN755"
><SPAN
CLASS="footnote"
>[2]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>       In some database systems, including older versions of
       <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>, the implementation of
       <TT
CLASS="LITERAL"
>DISTINCT</TT
> automatically orders the rows and
       so <TT
CLASS="LITERAL"
>ORDER BY</TT
> is unnecessary.  But this is not
       required by the SQL standard, and current
       <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> does not guarantee that
       <TT
CLASS="LITERAL"
>DISTINCT</TT
> causes the rows to be ordered.
      </P
></TD
></TR
></TABLE
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="tutorial-populate.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="tutorial-join.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Populating a Table With Rows</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="tutorial-sql.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Joins Between Tables</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>