sql joins with examples

Joins:

1)These are used to retrieve the data from more than one table.

2)To retrieve the data from more than one table the datatypes of fields which related to different tables need not be same while using the joins

Types of joins:

1):Inner Join

2):Cross Join

3)OuterJoin

     a)Left Outer Join

    b)Right Outer Join

    c)Full Outer Join

4)Natural Join

5)Equi Join

Examples and Description:

1:Emp

         EmployeeID  EmployeeName               

    1                Ramesh

    2                Sukumar

    3                Ravi                                                       

    4                Kalyani           

                                         

2.Products:

   ProductID            EmployeeID        Productname

  1 1                                  2                     Pen

  12                                   3                    Pencil

  1 2                                  3                    Eraser

  1 3                                  6                   Book

1):Inner Join:This join returns all the rows from the both the tables where there is a match.The result set consists of only matched rows.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E inner join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     

    2                  Sukumar                       Pen

    3                   Ravi                             Pencil                          

    3                   Ravi                             Eraser                            

2)Cross Join:Cross join is nothing but retrieving the data from more than one table with out using the condition.

Here two cases are there:

a)select E.EmployeeID,E.EmployeeName,P.Productname from Employees E,Products P

Note:(here we are using the cross join defaultly.Means we have not mentioned the any condition here.)

b)select E.EmployeeID,E.EmployeeName,P.Productname from Employees E cross join Products P

Note:this is the syantax of cross join..both queries(a &b)returns the same result) only the difference is Synatx but the o/p is same.

3)Outer Join:In outer join the resulting table may have empty columns.

a)Left Outer Join:Here left means first table.it reurns all the rows from the first table even though it does not have the matchs in Second table.But it returns only the matched rows from the second table.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E left join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     

    2                  Sukumar                       Pen

    3                   Ravi                             Pencil                          

    3                   Ravi                             Eraser        

           1                  Ramesh                        null

           4                  Kalyani                          null

a)Right Outer Join:Here Right means Second table.it returns all the rows from the second table even though it does not have the matchs in First table.But it returns only the matched rows from the First table.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E right join Products on E.EmployeeID=P.EmployeeID

Result:

1)       EmployeeID   EmployeeName          Productname     

    2                  Sukumar                          Pen

    3                   Ravi                                Pencil                          

    3                   Ravi                                Eraser        

            6                   null                                  Book   

5)Natural JOIN:it eliminates the duplicate values from the output.

6)Equi JOIN:An inner join is called equi-join when all the columns are selected with a *, or natural join otherwise