Non-equijoins differ from equijoins because they use either an inequality operator or a range operator. They are always a filtered outcome of a CROSS JOIN between tables. A CROSS JOIN returns all possibilities between rows of two tables. You can filter that Cartesian result set by comparing the inequality relationship between a column in each table or a set of like columns in each table. Likewise, you can filter the Cartesian result set by comparing whether a column value in one table is BETWEEN two columns in the other table.
- Range non-equijoin
- A range non-equijoin typically compares whether a column value in one table is found between a pair of column values in another table.
- Inequality non-equijoin
- An inequality non-equijoin typically compares an inequality relationship between a column value in one table against a column value in another table. It may also use an
AND or OR logical operator to make multiple column comparisons.
The following scrollable list exams the range and inequality syntax for non-equijoins. They’re defined in the ANSI SQL:89 definition and are generally portable across database implementations.
Non-equijoin Joins ↓
Rule(s):
- A non-equijoin doesn’t have an equijoin statement.
- A non-equijoin that uses the
CROSS JOIN doesn’t have a join in the FROM clause.
- A non-equijoin that uses the
INNER JOIN supports resolution through the ON clause.
- A non-equijoin that uses a comma separated list of tables doesn’t have an equijoin in the
WHERE clause.
Range Join
This example creates an non-equijoin where the comparison of values looks for the occurrence of values in an inclusive range of values. The syntax that uses CROSS JOIN is known as ANSI SQL:92. The syntax using a comma separated list of tables is sometimes called Oracle Proprietary but is really only an older version of ANSI SQL:89 syntax because it isn’t using any of the Oracle extensions. Both are shown in the example.
Oracle allows you to format the data like a SQL Report Writer by setting the column definitions, like:
1
2
| COLUMN month_short_name FORMAT A5 HEADING "Month|Name"
COLUMN transaction_amount FORMAT 99.00 HEADING "Amount" |
This example uses a traditional model with CALENDAR_JOIN and TRANSACTION_JOIN tables. The CALENDAR_JOIN table sets the range by qualifying the starting and ending dates of a month. The TRANSACTION_JOIN table provides the transaction date. A range non-equijoin filters a Cartesian Product or cross join result set and returns those rows where the search value is inside the range. It is important to note that neither range value may be a null value (check the SQL Functions post for more detail).
1
2
3
4
5
| SELECT c.month_short_name
, t.transaction_amount
FROM calendar_join c, transaction_join t
WHERE t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date); |
You can also interchangeably use either of the two ANSI SQL:92 syntax resolutions. The first uses a CROSS JOIN, like:
1
2
3
4
5
| SELECT c.month_short_name
, t.transaction_amount
FROM calendar_join c CROSS JOIN transaction_join t
WHERE t.transaction_date BETWEEN c.start_date AND c.end_date
ORDER BY EXTRACT(MONTH FROM t.transaction_date); |
The second uses an INNER JOIN with the ON operator, like:
1
2
3
4
5
| SELECT c.month_short_name
, t.transaction_amount
FROM calendar_join c INNER JOIN transaction_join t
ON (t.transaction_date BETWEEN c.start_date AND c.end_date)
ORDER BY EXTRACT(MONTH FROM t.transaction_date); |
Either returns the following result set:
Month
Name Amount
----- ------
JAN 32.87
JAN 38.99
MAR 9.99
APR 43.19
Inequality Join
Inequality non-equijoins are less frequently used than range non-equijoins but sometimes they’re needed to work with data. The >, >=, <, and <= are the SQL inequality operators. They let you compare the relationship between columns in different tables when you filter a CROSS JOIN or Cartesian result set.
The following query lets you find all values less than an ORDINAL_FRENCH_ID value of 3:
1
2
3
4
5
| SELECT ordinal_english_text
, ordinal_french_text
FROM ordinal_english CROSS JOIN ordinal_french
WHERE ordinal_english_id < ordinal_french_id
AND ordinal_french_id = 3; |
It returns the following:
ORDINAL_ENGLISH_TEXT ORDINAL_FRENCH_TEXT
-------------------------- --------------------------
One Trois
Two Trois
