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. ()
Sample Source Patterns
Order by clause added because the result order may vary between Oracle and Snowflake.
Check this to set up the sample database.
Basic Self Join case
Oracle
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;
Employees and Their Managers|
----------------------------+
Rajs works for Mourgos |
Raphaely works for King |
Rogers works for Kaufling |
Russell works for King |
Snowflake
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;
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.
