Non-equijoins

Equijoins , the most common type of join, use equality (=) to join tables. Figure  shows our first non-equijoin.

 

        SELECT c2.name
        FROM   customer c, customer c2
        WHERE  c.customer_id = 648 AND
               c.country <> c2.country
        ORDER BY c2.name; 
         
        SELECT e2.name, e2.hire_date
        FROM   employee e, employee e2
        WHERE  e.employee_id = 24 AND
               e.hire_date < e2.hire_date
        ORDER BY e2.hire_date, e2.name; 
         
        SELECT p2.name, p2.cost
        FROM   part p, part p2
        WHERE  p.part_id = 153 AND
               p.cost > p2.cost
        ORDER BY p2.cost;  

The first query uses not equal (<>) to perform the join. It returns all customers not in the same country as customer number 648. The second query uses less than (<) to perform the join. Instead of finding equal values to join, it joins all rows later than a specific hire date. The query returns all employees hired after employee number 24. The third query uses greater than (>) in a similar way. It returns all parts that cost less than part number 153. Non-equijoins are not used often, but certain queries require them.