Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 4 Joins: Retrieving Data from Several Tables

The not-equal join

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 which 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, it would be easy to think you could 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)

Not-equal joins and subqueries

Sometimes a not-equal join query is not sufficiently restrictive and needs to be replaced by a subquery. For example, suppose you want to list the names of authors who live in a city where no publisher is located. For the sake of clarity, let us also restrict this query to authors whose last names begin with "A", "B", or "C". A not-equal join query might be:

select distinct au_lname, authors.city 
from publishers, authors 
where au_lname like "[ABC]%" 
and publishers.city != authors.city

The results are not an answer to the question that was asked:

au_lname             city 
----------------     ------------ 
Bennet               Berkeley  
Carson               Berkeley  
Blotchet-Halls       Corvallis 
 
(3 rows affected)

The system interprets this version of the SQL statement to mean: "find the names of authors who live in a city where some publisher is not located." All the excluded authors qualify, including the authors who live in Berkeley, home of the publisher Algodata Infosystems.

In this case, the way that the system handles joins (first finding every eligible combination before evaluating other conditions) causes this query to return undesirable results. You must use a subquery to get the results you want. A subquery can eliminate the ineligible rows first and then perform the remaining restrictions.

Here is the correct statement:

select distinct au_lname, city 
from authors 
where au_lname like "[ABC]%" 
and city not in 
(select city from publishers 
where authors.city = publishers.city)

Now, the results are what you want:

au_lname             city                  
-------------        ------------
Blotchet-Halls       Corvallis             
 
(1 row affected)

Subqueries are covered in greater detail in Chapter 5, "Subqueries: Using Queries Within Other Queries."


	Expand Search