following query:

<programlisting>

SELECTW1.city,W1.temp_lo AS low,W1.temp_hi AS high,

W2.city,W2.temp_lo AS low,W2.temp_hi AS high

FROM weatherW1, weatherW2

WHEREW1.temp_lo &lt;W2.temp_lo

ANDW1.temp_hi &gt;W2.temp_hi;

SELECTw1.city,w1.temp_lo AS low,w1.temp_hi AS high,

w2.city,w2.temp_lo AS low,w2.temp_hi AS high

FROM weatherw1, weatherw2

WHEREw1.temp_lo &lt;w2.temp_lo

ANDw1.temp_hi &gt;w2.temp_hi;

</programlisting>

<screen>

(2 rows)

</screen>

Here we have relabeled the weather table as <literal>W1</literal> and

<literal>W2</literal> to be able to distinguish the left and right side

Here we have relabeled the weather table as <literal>w1</literal> and

<literal>w2</literal> 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.:

<programlisting>

FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

-- Suppose we want to find all the records that are in the temperature range

-- of other records.W1 andW2 are aliases for weather.

-- of other records.w1 andw2 are aliases for weather.

SELECTW1.city,W1.temp_lo,W1.temp_hi,

W2.city,W2.temp_lo,W2.temp_hi

FROM weatherW1, weatherW2

WHEREW1.temp_lo <W2.temp_lo

andW1.temp_hi >W2.temp_hi;

SELECTw1.city,w1.temp_lo,w1.temp_hi,

w2.city,w2.temp_lo,w2.temp_hi

FROM weatherw1, weatherw2

WHEREw1.temp_lo <w2.temp_lo

andw1.temp_hi >w2.temp_hi;

-----------------------------