Self Joins

For example:

SELECT a.c0002title, b.c0002title, a.c0002rating, a.c0002category
FROM t0002film a, t0002film b
WHERE a.c0002rating = b.c0002rating
AND a.c0002category = b.c0002category
AND a.c0002id ! = b.c0002id
ORDER BY 1,2;

A self-join allows a SELECT statement to compare data from the same table. The self-join query requires the table to be named at least twice in the FROM clause, each occurence being given a different alias. The WHERE clause performs the comparisons between the data.

The example compares films that have the same certificate ratings and categories. The final condition stops each film being compared with itself.i

The ROWID may have been used instead of the film number in the above example. is extremely useful if the Primary Key of a table consists of more than one column.

Self-joins may be used to identify duplicate information. For example,

SELECT a.ROWID, b.ROWID, a.c0002title
FROM t0002film a, t0002film b
WHERE a.c0002title = b.c0002title
AND a.ROWID ! = b.ROWID;

Hence, if two rows have to same film title, the ROWIDs of each, as well as the title is print