Joins Based on Comparison Operators

Go Up to Using Inner Joins

Inner joins can compare values in join columns using other comparison operators besides the equality operator. For example, a join might be based on a column in one table having a value less than the value in a column in another table. The ON clause in a comparison join always takes the form:

ON t1.column <operator> t2.column

where <operator> is a valid comparison operator. For a list of valid comparison operators, see Using Comparison Operators in Expressions.

For example, the following join returns information about provinces in Canada that are larger than the state of Alaska in the United States:

EXEC SQL
DECLARE BIGPROVINCE CURSOR FOR
SELECT S.STATE_NAME, S.AREA, P.PROVINCE_NAME, P.AREA
FROM STATES S JOIN PROVINCE P ON P.AREA > S.AREA AND
P.COUNTRY = 'Canada'
WHERE S.STATE_NAME = 'Alaska';

In this example, the first comparison operator in the ON clause tests to see if the area of a province is greater than the area of any state (the WHERE clause restricts final output to display only information for provinces that are larger in area than the state of Alaska).