SELF JOIN

In my experience, the SELF JOIN is almost as rare as the CROSS JOIN. This is due to the limited opportunities existing in most data to actually make use of it. It is particularly useful when attempting to capture hierarchal relationships or simply when there is a need to capture data, existing across multiple rows, within a single row. While this join type is often referred to as a SELF JOIN, in practice, it’s a LEFT JOIN with the same table on both sides of the LEFT JOIN keywords. Each instance requires a different name, so table aliases are a must. This eliminates the table and column ambiguity that would otherwise occur in the ON and SELECT clauses.

One common example can be found within the data responsible for capturing information on a company’s products and the corresponding product categories. Each product belongs within a category and that category may roll-up to another, broader category. The purpose of this broader or parent category is to group related categories together. Another common example can be found in human resource data where there exists a relationship between managers and employees. Typically, a manager has one or more employees, and an employee directly reports to one manager.

Let’s explore both examples, individually, and then walk through an example that is not so common. The data used in the first two examples on this page can be found at livesql.oracle.com.

Product Category Relationships
In this example, we’ll use the categories table below. Within this table, there is one row per product category. Within each row, the parent category ID value associated with the specific category’s parent category, if one exists, is recorded. By having the parent category ID within each row, we can gather each category’s parent information by traversing the same table and finding where the parent category ID exists as a category ID. For example, the category, “hardware1” has a parent category ID value of 10. In the row above, this parent category ID value can be found within the category ID column. Therefore, “hardware” is the parent category of “hardware1” and has a category description of “computer hardware and peripherals.”

This same process can be carried out manually for each category; however, the goal is to approach this programmatically by using two aliased instances of the categories table in a LEFT JOIN operation. The ON clause will contain the same instructions we processed manually when traversing the rows for matches. When a match is found, the category’s parent information, if it exists, will be represented within the same row, side by side.

Below is the code used to achieve this task and the corresponding result set. I’ve ordered the result set by the child category ID for readability.

-- Retrieve each category and category's parent category information within the same row.
SELECT
    child_category.category_id,
    child_category.category_name,
    child_category.category_description,
    parent_category.category_id AS parent_category_id,
    parent_category.category_name AS parent_category_name,
    parent_category.category_description AS parent_category_description
FROM
    oe.categories_tab child_category
    LEFT JOIN
    oe.categories_tab parent_category
        ON child_category.parent_category_id = parent_category.category_id
ORDER BY
    child_category.category_id;

Notice how the row corresponding to category ID 90 (i.e., online catalog) doesn’t contain parent category information. This is because it doesn’t have one.

Employee-Manager Relationships
In this example, we’ll use the employees table below. Within this table, there is one row per employee. Within each row, the manager ID value associated with the specific employee’s manager is recorded. The absence of a manager ID value is indicative of an employee not reporting directly to another employee, or more simply put, an unmanaged employee. Because each employee’s manager ID value is captured within the same row, we can gather the employee’s manager information by traversing the same table and finding the row where the manager ID value exists as an employee ID value. For example, Neena Kochhar (i.e., employee ID 101) reports to the employee with an employee ID value of 100. Based on the table, this employee ID value corresponds to Steven King.

Like the first example on product category relationships, this manual process can be approached programmatically. See the code used to do so and the corresponding result set, below. For simplicity, only a subset of the preexisting columns or attributes are included for each employee and corresponding manager.

-- Retrieve each employee's and corresponding manager's information.
SELECT
	hr.employees.employee_id,
	hr.employees.first_name,
	hr.employees.last_name,
	hr.employees.salary AS salary,
	manager.employee_id AS manager_id,
	manager.first_name AS manager_first_name,
	manager.last_name AS manager_last_name,
	manager.salary AS manager_salary
FROM
	hr.employees
	LEFT JOIN
	hr.employees manager
		ON  hr.employees.manager_id = manager.employee_id;

Step-rate Mortgages
A step-rate mortgage is one where the interest rate increases in a series of steps over time and for a specific duration. These steps and corresponding details are often conveyed in a schedule or step-rate addendum. In the table below, there exists a handful of mortgage loan account numbers and the associated step rate addendum data. There is one row per account number and step number. All data is fictional.

Now, imagine you’re given the following task: for each account and step number, compare the current step’s data with the next step’s data by displaying both steps’ data in a single row. In addition, provide the amount the principal and interest increased from step to step based on the comparison of principal and interest amounts for the steps. That is, compute the difference between the next step’s principal and interest amount and the current step’s principal and interest amount. Again, your solution should contain the step’s data and the subsequent step’s data, if one exists, in a single row for quick comparison.

Unlike the first two examples where a relationship between two rows was explicitly captured in a single row (e.g., an employee ID and corresponding manager ID), here the relationship between two or more rows is implied by the step numbers. Each account’s step numbers are sequential, one followed by two, two followed by three, and so forth. This fact can be relied upon when developing a solution to the task. Specifically, it can be added to the ON clause to ensure the necessary step comparisons are made by stitching together the appropriate rows (e.g., 1 compared to 2, 2 compared to 3, etc.). The first condition necessary in the ON clause is straightforward. The comparisons need to be made using step data from the same account number. The second and not so common condition is the step number in the current row is equal to the next step number less one. When attempting to make sense of the second condition, it’s important to remember two instances of the same table are being used.

One possible solution and corresponding result set can be found below. Keep in mind, with the advent of window or analytic functions, this problem can be solved in an arguably more intuitive manner. Row reference functions are not the focus of this page; however, an alternative solution utilizing them can be found below the initial solution.

-- Using LEFT JOIN to solve.
SELECT
    current_step.acct_num,
    current_step.step_num AS current_step_num,
    current_step.int_rate AS current_int_rate,
    current_step.pi_amt AS current_pi_amt,
    next_step.step_num AS next_step_num,
    next_step.int_rate AS next_int_rate,
    next_step.pi_amt AS next_pi_amt,
    next_step.pi_amt - current_step.pi_amt AS amt_increase
FROM
    pi_loan current_step
    LEFT JOIN
    pi_loan next_step
        ON current_step.acct_num = next_step.acct_num
        AND current_step.step_num = (next_step.step_num - 1)
ORDER BY
    current_step.acct_num, current_step.step_num;

-- Using Window/analytic function to solve.
SELECT
    pi_loan.acct_num,
    pi_loan.step_num AS current_step_num,
    pi_loan.int_rate AS current_int_rate,
    pi_loan.pi_amt AS current_pi_amt,
    LEAD(pi_loan.step_num, 1, NULL) OVER(
    	PARTITION BY pi_loan.acct_num
    	ORDER BY pi_loan.step_num
    ) AS next_step_num,
    LEAD(pi_loan.int_rate, 1, NULL) OVER(
        PARTITION BY pi_loan.acct_num
        ORDER BY pi_loan.step_num
    ) AS next_int_rate,
    LEAD(pi_loan.pi_amt, 1, NULL) OVER(
        PARTITION BY pi_loan.acct_num
        ORDER BY pi_loan.step_num
    ) AS next_pi_amt,
    (
    LEAD(pi_loan.pi_amt, 1, NULL) OVER(
        PARTITION BY pi_loan.acct_num
        ORDER BY pi_loan.step_num
    )
    - pi_loan.pi_amt
    ) AS amt_increase
FROM
    pi_loan
ORDER BY
    pi_loan.acct_num,
    pi_loan.step_num;

The SELF JOIN has a special place in my heart due to the initial difficulty I felt when attempting to understand it. In the beginning, the ON clause simply didn’t click for me. Hopefully, as a result of reading this, you are able to avoid the headache I endured or at least minimize the headache’s duration.

Knowledge Check
If you’d like to confirm your understanding on JOINS and other fundamental concepts, consider solving a handful of my practice problems here.