← Back to branch summary

~ubuntu-branches/ubuntu/precise/postgresql-9.1/precise-security

« back to all changes in this revision

Viewing changes to doc/src/sgml/html/tutorial-join.html

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2011-05-11 10:41:53 UTC
  • Revision ID: james.westby@ubuntu.com-20110511104153-psbh2o58553fv1m0
Tags: upstream-9.1~beta1
Import upstream version 9.1~beta1

Show diffs side-by-side

added added

removed removed

Lines of Context:
doc/src/sgml/html/tutorial-join.html
 
1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 
2
<HTML
 
3
><HEAD
 
4
><TITLE
 
5
>Joins Between Tables</TITLE
 
6
><META
 
7
NAME="GENERATOR"
 
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
 
9
REV="MADE"
 
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
 
11
REL="HOME"
 
12
TITLE="PostgreSQL 9.1beta1 Documentation"
 
13
HREF="index.html"><LINK
 
14
REL="UP"
 
15
TITLE="The SQL Language"
 
16
HREF="tutorial-sql.html"><LINK
 
17
REL="PREVIOUS"
 
18
TITLE="Querying a Table"
 
19
HREF="tutorial-select.html"><LINK
 
20
REL="NEXT"
 
21
TITLE="Aggregate Functions"
 
22
HREF="tutorial-agg.html"><LINK
 
23
REL="STYLESHEET"
 
24
TYPE="text/css"
 
25
HREF="stylesheet.css"><META
 
26
HTTP-EQUIV="Content-Type"
 
27
CONTENT="text/html; charset=ISO-8859-1"><META
 
28
NAME="creation"
 
29
CONTENT="2011-04-27T21:20:33"></HEAD
 
30
><BODY
 
31
CLASS="SECT1"
 
32
><DIV
 
33
CLASS="NAVHEADER"
 
34
><TABLE
 
35
SUMMARY="Header navigation table"
 
36
WIDTH="100%"
 
37
BORDER="0"
 
38
CELLPADDING="0"
 
39
CELLSPACING="0"
 
40
><TR
 
41
><TH
 
42
COLSPAN="5"
 
43
ALIGN="center"
 
44
VALIGN="bottom"
 
45
><A
 
46
HREF="index.html"
 
47
>PostgreSQL 9.1beta1 Documentation</A
 
48
></TH
 
49
></TR
 
50
><TR
 
51
><TD
 
52
WIDTH="10%"
 
53
ALIGN="left"
 
54
VALIGN="top"
 
55
><A
 
56
TITLE="Querying a Table"
 
57
HREF="tutorial-select.html"
 
58
ACCESSKEY="P"
 
59
>Prev</A
 
60
></TD
 
61
><TD
 
62
WIDTH="10%"
 
63
ALIGN="left"
 
64
VALIGN="top"
 
65
><A
 
66
TITLE="The SQL Language"
 
67
HREF="tutorial-sql.html"
 
68
>Fast Backward</A
 
69
></TD
 
70
><TD
 
71
WIDTH="60%"
 
72
ALIGN="center"
 
73
VALIGN="bottom"
 
74
>Chapter 2. The <ACRONYM
 
75
CLASS="ACRONYM"
 
76
>SQL</ACRONYM
 
77
> Language</TD
 
78
><TD
 
79
WIDTH="10%"
 
80
ALIGN="right"
 
81
VALIGN="top"
 
82
><A
 
83
TITLE="The SQL Language"
 
84
HREF="tutorial-sql.html"
 
85
>Fast Forward</A
 
86
></TD
 
87
><TD
 
88
WIDTH="10%"
 
89
ALIGN="right"
 
90
VALIGN="top"
 
91
><A
 
92
TITLE="Aggregate Functions"
 
93
HREF="tutorial-agg.html"
 
94
ACCESSKEY="N"
 
95
>Next</A
 
96
></TD
 
97
></TR
 
98
></TABLE
 
99
><HR
 
100
ALIGN="LEFT"
 
101
WIDTH="100%"></DIV
 
102
><DIV
 
103
CLASS="SECT1"
 
104
><H1
 
105
CLASS="SECT1"
 
106
><A
 
107
NAME="TUTORIAL-JOIN"
 
108
>2.6. Joins Between Tables</A
 
109
></H1
 
110
><P
 
111
>    Thus far, our queries have only accessed one table at a time.
 
112
    Queries can access multiple tables at once, or access the same
 
113
    table in such a way that multiple rows of the table are being
 
114
    processed at the same time.  A query that accesses multiple rows
 
115
    of the same or different tables at one time is called a
 
116
    <I
 
117
CLASS="FIRSTTERM"
 
118
>join</I
 
119
> query.  As an example, say you wish to
 
120
    list all the weather records together with the location of the
 
121
    associated city.  To do that, we need to compare the <TT
 
122
CLASS="STRUCTFIELD"
 
123
>city</TT
 
124
>
 
125
    column of each row of the <TT
 
126
CLASS="STRUCTNAME"
 
127
>weather</TT
 
128
> table with the
 
129
    <TT
 
130
CLASS="STRUCTFIELD"
 
131
>name</TT
 
132
> column of all rows in the <TT
 
133
CLASS="STRUCTNAME"
 
134
>cities</TT
 
135
>
 
136
    table, and select the pairs of rows where these values match.
 
137
    </P><DIV
 
138
CLASS="NOTE"
 
139
><BLOCKQUOTE
 
140
CLASS="NOTE"
 
141
><P
 
142
><B
 
143
>Note: </B
 
144
>      This  is only a conceptual model.  The join is usually performed
 
145
      in a more efficient manner than actually comparing each possible
 
146
      pair of rows, but this is invisible to the user.
 
147
     </P
 
148
></BLOCKQUOTE
 
149
></DIV
 
150
><P>
 
151
    This would be accomplished by the following query:
 
152
 
 
153
</P><PRE
 
154
CLASS="PROGRAMLISTING"
 
155
>SELECT *
 
156
    FROM weather, cities
 
157
    WHERE city = name;</PRE
 
158
><P>
 
159
 
 
160
</P><PRE
 
161
CLASS="SCREEN"
 
162
>     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
 
163
---------------+---------+---------+------+------------+---------------+-----------
 
164
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 
165
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
 
166
(2 rows)</PRE
 
167
><P>
 
168
 
 
169
   </P
 
170
><P
 
171
>    Observe two things about the result set:
 
172
    <P
 
173
></P
 
174
></P><UL
 
175
><LI
 
176
><P
 
177
>       There is no result row for the city of Hayward.  This is
 
178
       because there is no matching entry in the
 
179
       <TT
 
180
CLASS="STRUCTNAME"
 
181
>cities</TT
 
182
> table for Hayward, so the join
 
183
       ignores the unmatched rows in the <TT
 
184
CLASS="STRUCTNAME"
 
185
>weather</TT
 
186
> table.  We will see
 
187
       shortly how this can be fixed.
 
188
      </P
 
189
></LI
 
190
><LI
 
191
><P
 
192
>       There are two columns containing the city name.  This is
 
193
       correct because the lists of columns from the
 
194
       <TT
 
195
CLASS="STRUCTNAME"
 
196
>weather</TT
 
197
> and
 
198
       <TT
 
199
CLASS="STRUCTNAME"
 
200
>cities</TT
 
201
> tables are concatenated.  In
 
202
       practice this is undesirable, though, so you will probably want
 
203
       to list the output columns explicitly rather than using
 
204
       <TT
 
205
CLASS="LITERAL"
 
206
>*</TT
 
207
>:
 
208
</P><PRE
 
209
CLASS="PROGRAMLISTING"
 
210
>SELECT city, temp_lo, temp_hi, prcp, date, location
 
211
    FROM weather, cities
 
212
    WHERE city = name;</PRE
 
213
><P>
 
214
      </P
 
215
></LI
 
216
></UL
 
217
><P>
 
218
   </P
 
219
><DIV
 
220
CLASS="FORMALPARA"
 
221
><P
 
222
><B
 
223
>Exercise: </B
 
224
>     Attempt to determine the semantics of this query when the
 
225
     <TT
 
226
CLASS="LITERAL"
 
227
>WHERE</TT
 
228
> clause is omitted.
 
229
    </P
 
230
></DIV
 
231
><P
 
232
>    Since the columns all had different names, the parser
 
233
    automatically found which table they belong to.  If there
 
234
    were duplicate column names in the two tables you'd need to
 
235
    <I
 
236
CLASS="FIRSTTERM"
 
237
>qualify</I
 
238
> the column names to show which one you
 
239
    meant, as in:
 
240
 
 
241
</P><PRE
 
242
CLASS="PROGRAMLISTING"
 
243
>SELECT weather.city, weather.temp_lo, weather.temp_hi,
 
244
       weather.prcp, weather.date, cities.location
 
245
    FROM weather, cities
 
246
    WHERE cities.name = weather.city;</PRE
 
247
><P>
 
248
 
 
249
    It is widely considered good style to qualify all column names
 
250
    in a join query, so that the query won't fail if a duplicate
 
251
    column name is later added to one of the tables.
 
252
   </P
 
253
><P
 
254
>    Join queries of the kind seen thus far can also be written in this
 
255
    alternative form:
 
256
 
 
257
</P><PRE
 
258
CLASS="PROGRAMLISTING"
 
259
>SELECT *
 
260
    FROM weather INNER JOIN cities ON (weather.city = cities.name);</PRE
 
261
><P>
 
262
 
 
263
    This syntax is not as commonly used as the one above, but we show
 
264
    it here to help you understand the following topics.
 
265
   </P
 
266
><P
 
267
>    
 
268
 
 
269
    Now we will figure out how we can get the Hayward records back in.
 
270
    What we want the query to do is to scan the
 
271
    <TT
 
272
CLASS="STRUCTNAME"
 
273
>weather</TT
 
274
> table and for each row to find the
 
275
    matching <TT
 
276
CLASS="STRUCTNAME"
 
277
>cities</TT
 
278
> row(s).  If no matching row is
 
279
    found we want some <SPAN
 
280
CLASS="QUOTE"
 
281
>"empty values"</SPAN
 
282
> to be substituted
 
283
    for the <TT
 
284
CLASS="STRUCTNAME"
 
285
>cities</TT
 
286
> table's columns.  This kind
 
287
    of query is called an <I
 
288
CLASS="FIRSTTERM"
 
289
>outer join</I
 
290
>.  (The
 
291
    joins we have seen so far are inner joins.)  The command looks
 
292
    like this:
 
293
 
 
294
</P><PRE
 
295
CLASS="PROGRAMLISTING"
 
296
>SELECT *
 
297
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
 
298
 
 
299
     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
 
300
---------------+---------+---------+------+------------+---------------+-----------
 
301
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 
302
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 
303
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
 
304
(3 rows)</PRE
 
305
><P>
 
306
 
 
307
    This query is called a <I
 
308
CLASS="FIRSTTERM"
 
309
>left outer
 
310
    join</I
 
311
> because the table mentioned on the left of the
 
312
    join operator will have each of its rows in the output at least
 
313
    once, whereas the table on the right will only have those rows
 
314
    output that match some row of the left table.  When outputting a
 
315
    left-table row for which there is no right-table match, empty (null)
 
316
    values are substituted for the right-table columns.
 
317
   </P
 
318
><DIV
 
319
CLASS="FORMALPARA"
 
320
><P
 
321
><B
 
322
>Exercise: </B
 
323
>     There are also right outer joins and full outer joins.  Try to
 
324
     find out what those do.
 
325
    </P
 
326
></DIV
 
327
><P
 
328
>    
 
329
    
 
330
 
 
331
    We can also join a table against itself.  This is called a
 
332
    <I
 
333
CLASS="FIRSTTERM"
 
334
>self join</I
 
335
>.  As an example, suppose we wish
 
336
    to find all the weather records that are in the temperature range
 
337
    of other weather records.  So we need to compare the
 
338
    <TT
 
339
CLASS="STRUCTFIELD"
 
340
>temp_lo</TT
 
341
> and <TT
 
342
CLASS="STRUCTFIELD"
 
343
>temp_hi</TT
 
344
> columns of
 
345
    each <TT
 
346
CLASS="STRUCTNAME"
 
347
>weather</TT
 
348
> row to the
 
349
    <TT
 
350
CLASS="STRUCTFIELD"
 
351
>temp_lo</TT
 
352
> and
 
353
    <TT
 
354
CLASS="STRUCTFIELD"
 
355
>temp_hi</TT
 
356
> columns of all other
 
357
    <TT
 
358
CLASS="STRUCTNAME"
 
359
>weather</TT
 
360
> rows.  We can do this with the
 
361
    following query:
 
362
 
 
363
</P><PRE
 
364
CLASS="PROGRAMLISTING"
 
365
>SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
 
366
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
 
367
    FROM weather W1, weather W2
 
368
    WHERE W1.temp_lo &lt; W2.temp_lo
 
369
    AND W1.temp_hi &gt; W2.temp_hi;
 
370
 
 
371
     city      | low | high |     city      | low | high
 
372
---------------+-----+------+---------------+-----+------
 
373
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 
374
 Hayward       |  37 |   54 | San Francisco |  46 |   50
 
375
(2 rows)</PRE
 
376
><P>
 
377
 
 
378
    Here we have relabeled the weather table as <TT
 
379
CLASS="LITERAL"
 
380
>W1</TT
 
381
> and
 
382
    <TT
 
383
CLASS="LITERAL"
 
384
>W2</TT
 
385
> to be able to distinguish the left and right side
 
386
    of the join.  You can also use these kinds of aliases in other
 
387
    queries to save some typing, e.g.:
 
388
</P><PRE
 
389
CLASS="PROGRAMLISTING"
 
390
>SELECT *
 
391
    FROM weather w, cities c
 
392
    WHERE w.city = c.name;</PRE
 
393
><P>
 
394
    You will encounter this style of abbreviating quite frequently.
 
395
   </P
 
396
></DIV
 
397
><DIV
 
398
CLASS="NAVFOOTER"
 
399
><HR
 
400
ALIGN="LEFT"
 
401
WIDTH="100%"><TABLE
 
402
SUMMARY="Footer navigation table"
 
403
WIDTH="100%"
 
404
BORDER="0"
 
405
CELLPADDING="0"
 
406
CELLSPACING="0"
 
407
><TR
 
408
><TD
 
409
WIDTH="33%"
 
410
ALIGN="left"
 
411
VALIGN="top"
 
412
><A
 
413
HREF="tutorial-select.html"
 
414
ACCESSKEY="P"
 
415
>Prev</A
 
416
></TD
 
417
><TD
 
418
WIDTH="34%"
 
419
ALIGN="center"
 
420
VALIGN="top"
 
421
><A
 
422
HREF="index.html"
 
423
ACCESSKEY="H"
 
424
>Home</A
 
425
></TD
 
426
><TD
 
427
WIDTH="33%"
 
428
ALIGN="right"
 
429
VALIGN="top"
 
430
><A
 
431
HREF="tutorial-agg.html"
 
432
ACCESSKEY="N"
 
433
>Next</A
 
434
></TD
 
435
></TR
 
436
><TR
 
437
><TD
 
438
WIDTH="33%"
 
439
ALIGN="left"
 
440
VALIGN="top"
 
441
>Querying a Table</TD
 
442
><TD
 
443
WIDTH="34%"
 
444
ALIGN="center"
 
445
VALIGN="top"
 
446
><A
 
447
HREF="tutorial-sql.html"
 
448
ACCESSKEY="U"
 
449
>Up</A
 
450
></TD
 
451
><TD
 
452
WIDTH="33%"
 
453
ALIGN="right"
 
454
VALIGN="top"
 
455
>Aggregate Functions</TD
 
456
></TR
 
457
></TABLE
 
458
></DIV
 
459
></BODY
 
460
></HTML
 
461
>
 
 
b'\\ No newline at end of file'