NON-EQUIJOIN

a non-equijoin is a join condition that does not use the equality operator (=) to match rows between tables. Instead, it uses other comparison operators like <, >, <=, >=, or even != for the join condition.

  1. The main part of the query selects the starting and ending periods, along with the count of transactions within each period. The periods are defined by joining the TBL_TRANSACTION table (T) with a derived table containing specific date ranges.
  2. The derived table is created using the UNION ALL clause to combine the results of several SELECT statements. Each SELECT statement in the union defines a date range by specifying a starting (Starting_Period) and ending (Ending_Period) date. These date ranges are defined using the DATE keyword in the format DATE 'YYYY-MM-DD' and are selected from the DUAL table, which is a dummy table in Oracle databases.
  3. The main query uses the derived table and joins it with the TBL_TRANSACTION table (T) using the conditions in the ON clause. The condition ON t.date_of_transaction >= Starting_Period AND T.DATE_OF_TRANSACTION < Ending_Period + 1 ensures that each transaction falls within the specified date range.
  4. The COUNT(*) function is used to count the number of transactions within each period.
  5. The result set is grouped by the Starting_Period and Ending_Period columns using the GROUP BY clause.
  6. The final result set is ordered by Starting_Period and Ending_Period using the ORDER BY clause.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top