The term join applies to any query that combines data from two tables by comparing the values in a pair of columns from the tables. Although joins based on equality between matching columns (equi-joins) are by far the most common joins, SQL also allows you to join tables based on other comparison operators. Here’s an example where a greater than (>) comparison test is used as the basis for a join:
List all combinations of salespeople and offices where the salesperson’s quota is more than the office’s target.
SELECT NAME, QUOTA, CITY, TARGET
FROM SALESREPS, OFFICES
WHERE QUOTA > TARGET
As in all two-table queries, each row of the query results comes from a pair of rows, in this case from the SALESREPS and OFFICES tables. The search condition:
QUOTA > TARGET
selects pairs of rows where the QUOTA column from the SALESREPS row exceeds the TARGET column from the OFFICES row. Note that the pairs of SALESREPS and OFFICES rows selected are related only in this way; it is specifically not required that the SALESREPS row represent someone who works in the office represented by the OFFICES row. Admittedly, the example is a bit farfetched, and it illustrates why joins based on inequalities are not very common. However, they can be useful in decision- support applications and other applications that explore more complex interrelationships in the database.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.