-- Outer self join is a type of SQL join where a table is joined to itself using an outer join 
-- LEFT, RIGHT OR FULL JOIN 

-- Usefull when you want to compare rows within the same table and still include rows that dont have a match 

CREATE TABLE Employees ( 
    EmployeeID INT PRIMARY KEY, 
    Name VARCHAR(50), 
    ManagerID INT
    ); 


INSERT INTO Employees( EmployeeID, Name, ManagerID) VALUES 
    (1, 'Alice', NULL), 
    (2, 'Bob', 1),
    (3, 'Charlie', 1), 
    (4, 'Diana', 2), 
    (5, 'Evan', NULL); 



-- e1 is the alias for the employee row 
-- e2 is the alias for the manager row
SELECT e1.EmployeeID AS EmployeeID, e1.Name AS EmployeeName, e2.Name AS ManagerName 
FROM Employees e1 
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID; 


CREATE TABLE Projects ( 
    ProjectID INT PRIMARY KEY, 
    ProjectName VARCHAR(100), 
    DependsONProjectID INT
    );
-- The reason the last alias is p2 and not p3 is because a self-join only involves two references to the same table 
-- Even if the relationship conceptually feels like multiple levels. 
INSERT INTO Projects(ProjectID, ProjectName, DependsONProjectID) VALUES 
(1, 'Website Design', NULL), 
(2, 'SEO Optimization', 1), 
(3, 'Mobile App', 1), 
(4, 'API Integration', 3), 
(5, 'Analytics Setup', NULL);

SELECT
    p1.ProjectID AS ProjectID,
    p1.ProjectName AS ProjectName,
    p2.ProjectName AS DependsOn
FROM 
    Projects p1
LEFT JOIN 
    Projects p2
ON 
    p1.DependsOnProjectID = p2.ProjectID;


-- FULL JOIN and FULL OUTER are the same thing
-- Both return all rows and tables, matching them where possible and filling NULL where there is no match. 

CREATE TABLE DepartmentA ( 
    DeptID INT PRIMARY KEY, 
    DeptName VARCHAR(50)
    );

CREATE TABLE DepartmentB (
    DeptID INT PRIMARY KEY, 
    DeptName VARCHAR(50)
    );

INSERT INTO DepartmentA(DeptID, DeptName)VALUES 
(1, 'HR'), 
(2, 'Finance'), 
(3, 'IT'); 

INSERT INTO DepartmentB(DeptID, DeptName)VALUES 
(2, 'Finance'), 
(3, 'IT'), 
(4, 'HR'); 


SELECT
    A.DeptID AS DeptA_ID,
    A.DeptName AS DepartmentAName, 
    B.DeptID AS DeptB_ID, 
    B.DeptName AS DepartmentBName
FROM 
    DepartmentA A
FULL OUTER JOIN 
    DepartmentB B
ON 
    A.DeptID = B.DeptID; -- Will not work because SQL doesn't support full outer join