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)
