| 1 |
<!-- doc/src/sgml/query.sgml --> |
| 2 |
|
| 3 |
<chapter id="tutorial-sql"> |
| 4 |
<title>The <acronym>SQL</acronym> Language</title> |
| 5 |
|
| 6 |
<sect1 id="tutorial-sql-intro"> |
| 7 |
<title>Introduction</title> |
| 8 |
|
| 9 |
<para> |
| 10 |
This chapter provides an overview of how to use |
| 11 |
<acronym>SQL</acronym> to perform simple operations. This |
| 12 |
tutorial is only intended to give you an introduction and is in no |
| 13 |
way a complete tutorial on <acronym>SQL</acronym>. Numerous books |
| 14 |
have been written on <acronym>SQL</acronym>, including <xref |
| 15 |
linkend="melt93"/> and <xref linkend="date97"/>. |
| 16 |
You should be aware that some <productname>PostgreSQL</productname> |
| 17 |
language features are extensions to the standard. |
| 18 |
</para> |
| 19 |
|
| 20 |
<para> |
| 21 |
In the examples that follow, we assume that you have created a |
| 22 |
database named <literal>mydb</literal>, as described in the previous |
| 23 |
chapter, and have been able to start <application>psql</application>. |
| 24 |
</para> |
| 25 |
|
| 26 |
<para> |
| 27 |
Examples in this manual can also be found in the |
| 28 |
<productname>PostgreSQL</productname> source distribution |
| 29 |
in the directory <filename>src/tutorial/</filename>. (Binary |
| 30 |
distributions of <productname>PostgreSQL</productname> might not |
| 31 |
provide those files.) To use those |
| 32 |
files, first change to that directory and run <application>make</application>: |
| 33 |
|
| 34 |
<screen> |
| 35 |
<prompt>$</prompt> <userinput>cd <replaceable>...</replaceable>/src/tutorial</userinput> |
| 36 |
<prompt>$</prompt> <userinput>make</userinput> |
| 37 |
</screen> |
| 38 |
|
| 39 |
This creates the scripts and compiles the C files containing user-defined |
| 40 |
functions and types. Then, to start the tutorial, do the following: |
| 41 |
|
| 42 |
<screen> |
| 43 |
<prompt>$</prompt> <userinput>psql -s mydb</userinput> |
| 44 |
<computeroutput> |
| 45 |
... |
| 46 |
</computeroutput> |
| 47 |
<prompt>mydb=></prompt> <userinput>\i basics.sql</userinput> |
| 48 |
</screen> |
| 49 |
|
| 50 |
The <literal>\i</literal> command reads in commands from the |
| 51 |
specified file. <command>psql</command>'s <literal>-s</literal> option puts you in |
| 52 |
single step mode which pauses before sending each statement to the |
| 53 |
server. The commands used in this section are in the file |
| 54 |
<filename>basics.sql</filename>. |
| 55 |
</para> |
| 56 |
</sect1> |
| 57 |
|
| 58 |
|
| 59 |
<sect1 id="tutorial-concepts"> |
| 60 |
<title>Concepts</title> |
| 61 |
|
| 62 |
<para> |
| 63 |
<indexterm><primary>relational database</primary></indexterm> |
| 64 |
<indexterm><primary>hierarchical database</primary></indexterm> |
| 65 |
<indexterm><primary>object-oriented database</primary></indexterm> |
| 66 |
<indexterm><primary>relation</primary></indexterm> |
| 67 |
<indexterm><primary>table</primary></indexterm> |
| 68 |
|
| 69 |
<productname>PostgreSQL</productname> is a <firstterm>relational |
| 70 |
database management system</firstterm> (<acronym>RDBMS</acronym>). |
| 71 |
That means it is a system for managing data stored in |
| 72 |
<firstterm>relations</firstterm>. Relation is essentially a |
| 73 |
mathematical term for <firstterm>table</firstterm>. The notion of |
| 74 |
storing data in tables is so commonplace today that it might |
| 75 |
seem inherently obvious, but there are a number of other ways of |
| 76 |
organizing databases. Files and directories on Unix-like |
| 77 |
operating systems form an example of a hierarchical database. A |
| 78 |
more modern development is the object-oriented database. |
| 79 |
</para> |
| 80 |
|
| 81 |
<para> |
| 82 |
<indexterm><primary>row</primary></indexterm> |
| 83 |
<indexterm><primary>column</primary></indexterm> |
| 84 |
|
| 85 |
Each table is a named collection of <firstterm>rows</firstterm>. |
| 86 |
Each row of a given table has the same set of named |
| 87 |
<firstterm>columns</firstterm>, |
| 88 |
and each column is of a specific data type. Whereas columns have |
| 89 |
a fixed order in each row, it is important to remember that SQL |
| 90 |
does not guarantee the order of the rows within the table in any |
| 91 |
way (although they can be explicitly sorted for display). |
| 92 |
</para> |
| 93 |
|
| 94 |
<para> |
| 95 |
<indexterm><primary>database cluster</primary></indexterm> |
| 96 |
<indexterm><primary>cluster</primary><secondary>of databases</secondary><see>database cluster</see></indexterm> |
| 97 |
|
| 98 |
Tables are grouped into databases, and a collection of databases |
| 99 |
managed by a single <productname>PostgreSQL</productname> server |
| 100 |
instance constitutes a database <firstterm>cluster</firstterm>. |
| 101 |
</para> |
| 102 |
</sect1> |
| 103 |
|
| 104 |
|
| 105 |
<sect1 id="tutorial-table"> |
| 106 |
<title>Creating a New Table</title> |
| 107 |
|
| 108 |
<indexterm zone="tutorial-table"> |
| 109 |
<primary>CREATE TABLE</primary> |
| 110 |
</indexterm> |
| 111 |
|
| 112 |
<para> |
| 113 |
You can create a new table by specifying the table |
| 114 |
name, along with all column names and their types: |
| 115 |
|
| 116 |
<programlisting> |
| 117 |
CREATE TABLE weather ( |
| 118 |
city varchar(80), |
| 119 |
temp_lo int, -- low temperature |
| 120 |
temp_hi int, -- high temperature |
| 121 |
prcp real, -- precipitation |
| 122 |
date date |
| 123 |
); |
| 124 |
</programlisting> |
| 125 |
|
| 126 |
You can enter this into <command>psql</command> with the line |
| 127 |
breaks. <command>psql</command> will recognize that the command |
| 128 |
is not terminated until the semicolon. |
| 129 |
</para> |
| 130 |
|
| 131 |
<para> |
| 132 |
White space (i.e., spaces, tabs, and newlines) can be used freely |
| 133 |
in SQL commands. That means you can type the command aligned |
| 134 |
differently than above, or even all on one line. Two dashes |
| 135 |
(<quote><literal>--</literal></quote>) introduce comments. |
| 136 |
Whatever follows them is ignored up to the end of the line. SQL |
| 137 |
is case-insensitive about key words and identifiers, except |
| 138 |
when identifiers are double-quoted to preserve the case (not done |
| 139 |
above). |
| 140 |
</para> |
| 141 |
|
| 142 |
<para> |
| 143 |
<type>varchar(80)</type> specifies a data type that can store |
| 144 |
arbitrary character strings up to 80 characters in length. |
| 145 |
<type>int</type> is the normal integer type. <type>real</type> is |
| 146 |
a type for storing single precision floating-point numbers. |
| 147 |
<type>date</type> should be self-explanatory. (Yes, the column of |
| 148 |
type <type>date</type> is also named <structfield>date</structfield>. |
| 149 |
This might be convenient or confusing — you choose.) |
| 150 |
</para> |
| 151 |
|
| 152 |
<para> |
| 153 |
<productname>PostgreSQL</productname> supports the standard |
| 154 |
<acronym>SQL</acronym> types <type>int</type>, |
| 155 |
<type>smallint</type>, <type>real</type>, <type>double |
| 156 |
precision</type>, <type>char(<replaceable>N</replaceable>)</type>, |
| 157 |
<type>varchar(<replaceable>N</replaceable>)</type>, <type>date</type>, |
| 158 |
<type>time</type>, <type>timestamp</type>, and |
| 159 |
<type>interval</type>, as well as other types of general utility |
| 160 |
and a rich set of geometric types. |
| 161 |
<productname>PostgreSQL</productname> can be customized with an |
| 162 |
arbitrary number of user-defined data types. Consequently, type |
| 163 |
names are not key words in the syntax, except where required to |
| 164 |
support special cases in the <acronym>SQL</acronym> standard. |
| 165 |
</para> |
| 166 |
|
| 167 |
<para> |
| 168 |
The second example will store cities and their associated |
| 169 |
geographical location: |
| 170 |
<programlisting> |
| 171 |
CREATE TABLE cities ( |
| 172 |
name varchar(80), |
| 173 |
location point |
| 174 |
); |
| 175 |
</programlisting> |
| 176 |
The <type>point</type> type is an example of a |
| 177 |
<productname>PostgreSQL</productname>-specific data type. |
| 178 |
</para> |
| 179 |
|
| 180 |
<para> |
| 181 |
<indexterm> |
| 182 |
<primary>DROP TABLE</primary> |
| 183 |
</indexterm> |
| 184 |
|
| 185 |
Finally, it should be mentioned that if you don't need a table any |
| 186 |
longer or want to recreate it differently you can remove it using |
| 187 |
the following command: |
| 188 |
<synopsis> |
| 189 |
DROP TABLE <replaceable>tablename</replaceable>; |
| 190 |
</synopsis> |
| 191 |
</para> |
| 192 |
</sect1> |
| 193 |
|
| 194 |
|
| 195 |
<sect1 id="tutorial-populate"> |
| 196 |
<title>Populating a Table With Rows</title> |
| 197 |
|
| 198 |
<indexterm zone="tutorial-populate"> |
| 199 |
<primary>INSERT</primary> |
| 200 |
</indexterm> |
| 201 |
|
| 202 |
<para> |
| 203 |
The <command>INSERT</command> statement is used to populate a table with |
| 204 |
rows: |
| 205 |
|
| 206 |
<programlisting> |
| 207 |
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27'); |
| 208 |
</programlisting> |
| 209 |
|
| 210 |
Note that all data types use rather obvious input formats. |
| 211 |
Constants that are not simple numeric values usually must be |
| 212 |
surrounded by single quotes (<literal>'</literal>), as in the example. |
| 213 |
The |
| 214 |
<type>date</type> type is actually quite flexible in what it |
| 215 |
accepts, but for this tutorial we will stick to the unambiguous |
| 216 |
format shown here. |
| 217 |
</para> |
| 218 |
|
| 219 |
<para> |
| 220 |
The <type>point</type> type requires a coordinate pair as input, |
| 221 |
as shown here: |
| 222 |
<programlisting> |
| 223 |
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)'); |
| 224 |
</programlisting> |
| 225 |
</para> |
| 226 |
|
| 227 |
<para> |
| 228 |
The syntax used so far requires you to remember the order of the |
| 229 |
columns. An alternative syntax allows you to list the columns |
| 230 |
explicitly: |
| 231 |
<programlisting> |
| 232 |
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) |
| 233 |
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29'); |
| 234 |
</programlisting> |
| 235 |
You can list the columns in a different order if you wish or |
| 236 |
even omit some columns, e.g., if the precipitation is unknown: |
| 237 |
<programlisting> |
| 238 |
INSERT INTO weather (date, city, temp_hi, temp_lo) |
| 239 |
VALUES ('1994-11-29', 'Hayward', 54, 37); |
| 240 |
</programlisting> |
| 241 |
Many developers consider explicitly listing the columns better |
| 242 |
style than relying on the order implicitly. |
| 243 |
</para> |
| 244 |
|
| 245 |
<para> |
| 246 |
Please enter all the commands shown above so you have some data to |
| 247 |
work with in the following sections. |
| 248 |
</para> |
| 249 |
|
| 250 |
<para> |
| 251 |
<indexterm> |
| 252 |
<primary>COPY</primary> |
| 253 |
</indexterm> |
| 254 |
|
| 255 |
You could also have used <command>COPY</command> to load large |
| 256 |
amounts of data from flat-text files. This is usually faster |
| 257 |
because the <command>COPY</command> command is optimized for this |
| 258 |
application while allowing less flexibility than |
| 259 |
<command>INSERT</command>. An example would be: |
| 260 |
|
| 261 |
<programlisting> |
| 262 |
COPY weather FROM '/home/user/weather.txt'; |
| 263 |
</programlisting> |
| 264 |
|
| 265 |
where the file name for the source file must be available on the |
| 266 |
machine running the backend process, not the client, since the backend process |
| 267 |
reads the file directly. The data inserted above into the weather table |
| 268 |
could also be inserted from a file containing (values are separated by a |
| 269 |
tab character): |
| 270 |
|
| 271 |
<programlisting> |
| 272 |
San Francisco 46 50 0.25 1994-11-27 |
| 273 |
San Francisco 43 57 0.0 1994-11-29 |
| 274 |
Hayward 37 54 \N 1994-11-29 |
| 275 |
</programlisting> |
| 276 |
|
| 277 |
You can read more about the <command>COPY</command> command in |
| 278 |
<xref linkend="sql-copy"/>. |
| 279 |
</para> |
| 280 |
</sect1> |
| 281 |
|
| 282 |
|
| 283 |
<sect1 id="tutorial-select"> |
| 284 |
<title>Querying a Table</title> |
| 285 |
|
| 286 |
<para> |
| 287 |
<indexterm><primary>query</primary></indexterm> |
| 288 |
<indexterm><primary>SELECT</primary></indexterm> |
| 289 |
|
| 290 |
To retrieve data from a table, the table is |
| 291 |
<firstterm>queried</firstterm>. An <acronym>SQL</acronym> |
| 292 |
<command>SELECT</command> statement is used to do this. The |
| 293 |
statement is divided into a select list (the part that lists the |
| 294 |
columns to be returned), a table list (the part that lists the |
| 295 |
tables from which to retrieve the data), and an optional |
| 296 |
qualification (the part that specifies any restrictions). For |
| 297 |
example, to retrieve all the rows of table |
| 298 |
<structname>weather</structname>, type: |
| 299 |
<programlisting> |
| 300 |
SELECT * FROM weather; |
| 301 |
</programlisting> |
| 302 |
Here <literal>*</literal> is a shorthand for <quote>all columns</quote>. |
| 303 |
<footnote> |
| 304 |
<para> |
| 305 |
While <literal>SELECT *</literal> is useful for off-the-cuff |
| 306 |
queries, it is widely considered bad style in production code, |
| 307 |
since adding a column to the table would change the results. |
| 308 |
</para> |
| 309 |
</footnote> |
| 310 |
So the same result would be had with: |
| 311 |
<programlisting> |
| 312 |
SELECT city, temp_lo, temp_hi, prcp, date FROM weather; |
| 313 |
</programlisting> |
| 314 |
|
| 315 |
The output should be: |
| 316 |
|
| 317 |
<screen> |
| 318 |
city | temp_lo | temp_hi | prcp | date |
| 319 |
---------------+---------+---------+------+------------ |
| 320 |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
| 321 |
San Francisco | 43 | 57 | 0 | 1994-11-29 |
| 322 |
Hayward | 37 | 54 | | 1994-11-29 |
| 323 |
(3 rows) |
| 324 |
</screen> |
| 325 |
</para> |
| 326 |
|
| 327 |
<para> |
| 328 |
You can write expressions, not just simple column references, in the |
| 329 |
select list. For example, you can do: |
| 330 |
<programlisting> |
| 331 |
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather; |
| 332 |
</programlisting> |
| 333 |
This should give: |
| 334 |
<screen> |
| 335 |
city | temp_avg | date |
| 336 |
---------------+----------+------------ |
| 337 |
San Francisco | 48 | 1994-11-27 |
| 338 |
San Francisco | 50 | 1994-11-29 |
| 339 |
Hayward | 45 | 1994-11-29 |
| 340 |
(3 rows) |
| 341 |
</screen> |
| 342 |
Notice how the <literal>AS</literal> clause is used to relabel the |
| 343 |
output column. (The <literal>AS</literal> clause is optional.) |
| 344 |
</para> |
| 345 |
|
| 346 |
<para> |
| 347 |
A query can be <quote>qualified</quote> by adding a <literal>WHERE</literal> |
| 348 |
clause that specifies which rows are wanted. The <literal>WHERE</literal> |
| 349 |
clause contains a Boolean (truth value) expression, and only rows for |
| 350 |
which the Boolean expression is true are returned. The usual |
| 351 |
Boolean operators (<literal>AND</literal>, |
| 352 |
<literal>OR</literal>, and <literal>NOT</literal>) are allowed in |
| 353 |
the qualification. For example, the following |
| 354 |
retrieves the weather of San Francisco on rainy days: |
| 355 |
|
| 356 |
<programlisting> |
| 357 |
SELECT * FROM weather |
| 358 |
WHERE city = 'San Francisco' AND prcp > 0.0; |
| 359 |
</programlisting> |
| 360 |
Result: |
| 361 |
<screen> |
| 362 |
city | temp_lo | temp_hi | prcp | date |
| 363 |
---------------+---------+---------+------+------------ |
| 364 |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
| 365 |
(1 row) |
| 366 |
</screen> |
| 367 |
</para> |
| 368 |
|
| 369 |
<para> |
| 370 |
<indexterm><primary>ORDER BY</primary></indexterm> |
| 371 |
|
| 372 |
You can request that the results of a query |
| 373 |
be returned in sorted order: |
| 374 |
|
| 375 |
<programlisting> |
| 376 |
SELECT * FROM weather |
| 377 |
ORDER BY city; |
| 378 |
</programlisting> |
| 379 |
|
| 380 |
<screen> |
| 381 |
city | temp_lo | temp_hi | prcp | date |
| 382 |
---------------+---------+---------+------+------------ |
| 383 |
Hayward | 37 | 54 | | 1994-11-29 |
| 384 |
San Francisco | 43 | 57 | 0 | 1994-11-29 |
| 385 |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
| 386 |
</screen> |
| 387 |
|
| 388 |
In this example, the sort order isn't fully specified, and so you |
| 389 |
might get the San Francisco rows in either order. But you'd always |
| 390 |
get the results shown above if you do: |
| 391 |
|
| 392 |
<programlisting> |
| 393 |
SELECT * FROM weather |
| 394 |
ORDER BY city, temp_lo; |
| 395 |
</programlisting> |
| 396 |
</para> |
| 397 |
|
| 398 |
<para> |
| 399 |
<indexterm><primary>DISTINCT</primary></indexterm> |
| 400 |
<indexterm><primary>duplicate</primary></indexterm> |
| 401 |
|
| 402 |
You can request that duplicate rows be removed from the result of |
| 403 |
a query: |
| 404 |
|
| 405 |
<programlisting> |
| 406 |
SELECT DISTINCT city |
| 407 |
FROM weather; |
| 408 |
</programlisting> |
| 409 |
|
| 410 |
<screen> |
| 411 |
city |
| 412 |
--------------- |
| 413 |
Hayward |
| 414 |
San Francisco |
| 415 |
(2 rows) |
| 416 |
</screen> |
| 417 |
|
| 418 |
Here again, the result row ordering might vary. |
| 419 |
You can ensure consistent results by using <literal>DISTINCT</literal> and |
| 420 |
<literal>ORDER BY</literal> together: |
| 421 |
<footnote> |
| 422 |
<para> |
| 423 |
In some database systems, including older versions of |
| 424 |
<productname>PostgreSQL</productname>, the implementation of |
| 425 |
<literal>DISTINCT</literal> automatically orders the rows and |
| 426 |
so <literal>ORDER BY</literal> is unnecessary. But this is not |
| 427 |
required by the SQL standard, and current |
| 428 |
<productname>PostgreSQL</productname> does not guarantee that |
| 429 |
<literal>DISTINCT</literal> causes the rows to be ordered. |
| 430 |
</para> |
| 431 |
</footnote> |
| 432 |
|
| 433 |
<programlisting> |
| 434 |
SELECT DISTINCT city |
| 435 |
FROM weather |
| 436 |
ORDER BY city; |
| 437 |
</programlisting> |
| 438 |
</para> |
| 439 |
</sect1> |
| 440 |
|
| 441 |
|
| 442 |
<sect1 id="tutorial-join"> |
| 443 |
<title>Joins Between Tables</title> |
| 444 |
|
| 445 |
<indexterm zone="tutorial-join"> |
| 446 |
<primary>join</primary> |
| 447 |
</indexterm> |
| 448 |
|
| 449 |
<para> |
| 450 |
Thus far, our queries have only accessed one table at a time. |
| 451 |
Queries can access multiple tables at once, or access the same |
| 452 |
table in such a way that multiple rows of the table are being |
| 453 |
processed at the same time. Queries that access multiple tables |
| 454 |
(or multiple instances of the same table) at one time are called |
| 455 |
<firstterm>join</firstterm> queries. They combine rows from one table |
| 456 |
with rows from a second table, with an expression specifying which rows |
| 457 |
are to be paired. For example, to return all the weather records together |
| 458 |
with the location of the associated city, the database needs to compare |
| 459 |
the <structfield>city</structfield> |
| 460 |
column of each row of the <structname>weather</structname> table with the |
| 461 |
<structfield>name</structfield> column of all rows in the <structname>cities</structname> |
| 462 |
table, and select the pairs of rows where these values match.<footnote> |
| 463 |
<para> |
| 464 |
This is only a conceptual model. The join is usually performed |
| 465 |
in a more efficient manner than actually comparing each possible |
| 466 |
pair of rows, but this is invisible to the user. |
| 467 |
</para> |
| 468 |
</footnote> |
| 469 |
This would be accomplished by the following query: |
| 470 |
|
| 471 |
<programlisting> |
| 472 |
SELECT * FROM weather JOIN cities ON city = name; |
| 473 |
</programlisting> |
| 474 |
|
| 475 |
<screen> |
| 476 |
city | temp_lo | temp_hi | prcp | date | name | location |
| 477 |
---------------+---------+---------+------+------------+---------------+----------- |
| 478 |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) |
| 479 |
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) |
| 480 |
(2 rows) |
| 481 |
</screen> |
| 482 |
|
| 483 |
</para> |
| 484 |
|
| 485 |
<para> |
| 486 |
Observe two things about the result set: |
| 487 |
<itemizedlist> |
| 488 |
<listitem> |
| 489 |
<para> |
| 490 |
There is no result row for the city of Hayward. This is |
| 491 |
because there is no matching entry in the |
| 492 |
<structname>cities</structname> table for Hayward, so the join |
| 493 |
ignores the unmatched rows in the <structname>weather</structname> table. We will see |
| 494 |
shortly how this can be fixed. |
| 495 |
</para> |
| 496 |
</listitem> |
| 497 |
|
| 498 |
<listitem> |
| 499 |
<para> |
| 500 |
There are two columns containing the city name. This is |
| 501 |
correct because the lists of columns from the |
| 502 |
<structname>weather</structname> and |
| 503 |
<structname>cities</structname> tables are concatenated. In |
| 504 |
practice this is undesirable, though, so you will probably want |
| 505 |
to list the output columns explicitly rather than using |
| 506 |
<literal>*</literal>: |
| 507 |
<programlisting> |
| 508 |
SELECT city, temp_lo, temp_hi, prcp, date, location |
| 509 |
FROM weather JOIN cities ON city = name; |
| 510 |
</programlisting> |
| 511 |
</para> |
| 512 |
</listitem> |
| 513 |
</itemizedlist> |
| 514 |
</para> |
| 515 |
|
| 516 |
<para> |
| 517 |
Since the columns all had different names, the parser |
| 518 |
automatically found which table they belong to. If there |
| 519 |
were duplicate column names in the two tables you'd need to |
| 520 |
<firstterm>qualify</firstterm> the column names to show which one you |
| 521 |
meant, as in: |
| 522 |
|
| 523 |
<programlisting> |
| 524 |
SELECT weather.city, weather.temp_lo, weather.temp_hi, |
| 525 |
weather.prcp, weather.date, cities.location |
| 526 |
FROM weather JOIN cities ON weather.city = cities.name; |
| 527 |
</programlisting> |
| 528 |
|
| 529 |
It is widely considered good style to qualify all column names |
| 530 |
in a join query, so that the query won't fail if a duplicate |
| 531 |
column name is later added to one of the tables. |
| 532 |
</para> |
| 533 |
|
| 534 |
<para> |
| 535 |
Join queries of the kind seen thus far can also be written in this |
| 536 |
form: |
| 537 |
|
| 538 |
<programlisting> |
| 539 |
SELECT * |
| 540 |
FROM weather, cities |
| 541 |
WHERE city = name; |
| 542 |
</programlisting> |
| 543 |
|
| 544 |
This syntax pre-dates the <literal>JOIN</literal>/<literal>ON</literal> |
| 545 |
syntax, which was introduced in SQL-92. The tables are simply listed in |
| 546 |
the <literal>FROM</literal> clause, and the comparison expression is added |
| 547 |
to the <literal>WHERE</literal> clause. The results from this older |
| 548 |
implicit syntax and the newer explicit |
| 549 |
<literal>JOIN</literal>/<literal>ON</literal> syntax are identical. But |
| 550 |
for a reader of the query, the explicit syntax makes its meaning easier to |
| 551 |
understand: The join condition is introduced by its own key word whereas |
| 552 |
previously the condition was mixed into the <literal>WHERE</literal> |
| 553 |
clause together with other conditions. |
| 554 |
</para> |
| 555 |
|
| 556 |
<indexterm><primary>join</primary><secondary>outer</secondary></indexterm> |
| 557 |
|
| 558 |
<para> |
| 559 |
Now we will figure out how we can get the Hayward records back in. |
| 560 |
What we want the query to do is to scan the |
| 561 |
<structname>weather</structname> table and for each row to find the |
| 562 |
matching <structname>cities</structname> row(s). If no matching row is |
| 563 |
found we want some <quote>empty values</quote> to be substituted |
| 564 |
for the <structname>cities</structname> table's columns. This kind |
| 565 |
of query is called an <firstterm>outer join</firstterm>. (The |
| 566 |
joins we have seen so far are <firstterm>inner joins</firstterm>.) |
| 567 |
The command looks like this: |
| 568 |
|
| 569 |
<programlisting> |
| 570 |
SELECT * |
| 571 |
FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name; |
| 572 |
</programlisting> |
| 573 |
|
| 574 |
<screen> |
| 575 |
city | temp_lo | temp_hi | prcp | date | name | location |
| 576 |
---------------+---------+---------+------+------------+---------------+----------- |
| 577 |
Hayward | 37 | 54 | | 1994-11-29 | | |
| 578 |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) |
| 579 |
San Francisco | 43 | 57 | 0 | 1994-11-29 | San Francisco | (-194,53) |
| 580 |
(3 rows) |
| 581 |
</screen> |
| 582 |
|
| 583 |
This query is called a <firstterm>left outer |
| 584 |
join</firstterm> because the table mentioned on the left of the |
| 585 |
join operator will have each of its rows in the output at least |
| 586 |
once, whereas the table on the right will only have those rows |
| 587 |
output that match some row of the left table. When outputting a |
| 588 |
left-table row for which there is no right-table match, empty (null) |
| 589 |
values are substituted for the right-table columns. |
| 590 |
</para> |
| 591 |
|
| 592 |
<formalpara> |
| 593 |
<title>Exercise:</title> |
| 594 |
|
| 595 |
<para> |
| 596 |
There are also right outer joins and full outer joins. Try to |
| 597 |
find out what those do. |
| 598 |
</para> |
| 599 |
</formalpara> |
| 600 |
|
| 601 |
<indexterm><primary>join</primary><secondary>self</secondary></indexterm> |
| 602 |
<indexterm><primary>alias</primary><secondary>for table name in query</secondary></indexterm> |
| 603 |
<para> |
| 604 |
We can also join a table against itself. This is called a |
| 605 |
<firstterm>self join</firstterm>. As an example, suppose we wish |
| 606 |
to find all the weather records that are in the temperature range |
| 607 |
of other weather records. So we need to compare the |
| 608 |
<structfield>temp_lo</structfield> and <structfield>temp_hi</structfield> columns of |
| 609 |
each <structname>weather</structname> row to the |
| 610 |
<structfield>temp_lo</structfield> and |
| 611 |
<structfield>temp_hi</structfield> columns of all other |
| 612 |
<structname>weather</structname> rows. We can do this with the |
| 613 |
following query: |
| 614 |
|
| 615 |
<programlisting> |
| 616 |
SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high, |
| 617 |
w2.city, w2.temp_lo AS low, w2.temp_hi AS high |
| 618 |
FROM weather w1 JOIN weather w2 |
| 619 |
ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi; |
| 620 |
</programlisting> |
| 621 |
|
| 622 |
<screen> |
| 623 |
city | low | high | city | low | high |
| 624 |
---------------+-----+------+---------------+-----+------ |
| 625 |
San Francisco | 43 | 57 | San Francisco | 46 | 50 |
| 626 |
Hayward | 37 | 54 | San Francisco | 46 | 50 |
| 627 |
(2 rows) |
| 628 |
</screen> |
| 629 |
|
| 630 |
Here we have relabeled the weather table as <literal>w1</literal> and |
| 631 |
<literal>w2</literal> to be able to distinguish the left and right side |
| 632 |
of the join. You can also use these kinds of aliases in other |
| 633 |
queries to save some typing, e.g.: |
| 634 |
<programlisting> |
| 635 |
SELECT * |
| 636 |
FROM weather w JOIN cities c ON w.city = c.name; |
| 637 |
</programlisting> |
| 638 |
You will encounter this style of abbreviating quite frequently. |
| 639 |
</para> |
| 640 |
</sect1> |
| 641 |
|
| 642 |
|
| 643 |
<sect1 id="tutorial-agg"> |
| 644 |
<title>Aggregate Functions</title> |
| 645 |
|
| 646 |
<indexterm zone="tutorial-agg"> |
| 647 |
<primary>aggregate function</primary> |
| 648 |
</indexterm> |
| 649 |
|
| 650 |
<para> |
| 651 |
Like most other relational database products, |
| 652 |
<productname>PostgreSQL</productname> supports |
| 653 |
<firstterm>aggregate functions</firstterm>. |
| 654 |
An aggregate function computes a single result from multiple input rows. |
| 655 |
For example, there are aggregates to compute the |
| 656 |
<function>count</function>, <function>sum</function>, |
| 657 |
<function>avg</function> (average), <function>max</function> (maximum) and |
| 658 |
<function>min</function> (minimum) over a set of rows. |
| 659 |
</para> |
| 660 |
|
| 661 |
<para> |
| 662 |
As an example, we can find the highest low-temperature reading anywhere |
| 663 |
with: |
| 664 |
|
| 665 |
<programlisting> |
| 666 |
SELECT max(temp_lo) FROM weather; |
| 667 |
</programlisting> |
| 668 |
|
| 669 |
<screen> |
| 670 |
max |
| 671 |
----- |
| 672 |
46 |
| 673 |
(1 row) |
| 674 |
</screen> |
| 675 |
</para> |
| 676 |
|
| 677 |
<para> |
| 678 |
<indexterm><primary>subquery</primary></indexterm> |
| 679 |
|
| 680 |
If we wanted to know what city (or cities) that reading occurred in, |
| 681 |
we might try: |
| 682 |
|
| 683 |
<programlisting> |
| 684 |
SELECT city FROM weather WHERE temp_lo = max(temp_lo); <lineannotation>-- WRONG</lineannotation> |
| 685 |
</programlisting> |
| 686 |
|
| 687 |
but this will not work since the aggregate |
| 688 |
<function>max</function> cannot be used in the |
| 689 |
<literal>WHERE</literal> clause. (This restriction exists because |
| 690 |
the <literal>WHERE</literal> clause determines which rows will be |
| 691 |
included in the aggregate calculation; so obviously it has to be evaluated |
| 692 |
before aggregate functions are computed.) |
| 693 |
However, as is often the case |
| 694 |
the query can be restated to accomplish the desired result, here |
| 695 |
by using a <firstterm>subquery</firstterm>: |
| 696 |
|
| 697 |
<programlisting> |
| 698 |
SELECT city FROM weather |
| 699 |
WHERE temp_lo = (SELECT max(temp_lo) FROM weather); |
| 700 |
</programlisting> |
| 701 |
|
| 702 |
<screen> |
| 703 |
city |
| 704 |
--------------- |
| 705 |
San Francisco |
| 706 |
(1 row) |
| 707 |
</screen> |
| 708 |
|
| 709 |
This is OK because the subquery is an independent computation |
| 710 |
that computes its own aggregate separately from what is happening |
| 711 |
in the outer query. |
| 712 |
</para> |
| 713 |
|
| 714 |
<para> |
| 715 |
<indexterm><primary>GROUP BY</primary></indexterm> |
| 716 |
<indexterm><primary>HAVING</primary></indexterm> |
| 717 |
|
| 718 |
Aggregates are also very useful in combination with <literal>GROUP |
| 719 |
BY</literal> clauses. For example, we can get the number of readings |
| 720 |
and the maximum low temperature observed in each city with: |
| 721 |
|
| 722 |
<programlisting> |
| 723 |
SELECT city, count(*), max(temp_lo) |
| 724 |
FROM weather |
| 725 |
GROUP BY city; |
| 726 |
</programlisting> |
| 727 |
|
| 728 |
<screen> |
| 729 |
city | count | max |
| 730 |
---------------+-------+----- |
| 731 |
Hayward | 1 | 37 |
| 732 |
San Francisco | 2 | 46 |
| 733 |
(2 rows) |
| 734 |
</screen> |
| 735 |
|
| 736 |
which gives us one output row per city. Each aggregate result is |
| 737 |
computed over the table rows matching that city. |
| 738 |
We can filter these grouped |
| 739 |
rows using <literal>HAVING</literal>: |
| 740 |
|
| 741 |
<programlisting> |
| 742 |
SELECT city, count(*), max(temp_lo) |
| 743 |
FROM weather |
| 744 |
GROUP BY city |
| 745 |
HAVING max(temp_lo) < 40; |
| 746 |
</programlisting> |
| 747 |
|
| 748 |
<screen> |
| 749 |
city | count | max |
| 750 |
---------+-------+----- |
| 751 |
Hayward | 1 | 37 |
| 752 |
(1 row) |
| 753 |
</screen> |
| 754 |
|
| 755 |
which gives us the same results for only the cities that have all |
| 756 |
<structfield>temp_lo</structfield> values below 40. Finally, if we only care about |
| 757 |
cities whose |
| 758 |
names begin with <quote><literal>S</literal></quote>, we might do: |
| 759 |
|
| 760 |
<programlisting> |
| 761 |
SELECT city, count(*), max(temp_lo) |
| 762 |
FROM weather |
| 763 |
WHERE city LIKE 'S%' -- <co id="co.tutorial-agg-like"/> |
| 764 |
GROUP BY city; |
| 765 |
</programlisting> |
| 766 |
|
| 767 |
<screen> |
| 768 |
city | count | max |
| 769 |
---------------+-------+----- |
| 770 |
San Francisco | 2 | 46 |
| 771 |
(1 row) |
| 772 |
</screen> |
| 773 |
<calloutlist> |
| 774 |
<callout arearefs="co.tutorial-agg-like"> |
| 775 |
<para> |
| 776 |
The <literal>LIKE</literal> operator does pattern matching and |
| 777 |
is explained in <xref linkend="functions-matching"/>. |
| 778 |
</para> |
| 779 |
</callout> |
| 780 |
</calloutlist> |
| 781 |
</para> |
| 782 |
|
| 783 |
<para> |
| 784 |
It is important to understand the interaction between aggregates and |
| 785 |
<acronym>SQL</acronym>'s <literal>WHERE</literal> and <literal>HAVING</literal> clauses. |
| 786 |
The fundamental difference between <literal>WHERE</literal> and |
| 787 |
<literal>HAVING</literal> is this: <literal>WHERE</literal> selects |
| 788 |
input rows before groups and aggregates are computed (thus, it controls |
| 789 |
which rows go into the aggregate computation), whereas |
| 790 |
<literal>HAVING</literal> selects group rows after groups and |
| 791 |
aggregates are computed. Thus, the |
| 792 |
<literal>WHERE</literal> clause must not contain aggregate functions; |
| 793 |
it makes no sense to try to use an aggregate to determine which rows |
| 794 |
will be inputs to the aggregates. On the other hand, the |
| 795 |
<literal>HAVING</literal> clause always contains aggregate functions. |
| 796 |
(Strictly speaking, you are allowed to write a <literal>HAVING</literal> |
| 797 |
clause that doesn't use aggregates, but it's seldom useful. The same |
| 798 |
condition could be used more efficiently at the <literal>WHERE</literal> |
| 799 |
stage.) |
| 800 |
</para> |
| 801 |
|
| 802 |
<para> |
| 803 |
In the previous example, we can apply the city name restriction in |
| 804 |
<literal>WHERE</literal>, since it needs no aggregate. This is |
| 805 |
more efficient than adding the restriction to <literal>HAVING</literal>, |
| 806 |
because we avoid doing the grouping and aggregate calculations |
| 807 |
for all rows that fail the <literal>WHERE</literal> check. |
| 808 |
</para> |
| 809 |
|
| 810 |
<para> |
| 811 |
Another way to select the rows that go into an aggregate |
| 812 |
computation is to use <literal>FILTER</literal>, which is a |
| 813 |
per-aggregate option: |
| 814 |
|
| 815 |
<programlisting> |
| 816 |
SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo) |
| 817 |
FROM weather |
| 818 |
GROUP BY city; |
| 819 |
</programlisting> |
| 820 |
|
| 821 |
<screen> |
| 822 |
city | count | max |
| 823 |
---------------+-------+----- |
| 824 |
Hayward | 1 | 37 |
| 825 |
San Francisco | 1 | 46 |
| 826 |
(2 rows) |
| 827 |
</screen> |
| 828 |
|
| 829 |
<literal>FILTER</literal> is much like <literal>WHERE</literal>, |
| 830 |
except that it removes rows only from the input of the particular |
| 831 |
aggregate function that it is attached to. |
| 832 |
Here, the <literal>count</literal> aggregate counts only |
| 833 |
rows with <literal>temp_lo</literal> below 45; but the |
| 834 |
<literal>max</literal> aggregate is still applied to all rows, |
| 835 |
so it still finds the reading of 46. |
| 836 |
</para> |
| 837 |
</sect1> |
| 838 |
|
| 839 |
|
| 840 |
<sect1 id="tutorial-update"> |
| 841 |
<title>Updates</title> |
| 842 |
|
| 843 |
<indexterm zone="tutorial-update"> |
| 844 |
<primary>UPDATE</primary> |
| 845 |
</indexterm> |
| 846 |
|
| 847 |
<para> |
| 848 |
You can update existing rows using the |
| 849 |
<command>UPDATE</command> command. |
| 850 |
Suppose you discover the temperature readings are |
| 851 |
all off by 2 degrees after November 28. You can correct the |
| 852 |
data as follows: |
| 853 |
|
| 854 |
<programlisting> |
| 855 |
UPDATE weather |
| 856 |
SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 |
| 857 |
WHERE date > '1994-11-28'; |
| 858 |
</programlisting> |
| 859 |
</para> |
| 860 |
|
| 861 |
<para> |
| 862 |
Look at the new state of the data: |
| 863 |
<programlisting> |
| 864 |
SELECT * FROM weather; |
| 865 |
|
| 866 |
city | temp_lo | temp_hi | prcp | date |
| 867 |
---------------+---------+---------+------+------------ |
| 868 |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
| 869 |
San Francisco | 41 | 55 | 0 | 1994-11-29 |
| 870 |
Hayward | 35 | 52 | | 1994-11-29 |
| 871 |
(3 rows) |
| 872 |
</programlisting> |
| 873 |
</para> |
| 874 |
</sect1> |
| 875 |
|
| 876 |
<sect1 id="tutorial-delete"> |
| 877 |
<title>Deletions</title> |
| 878 |
|
| 879 |
<indexterm zone="tutorial-delete"> |
| 880 |
<primary>DELETE</primary> |
| 881 |
</indexterm> |
| 882 |
|
| 883 |
<para> |
| 884 |
Rows can be removed from a table using the <command>DELETE</command> |
| 885 |
command. |
| 886 |
Suppose you are no longer interested in the weather of Hayward. |
| 887 |
Then you can do the following to delete those rows from the table: |
| 888 |
<programlisting> |
| 889 |
DELETE FROM weather WHERE city = 'Hayward'; |
| 890 |
</programlisting> |
| 891 |
|
| 892 |
All weather records belonging to Hayward are removed. |
| 893 |
|
| 894 |
<programlisting> |
| 895 |
SELECT * FROM weather; |
| 896 |
</programlisting> |
| 897 |
|
| 898 |
<screen> |
| 899 |
city | temp_lo | temp_hi | prcp | date |
| 900 |
---------------+---------+---------+------+------------ |
| 901 |
San Francisco | 46 | 50 | 0.25 | 1994-11-27 |
| 902 |
San Francisco | 41 | 55 | 0 | 1994-11-29 |
| 903 |
(2 rows) |
| 904 |
</screen> |
| 905 |
</para> |
| 906 |
|
| 907 |
<para> |
| 908 |
One should be wary of statements of the form |
| 909 |
<synopsis> |
| 910 |
DELETE FROM <replaceable>tablename</replaceable>; |
| 911 |
</synopsis> |
| 912 |
|
| 913 |
Without a qualification, <command>DELETE</command> will |
| 914 |
remove <emphasis>all</emphasis> rows from the given table, leaving it |
| 915 |
empty. The system will not request confirmation before |
| 916 |
doing this! |
| 917 |
</para> |
| 918 |
</sect1> |
| 919 |
|
| 920 |
</chapter> |