Self Join

Some parts in the output codes are omitted for clarity reasons.

Description

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. (Oracle SQL Language Reference Self Join Subsection)

Sample Source Patterns

Order by clause added because the result order may vary between Oracle and Snowflake.

Check this section to set up the sample database.

Basic Self Join case

Oracle

Query

IN -> Oracle_01.sql

SELECT e1.last_name||' works for '||e2.last_name 
   "Employees and Their Managers"
   FROM hr.employees e1, hr.employees e2 
   WHERE e1.manager_id = e2.employee_id
      AND e1.last_name LIKE 'R%'
   ORDER BY e1.last_name;

Result

Employees and Their Managers|
----------------------------+
Rajs works for Mourgos      |
Raphaely works for King     |
Rogers works for Kaufling   |
Russell works for King      |

Snowflake

Query

OUT -> Oracle_01.sql

SELECT
   NVL( e1.last_name :: STRING, '') || ' works for ' || NVL(e2.last_name :: STRING, '') "Employees and Their Managers"
FROM
   hr.employees e1,
   hr.employees e2
   WHERE e1.manager_id = e2.employee_id
      AND e1.last_name LIKE 'R%'
   ORDER BY e1.last_name;

Result

Employees and Their Managers|
----------------------------+
Rajs works for Mourgos      |
Raphaely works for King     |
Rogers works for Kaufling   |
Russell works for King      |

As proved previously the self join in Oracle is functionally equivalent to Snowflake.

Known Issues

No issues were found.

Related EWIs

No related EWIs.