The Not-Equal Join

The not-equal join (< >) is rarely used. As a general rule, not-equal joins make sense only when used with a self-join. For example, this not-equal join and self-join are used to find the categories with two or more inexpensive (less than $15) books of different prices:

SELECT DISTINCT t1.type, t1.price
FROM titles t1, titles t2
WHERE t1.price < $15 AND t2.price < $15
    AND t1.type = t2.type
    AND t1.price <> t2.price

Note The expression NOT column_name = column_name is equivalent to column_name < > column_name.

The following example uses a not-equal join combined with a self-join to find all rows in the titleauthor table where two or more rows have the same title_id but different au_id numbers (that is, books with more than one author).

SELECT DISTINCT t1.au_id, t1.title_id
FROM titleauthor t1, titleauthor t2
WHERE t1.title_id = t2.title_id
    AND t1.au_id <> t2.au_id

au_id                title_id
-----------            --------
213-46-8915            BU1032
267-41-2394            BU1111
267-41-2394            TC7777
409-56-7008            BU1032
427-17-2319            PC8888
472-27-2349            TC7777
672-71-3249            TC7777
722-51-5454            MC3021
724-80-9391            BU1111
724-80-9391            PS1372
756-30-7391            PS1372
846-92-7186            PC8888
899-46-2035            MC3021
899-46-2035            PS2091
998-72-3567            PS2091

(15 row(s) affected)