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" |
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); |
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); |
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); |
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 |
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; |
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 |
ORDINAL_ENGLISH_TEXT ORDINAL_FRENCH_TEXT
-------------------------- --------------------------
One Trois
Two Trois