Joins Not Based on Equality

You can join values in two columns that are not equal. The following comparison operators can be used:



Symbol	Meaning

>	Greater than
> =	Greater than or equal to
<	Less than
< =	Less than or equal to
< >	Not equal to
!>	Not greater than
!<	Not lesser than

This example of a greater-than (>) join finds New Moon authors who live in states that come alphabetically after Massachusetts, where New Moon Books is located:

SELECT pub_name, publishers.state, au_lname, au_fname, authors.state
FROM publishers, authors
WHERE authors.state > publishers.state
    AND pub_name = 'New Moon Books'

pub_name	state	au_lname	au_fname	state
--------------	-----	--------------	-----------	-----
New Moon Books	MA	Greene	Morningstar	TN
New Moon Books	MA	Blotchet-Halls	Reginald	OR
New Moon Books	MA	del Castillo	Innes	MI
New Moon Books	MA	Panteley	Sylvia	MD
New Moon Books	MA	Ringer	Anne	UT
New Moon Books	MA	Ringer	Albert	UT

(6 row(s) affected)

The following example uses a greater-than-or-equal-to join (>=) and a less-than join (<) to print the royalty columns from the titles and roysched tables:

SELECT t.title_id, t.ytd_sales, r.royalty, t.royalty
FROM titles t, roysched r
WHERE t.title_id = r.title_id
AND t.ytd_sales >= r.lorange AND t.ytd_sales < r.hirange

title_id ytd_sales   royalty     royalty     
-------- ----------- ----------- ----------- 
BU1032   15          10          10          
MC3021   40          10          24          
PS2091   30          10          12          

(3 row(s) affected)