The not-equal join is particularly useful in restricting the rows returned by a self-join. In the following example, a not-equal join and a self-join find the categories in which there are 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
type price ---------- ----- business 2.99 business 11.95 psychology 7.00 psychology 7.99 psychology 10.95 trad_cook 11.95 trad_cook 14.99 (7 rows affected)
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. It finds all the rows in the titleauthor table where there are two or more rows with the same title_id, but different au_id numbers, that is books that have multiple authors.
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 order by t1.title_id
au_id title_id ----------- -------- 213-46-8915 BU1032 409-56-7008 BU1032 267-41-2394 BU1111 724-80-9391 BU1111 722-51-5454 MC3021 899-46-2035 MC3021 427-17-2319 PC8888 846-92-7186 PC8888 724-80-9391 PS1372 756-30-7391 PS1372 899-46-2035 PS2091 998-72-3567 PS2091 267-41-2394 TC7777 472-27-2349 TC7777 672-71-3249 TC7777 (15 rows affected)
For each book in titles, the following example finds all other books of the same type that have a different price:
select t1.type, t1.title_id, t1.price, t2.title_id, t2.price from titles t1, titles t2 where t1.type = t2.type and t1.price != t2.price
