begriffs open source - ai-pg/blob - full-docs/src/sgml/html/tutorial-join.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>2.6. Joins Between Tables</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="tutorial-select.html" title="2.5. Querying a Table" /><link rel="next" href="tutorial-agg.html" title="2.7. Aggregate Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">2.6. Joins Between Tables</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="tutorial-select.html" title="2.5. Querying a Table">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="tutorial-sql.html" title="Chapter 2. The SQL Language">Up</a></td><th width="60%" align="center">Chapter 2. The <acronym class="acronym">SQL</acronym> Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="tutorial-agg.html" title="2.7. Aggregate Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="TUTORIAL-JOIN"><div class="titlepage"><div><div><h2 class="title" style="clear: both">2.6. Joins Between Tables <a href="#TUTORIAL-JOIN" class="id_link">#</a></h2></div></div></div><a id="id-1.4.4.7.2" class="indexterm"></a><p>
   3     Thus far, our queries have only accessed one table at a time.
   4     Queries can access multiple tables at once, or access the same
   5     table in such a way that multiple rows of the table are being
   6     processed at the same time.  Queries that access multiple tables
   7     (or multiple instances of the same table) at one time are called
   8     <em class="firstterm">join</em> queries.  They combine rows from one table
   9     with rows from a second table, with an expression specifying which rows
  10     are to be paired.  For example, to return all the weather records together
  11     with the location of the associated city, the database needs to compare
  12     the <code class="structfield">city</code>
  13     column of each row of the <code class="structname">weather</code> table with the
  14     <code class="structfield">name</code> column of all rows in the <code class="structname">cities</code>
  15     table, and select the pairs of rows where these values match.<a href="#ftn.id-1.4.4.7.3.6" class="footnote"><sup class="footnote" id="id-1.4.4.7.3.6">[4]</sup></a>
  16     This would be accomplished by the following query:
  17
  18 </p><pre class="programlisting">
  19 SELECT * FROM weather JOIN cities ON city = name;
  20 </pre><p>
  21
  22 </p><pre class="screen">
  23      city      | temp_lo | temp_hi | prcp |    date    |     name      | location
  24 ---------------+---------+---------+------+------------+---------------+-----------
  25  San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
  26  San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
  27 (2 rows)
  28 </pre><p>
  29
  30    </p><p>
  31     Observe two things about the result set:
  32     </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
  33        There is no result row for the city of Hayward.  This is
  34        because there is no matching entry in the
  35        <code class="structname">cities</code> table for Hayward, so the join
  36        ignores the unmatched rows in the <code class="structname">weather</code> table.  We will see
  37        shortly how this can be fixed.
  38       </p></li><li class="listitem"><p>
  39        There are two columns containing the city name.  This is
  40        correct because the lists of columns from the
  41        <code class="structname">weather</code> and
  42        <code class="structname">cities</code> tables are concatenated.  In
  43        practice this is undesirable, though, so you will probably want
  44        to list the output columns explicitly rather than using
  45        <code class="literal">*</code>:
  46 </p><pre class="programlisting">
  47 SELECT city, temp_lo, temp_hi, prcp, date, location
  48     FROM weather JOIN cities ON city = name;
  49 </pre><p>
  50       </p></li></ul></div><p>
  51    </p><p>
  52     Since the columns all had different names, the parser
  53     automatically found which table they belong to.  If there
  54     were duplicate column names in the two tables you'd need to
  55     <em class="firstterm">qualify</em> the column names to show which one you
  56     meant, as in:
  57
  58 </p><pre class="programlisting">
  59 SELECT weather.city, weather.temp_lo, weather.temp_hi,
  60        weather.prcp, weather.date, cities.location
  61     FROM weather JOIN cities ON weather.city = cities.name;
  62 </pre><p>
  63
  64     It is widely considered good style to qualify all column names
  65     in a join query, so that the query won't fail if a duplicate
  66     column name is later added to one of the tables.
  67    </p><p>
  68     Join queries of the kind seen thus far can also be written in this
  69     form:
  70
  71 </p><pre class="programlisting">
  72 SELECT *
  73     FROM weather, cities
  74     WHERE city = name;
  75 </pre><p>
  76
  77     This syntax pre-dates the <code class="literal">JOIN</code>/<code class="literal">ON</code>
  78     syntax, which was introduced in SQL-92.  The tables are simply listed in
  79     the <code class="literal">FROM</code> clause, and the comparison expression is added
  80     to the <code class="literal">WHERE</code> clause.  The results from this older
  81     implicit syntax and the newer explicit
  82     <code class="literal">JOIN</code>/<code class="literal">ON</code> syntax are identical.  But
  83     for a reader of the query, the explicit syntax makes its meaning easier to
  84     understand: The join condition is introduced by its own key word whereas
  85     previously the condition was mixed into the <code class="literal">WHERE</code>
  86     clause together with other conditions.
  87    </p><a id="id-1.4.4.7.7" class="indexterm"></a><p>
  88     Now we will figure out how we can get the Hayward records back in.
  89     What we want the query to do is to scan the
  90     <code class="structname">weather</code> table and for each row to find the
  91     matching <code class="structname">cities</code> row(s).  If no matching row is
  92     found we want some <span class="quote">“<span class="quote">empty values</span>”</span> to be substituted
  93     for the <code class="structname">cities</code> table's columns.  This kind
  94     of query is called an <em class="firstterm">outer join</em>.  (The
  95     joins we have seen so far are <em class="firstterm">inner joins</em>.)
  96     The command looks like this:
  97
  98 </p><pre class="programlisting">
  99 SELECT *
 100     FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;
 101 </pre><p>
 102
 103 </p><pre class="screen">
 104      city      | temp_lo | temp_hi | prcp |    date    |     name      | location
 105 ---------------+---------+---------+------+------------+---------------+-----------
 106  Hayward       |      37 |      54 |      | 1994-11-29 |               |
 107  San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 108  San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
 109 (3 rows)
 110 </pre><p>
 111
 112     This query is called a <em class="firstterm">left outer
 113     join</em> because the table mentioned on the left of the
 114     join operator will have each of its rows in the output at least
 115     once, whereas the table on the right will only have those rows
 116     output that match some row of the left table.  When outputting a
 117     left-table row for which there is no right-table match, empty (null)
 118     values are substituted for the right-table columns.
 119    </p><p><strong>Exercise: </strong>
 120      There are also right outer joins and full outer joins.  Try to
 121      find out what those do.
 122     </p><a id="id-1.4.4.7.10" class="indexterm"></a><a id="id-1.4.4.7.11" class="indexterm"></a><p>
 123     We can also join a table against itself.  This is called a
 124     <em class="firstterm">self join</em>.  As an example, suppose we wish
 125     to find all the weather records that are in the temperature range
 126     of other weather records.  So we need to compare the
 127     <code class="structfield">temp_lo</code> and <code class="structfield">temp_hi</code> columns of
 128     each <code class="structname">weather</code> row to the
 129     <code class="structfield">temp_lo</code> and
 130     <code class="structfield">temp_hi</code> columns of all other
 131     <code class="structname">weather</code> rows.  We can do this with the
 132     following query:
 133
 134 </p><pre class="programlisting">
 135 SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
 136        w2.city, w2.temp_lo AS low, w2.temp_hi AS high
 137     FROM weather w1 JOIN weather w2
 138         ON w1.temp_lo &lt; w2.temp_lo AND w1.temp_hi &gt; w2.temp_hi;
 139 </pre><p>
 140
 141 </p><pre class="screen">
 142      city      | low | high |     city      | low | high
 143 ---------------+-----+------+---------------+-----+------
 144  San Francisco |  43 |   57 | San Francisco |  46 |   50
 145  Hayward       |  37 |   54 | San Francisco |  46 |   50
 146 (2 rows)
 147 </pre><p>
 148
 149     Here we have relabeled the weather table as <code class="literal">w1</code> and
 150     <code class="literal">w2</code> to be able to distinguish the left and right side
 151     of the join.  You can also use these kinds of aliases in other
 152     queries to save some typing, e.g.:
 153 </p><pre class="programlisting">
 154 SELECT *
 155     FROM weather w JOIN cities c ON w.city = c.name;
 156 </pre><p>
 157     You will encounter this style of abbreviating quite frequently.
 158    </p><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.id-1.4.4.7.3.6" class="footnote"><p><a href="#id-1.4.4.7.3.6" class="para"><sup class="para">[4] </sup></a>
 159       This  is only a conceptual model.  The join is usually performed
 160       in a more efficient manner than actually comparing each possible
 161       pair of rows, but this is invisible to the user.
 162      </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="tutorial-select.html" title="2.5. Querying a Table">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="tutorial-sql.html" title="Chapter 2. The SQL Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="tutorial-agg.html" title="2.7. Aggregate Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">2.5. Querying a Table </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 2.7. Aggregate Functions</td></tr></table></div></body></html>