Outer Join

         It returns matched and unmatched records from both (Left and Right) tables based on given Join condition. These are three types.

• Left Outer Join
• Right Outer Join
• Full Outer Join

Left Outer Join: 
        It returns all records from left table and only matched records from right table. Based on given condition.

Example:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            LEFT OUTER JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID

Right Outer Join: 
        It returns all records from right table and only matched records from left table. Based on given condition.

Example:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            RIGHT OUTER JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID

Full Outer Join: 
        It returns all matched and unmatched records from both the tables. Based on given condition.

Example:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            FULL OUTER JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID