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 join query. As an example, say we wish to find all the records that are in the temperature range of other records. In effect, we need to compare the temp_lo and temp_hi columns of each WEATHER row to the temp_lo and temp_hi columns of all other WEATHER columns.
Note: This is only a conceptual model. The actual join may be performed in a more efficient manner, but this is invisible to the user.
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 |
+--------------+-----+------+---------------+-----+------+
|San Francisco | 37 | 54 | San Francisco | 46 | 50 |
+--------------+-----+------+---------------+-----+------+
Note: The semantics of such a join are that the qualification is a truth expression defined for the Cartesian product of the tables indicated in the query. For those rows in the Cartesian product for which the qualification is true, Postgres computes and returns the values specified in the target list. Postgres SQL does not assign any meaning to duplicate values in such expressions. This means that Postgres sometimes recomputes the same target list several times; this frequently happens when Boolean expressions are connected with an "or". To remove such duplicates, you must use the SELECT DISTINCT statement.
In this case, both W1 and W2 are surrogates for a row of the table weather, and both range over all rows of the table. (In the terminology of most database systems, W1 and W2 are known as range variables.) A query can contain an arbitrary number of table names and surrogates.