The Not-Equal Join

A not-equal join is useful for 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