Joins

          Join combines records from two or more tables in a database. Below are the different types of joins

Types of Joins:

• Inner Join
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join
• Self Join

Cross Join

          Cross join produces the Cartesian product of the two tables (left and right). This cross join does not have a Where clause. This product result set is the number of rows in the first table multiplied by the number of rows in the second table.

If EMP table is having 5 records and table DEPT is having 3 records. In Cross Join output will come as 15 records.

Example:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            CROSS JOIN DEPT B

Self Join

      A table can be joined to itself in a self join. In self join left and right tables are same with different alias name.If we need to create a result set that joins records in a table with other records in the same table, then we can use self join.

Example: I need to display all employee name along with their manager name.
Sample Emp table:
E_ID    DEPT_ID    E_NAME    MANAGER_ID
1          10                A                 NULL
2          20                B                 1
3          30                C                 2


Query:
       SELECT
          a1.E_NAME
           ,b2.E_NAME AS MANAGER_NAME
          FROM EMP a1
          LEFT JOIN EMP b2
          ON a1.MANAGER_ID = b2.E_ID

Output:

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

Inner Join

       It returns all matched records from both (Left and right) tables, based on given condition.

Example: Below three queries return same result.

    1. Using on condition:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            INNER JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID
 
      2. Using Where condition:
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A,
            DEPT B
            WHERE A.Dept_ID = B.Dept_ID

      3. We can  mention 'JOIN' instead of  'INNER JOIN'.
            SELECT
            A.ID
            ,A.First_Name
            ,A.Last_Name
            ,A.Dept_ID
            ,B.Dept_Name
            FROM EMP A
            JOIN DEPT B
            ON A.Dept_ID = B.Dept_ID