Creating Self-joins

Go Up to Using Inner Joins

A self-join is an inner join where a table is joined to itself to correlate columns of data. For example, the RIVERS table lists rivers by name, and, for each river, lists the river into which it flows. Not all rivers, of course, flow into other rivers. To discover which rivers flow into other rivers, and what their names are, theRIVERS table must be joined to itself:

EXEC SQL
DECLARE RIVERSTORIVERS CURSOR FOR
SELECT R1.RIVER, R2.RIVER
FROM RIVERS R1 JOIN RIVERS R2 ON R2.OUTFLOW = R1.RIVER
ORDER BY R1.RIVER, R2.SOURCE;

As this example illustrates, when a table is joined to itself, each invocation of the table must be assigned a unique correlation name (R1 and R2 are correlation names in the example). For more information about assigning and using correlation names, see Declaring and Using Correlation Names.