ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Explicit join conditions (the ON phrase)

Types of explicit join conditions

Most join conditions are based on equality, and so are called equijoins. For example,

SELECT *
FROM department JOIN employee
   ON department.dept_id = employee.dept_id

However, you do not have to use equality (=) in a join condition. You can use any search condition, such as conditions containing LIKE, SOUNDEX, BETWEEN, > (greater than), and != (not equal to).

Example 

The following example answers the question: For which products has someone ordered more than the quantity in stock?

SELECT DISTINCT product.name
FROM product JOIN sales_order_items
ON product.id = sales_order_items.prod_id
   AND product.quantity > sales_order_items.quantity

For more information about search conditions, see Search conditions.