Joins Not Based on Equality

The condition for joining the values in two columns does not need to be equality. You can use any of the other comparison operators: not equal (!=), greater than (>), less than (<), greater than or equal to (>=), and less than or equal to (<=).

Transact-SQL also provides the operators !> and !<, which are equivalent to <= and >=, respectively.      

This example of a greater-than join finds New Age authors who live in states that come after New Age Books’ state, Massachusetts, in alphabetical order.

select pub_name, publishers.state, 
   au_lname, au_fname, authors.state 
from publishers, authors 
where authors.state > publishers.state 
and pub_name = "New Age Books" 
pub_name         state   au_lname       au_fname         state 
-------------    ------  -------------- -----------      ----- 
New Age Books    MA      Greene         Morningstar      TN
New Age Books    MA      Blotchet-Halls Reginald         OR
New Age Books    MA      del Castillo   Innes            MI
New Age Books    MA      Panteley       Sylvia           MD
New Age Books    MA      Ringer         Anne             UT 
New Age Books    MA      Ringer         Albert           UT 
 
(6 rows affected) 

The following example uses a >= join and a < join to look up the correct royalty from the roysched table, based on the book’s total sales.

select t.title_id, t.total_sales, r.royalty 
from titles t, roysched r 
where t.title_id = r.title_id 
and t.total_sales >= r.lorange 
and t.total_sales < r.hirange 
title_id       total_sales      royalty
--------       -----------      ------- 
BU1032               4095           10  
BU1111               3876           10  
BU2075               1872           24  
BU7832               4095           10  
MC2222               2032           12  
MC3021              22246           24  
PC1035               8780           16  
PC8888               4095           10  
PS1372                375           10  
PS2091               2045           12  
PS2106                111           10  
PS3333               4072           10  
PS7777               3336           10  
TC3218                375           10  
TC4203              15096           14  
TC7777               4095           10  
 
(16 rows affected)