SQL-JOIN

         When we want to fetch data from two or more than two tables then we go for joins. Basically JOIN is an operation which combines results from two or more tables. We apply JOINS on related tables. JOINS are classified as below:

•                EQUI JOIN / INNER JOIN
•                OUTER JOIN                         

                                         LEFT OUTER JOIN
                                         RIGHT OUTER JOIN     
                                         FULL JOIN

•                 SELF JOIN 
•                 CROSS JOIN 

 INNER JOIN 

         INNER JOIN is also known as EQUI JOIN. It combines the data from two or more tables based on the JOIN conditions. It displays all the matching records and eliminates all non matching records. 

Let us consider below two tables for illustrations.

CustomerInfo Table

CustomerInfo Table

 ProdctInfo Table

ProductInfo Table

             Now , we will make a inner join between CustomerInfo Table and ProductInfo Table by writing the following below query.


                                 SELECT Name, ProductName,Quantity, MRP
                                 FROM CustomerInfo C
                                 INNER JOIN ProductInfo P
                                 ON C.ProductID=P.ProductID

         We are selecting Name, ProductName, Quantity from CustomerInfo Table and MRP from ProductInfo Table based on the common ProductID present in the both table. The output is as follows.

INNER_JOIN

       We see that 7 matching records have been displayed while the other 2 non matching records have been discarded.
           While selecting column names, it is necessary to use table name alias along with dot operator to the column names when that column name is present in both tables. In such condition, SQL does not know from which table the data has to be picked thus making it ambiguous. To avoid such conditions, table alias is used.

  To understand INNER JOIN even better, we will make use of venn diagram.

   CustomerInfo and ProductInfo are two table shown in venn diagram. Shaded region represents the INNER JOIN. It is the region which is common to both the tables.

INNER_JOIN

LEFT OUTER JOIN

We go for LEFT OUTER JOIN  when we want all the matching rows from both table and non matching rows from left table.

   Now , we will make a left outer join between CustomerInfo Table and ProductInfo Table by writing the following below query.

 SELECT Name, ProductName,Quantity, MRP
 FROM CustomerInfo C
 LEFT OUTER JOIN ProductInfo P
 ON C.ProductID=P.ProductID

    We are selecting Name, ProductName, Quantity from CustomerInfo Table and MRP from ProductInfo Table in such a way that all matching rows between both table and non matching rows from left table gets selected based on the common ProductID present in the both table. The output is as follows.

LEFT OUTER JOIN

       We see that 7 matching records from both tables and 2 non matching records from left table has been displayed.

To understand LEFT OUTER JOIN even better, we will make use of venn diagram.

   CustomerInfo and ProductInfo are two table shown in venn diagram. Shaded region represents the LEFT OUTER JOIN.

 

LEFT OUTER JOIN

RIGHT OUTER JOIN

We go for RIGHT OUTER JOIN  when we want all the matching rows from both table and non matching rows from right table.

   Now , we will make a right outer join between CustomerInfo Table and ProductInfo Table by writing the following below query.

 SELECT Name, ProductName,Quantity, MRP
 FROM CustomerInfo C
 RIGHT OUTER JOIN ProductInfo P
 ON C.ProductID=P.ProductID

    We are selecting Name, ProductName, Quantity from CustomerInfo Table and MRP from ProductInfo Table in such a way that all matching rows between both table and non matching rows from right table gets selected based on the common ProductID present in the both table. The output is as follows.

 

RIGHT OUTER JOIN

      We see that 7 matching records from both tables and 1 non matching records from right table has been displayed.

To understand RIGHT OUTER JOIN even better, we will make use of venn diagram.

   CustomerInfo and ProductInfo are two table shown in venn diagram. Shaded region represents the RIGHT OUTER JOIN.

 

RIGHT OUTER JOIN

FULL OUTER JOIN

      We go for FULL OUTER JOIN  when we want all the matching rows from both table and non matching rows from left table as well as right table.

   Now , we will make a full outer join between CustomerInfo Table and ProductInfo Table by writing the following below query.

 SELECT Name, ProductName,Quantity, MRP
 FROM CustomerInfo C
 FULL OUTER JOIN ProductInfo P
 ON C.ProductID=P.ProductID

    We are selecting Name, ProductName, Quantity from CustomerInfo Table and MRP from ProductInfo Table in such a way that all matching rows between both table and non matching rows from right table as well as left table gets selected based on the common ProductID present in the both table. The output is as follows.

FULL OUTER JOIN

    We see that 7 matching records from both tables , 2 non matching records from left table and 1 non matching records from right table has been displayed. 

To understand FULL OUTER JOIN even better, we will make use of venn diagram.

   CustomerInfo and ProductInfo are two table shown in venn diagram. Shaded region represents the FULL OUTER JOIN.

FULL OUTER JOIN

 SELF JOIN:
 
     When a table is joined with itself then it is called SELF JOIN. The key point to remember here is that self join can be inner join  or  outer join or cross join whichever is required.We should  never use the word 'SELF JOIN' while writing a query. In self join , single table is treated as two table by giving two different table alias.
        Self Join can be explained well with example. Let us take Employee table. Table has EmployeeID, EmployeeName and MangerID. ManagerID is a foreign key to EmployeeID referring in the same table. Now we want each EmployeeName and their corresponding Manager name as output .

Employee Table

        First,  we will take an example of  INNER  SELF JOIN . Here , we will select EmployeeName from Employee table giving alias as EMP and  ManagerName from Manager table which is nothing but a Employee table giving alias as Mng and then make a join condition on the ManagerID column of Employee table and EmployeeID of Manager table.

SELECT Emp.EmployeeName,
Mng.EmployeeName AS ManagerName 
FROM Employee Emp
INNER JOIN Employee Mng
ON Emp.ManagerID=Mng.EmployeeID

INNER SELF JOIN

    We see that all employee name except one employee  is displayed along with their Manager's name. We see that 'Shweta ' name and her Manager name is not displayed because her manger id is not present among employee id list. Therefore when we make inner join , it displays only matching and ignores non matching records. But if we go for left outer self join , we will be able fetch all employee name including 'Shweta' name along with their manager's name. This is so because left join always returns matching records and non matching records from left table. Left outer self join  is illustrated below.

SELECT Emp.EmployeeName,
Mng.EmployeeName AS ManagerName 
FROM Employee Emp
LEFT JOIN Employee Mng
ON Emp.ManagerID=Mng.EmployeeID

LEFT OUTER SELF JOIN

CROSS JOIN

     Cross join gives the cartesian product of the tables involved. It does not have join condition. If first table has 5 records and second table has 5 records, CROSS JOIN will produce 5 multiply 5  which is 25 rows. In other words , each row of first table joins with all records of second  table. Let us see the below example.

 Class Table & School table

Class Table & School Table

  We see that both Class and School table has 5 records each. Let us execute the below query for cross join.

SELECT * FROM Class 
CROSS JOIN
School 

CROSS JOIN 

So the cross join has should produced 25 rows.