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 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 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
Be careful when interpreting the results of a not-equal join. For example, you may think you can use a not-equal join to find the authors who live in a city where no publisher is located:
select distinct au_lname, authors.city from publishers, authors where publishers.city != authors.city
However, this query finds the authors who live in a city where no publishers are located, which is all of them. The correct SQL statement is a subquery:
select distinct au_lname, authors.city from publishers, authors where authors.city not in (select city from publishers where authors.city = publishers.city)
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
