IBM PureData System for Analytics, Version 7.1

Self-join queries

A self-join query joins a table against itself.

For example, to find all the weather records that are in the temperature range of other weather records, you 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 rows:

MYDB.SCHEMA(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;

The example query relabels the weather table as W1 and W2 to distinguish between the left and right side of the join.