Self Join

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

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

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

Known Issues

No issues were found.

Related EWIs

No related EWIs.