1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>2.6.Joins Between Tables</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="tutorial-sql.html" title="Chapter2.The SQL Language">
<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">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="tutorial-join"></a>2.6.Joins Between Tables</h2></div></div></div>
<a name="id568731"></a><p> Thus far, our queries have only accessed one table at a time.
Queries can access multiple tables at once, or access the same
table in such a way that multiple rows of the table are being
processed at the same time. A query that accesses multiple rows
of the same or different tables at one time is called a
<em class="firstterm">join</em> query. As an example, say you wish to
list all the weather records together with the location of the
associated city. To do that, we need to compare the city column of
each row of the weather table with the name column of all rows in
the cities table, and select the pairs of rows where these values match.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> This is only a conceptual model. The join is usually performed
in a more efficient manner than actually comparing each possible
pair of rows, but this is invisible to the user.
</p>
</div>
<p>
This would be accomplished by the following query:
</p>
<pre class="programlisting">SELECT *
FROM weather, cities
WHERE city = name;</pre>
<p>
</p>
<pre class="screen"> city | temp_lo | temp_hi | prcp | date | name | location
---------------+---------+---------+------+------------+---------------+-----------
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)
(2 rows)</pre>
<p>
</p>
<p> Observe two things about the result set:
</p>
<div class="itemizedlist"><ul type="disc">
<li><p> There is no result row for the city of Hayward. This is
because there is no matching entry in the
<code class="classname">cities</code> table for Hayward, so the join
ignores the unmatched rows in the weather table. We will see
shortly how this can be fixed.
</p></li>
<li>
<p> There are two columns containing the city name. This is
correct because the lists of columns of the
<code class="classname">weather</code> and the
<code class="classname">cities</code> table are concatenated. In
practice this is undesirable, though, so you will probably want
to list the output columns explicitly rather than using
<code class="literal">*</code>:
</p>
<pre class="programlisting">SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;</pre>
<p>
</p>
</li>
</ul></div>
<p>
</p>
<p><b>Exercise:</b> Attempt to find out the semantics of this query when the
<code class="literal">WHERE</code> clause is omitted.
</p>
<p> 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:
</p>
<pre class="programlisting">SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;</pre>
<p>
</p>
<p> Join queries of the kind seen thus far can also be written in this
alternative form:
</p>
<pre class="programlisting">SELECT *
FROM weather INNER JOIN cities ON (weather.city = cities.name);</pre>
<p>
This syntax is not as commonly used as the one above, but we show
it here to help you understand the following topics.
</p>
<p> <a name="id568878"></a>
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
<code class="classname">weather</code> table and for each row to find the
matching <code class="classname">cities</code> row. If no matching row is
found we want some “<span class="quote">empty values</span>” to be substituted
for the <code class="classname">cities</code> table's columns. This kind
of query is called an <em class="firstterm">outer join</em>. (The
joins we have seen so far are inner joins.) The command looks
like this:
</p>
<pre class="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)</pre>
<p>
This query is called a <em class="firstterm">left outer
join</em> 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.
</p>
<p><b>Exercise:</b> There are also right outer joins and full outer joins. Try to
find out what those do.
</p>
<p> <a name="id568944"></a>
<a name="id568954"></a>
We can also join a table against itself. This is called a
<em class="firstterm">self join</em>. 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
<code class="structfield">temp_lo</code> and <code class="structfield">temp_hi</code> columns of
each <code class="classname">weather</code> row to the
<code class="structfield">temp_lo</code> and
<code class="structfield">temp_hi</code> columns of all other
<code class="classname">weather</code> rows. We can do this with the
following query:
</p>
<pre class="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)</pre>
<p>
Here we have relabeled the weather table as <code class="literal">W1</code> and
<code class="literal">W2</code> 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.:
</p>
<pre class="programlisting">SELECT *
FROM weather w, cities c
WHERE w.city = c.name;</pre>
<p>
You will encounter this style of abbreviating quite frequently.
</p>
</div></body>
</html>
|
