What is Joins ? What is the use of joins ? What are the types of joins?How to use joins in sql server ?

1) What is the use of Joins ?

    Joins are used for retriving the data from one or more tables at the same time.

2) How many types of joins ?What are those?

   1) Inner Joins

       1) An Equality condition

       2) Equality condition

       3) Self join

   2) Cross joins

   3) Outer joins

       1) Left outer join

       2) Right outer join

       3) Full outer join

3) What is Equijoin or Equality join ? Give an Example ?

     If two or more tables are combined together basing on equality condition.we call it as a quijion.In this case the matching rows of first table will go and combine       with matching row's of second table.

      Write a query to retrive data from Employee and Department tables?

     Select E.EmpNo,E.Ename,E.job,E.mgr,E.sal,E.comm,E.DeptNo,D.DeptNo,D.Dname,D.Loc from Employee E inner join Department D on E.DeptNo=DeptNo

4) What is Non Equijoin or Non Equality condition ? Give an Example ?

    If two or more tables are combined to gether basing on any condition other than Equality condition we call it as non equijoin.

     Write a Query to retrive the data from Employee and Salary Grade tables to findout the grade of each employee basing on his salary matching with lowsal & high sal      of sal grade table ?

     Select E.EmpNo,E.Ename,E.sal.S.Losal,S.hisal from Employee E inner join Salgrade s on E.sal between S.Losal and S.hisal

5) What is Self join ? Give an Example ?

    If a table is combined to itself basing on equality or no equality condition we call it as a self join

       Note: Duplicate Records eliminating by using Distinct

     Write a query to get the list of employee's who are having sub ordinates under them ?

    Select Distinct E.Empno,E.Ename ,E.job,E.Mgr,E.sal,E.Deptno from Employee E inner join Employee M on E.Empno = M.Mgr

6) What is Cross join ?Give an Example ?

   These is earlier known as cartision join.if at all two or more tables are combine to gether without any condition we call it as a cross join.In this case each row of the first table will join with each row of the second table so in the first table has m rows and the second table has n rows.The output will be m*n rows

    Example :

    Select * from Employee E cross join Department D

7) What is outer joins ? Give an Example ?

   This is an extenstion to the existing inner joins .where in case of  inner joins we will be getting only the matching records basing on the condtion between the    tables where as in case of outer join along with the matching records we can aslo retrived the unmathced data from the tables outer joins are of three types.

      a) Left outer join

      b) Right outer join

     c) Full outer join

8) What is Left outer join ? Give an Example ?

     In this case we will be getting the matching records from both the two tables as well as the unmatched records from left hand side table.

    Example

       Select E.Empno,E.Ename,E.Deptno as EDeptNo ,D.Deptno as DDeptNo,D.Dname,D.Loc from Employee E left outer join Department D on E.Deptno=D.Deptno

9) What is Right outer join ? Give an Example ?

   In this case we will be getting the matching records from both the two tables as well as the unmatched records from right hand side table.

    Example

       Select E.Empno,E.Ename,E.Deptno as EDeptNo ,D.Deptno as DDeptNo,D.Dname,D.Loc from Employee E right outer join Department D on E.Deptno=D.Deptno

10) What is Full outer join ?Give an Example ?

   In this case we will be getting the matching records from both the two tables as well as the unmatched records from left hand side and right hand side tables.

    Example

       Select E.Empno,E.Ename,E.Deptno as EDeptNo ,D.Deptno as DDeptNo,D.Dname,D.Loc from Employee E Full outer join Department D on E.Deptno=D.Deptno

   
About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

No comments