Database Tutorial

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