Joins in SQL Server

In this article we learn about joins in SQL Server.

A join is used to combined data from two or more tables in SQL.

To provide a sample, first we create two tables called Table1 and Table2.

  1. Create table table1
  2. (  
  3.    id int,  
  4.    Name varchar(30)  
  5. )  
  6.   
  7. Create table table2
  8. (  
  9.    id int,  
  10.    Name varchar(30),  
  11.    data varchar(100)  
  12. )  
Insert some data into the tables as in the following:
  1. insert into table1 values(1,’test1′)  
  2. insert into table1 values(2,’test2′)  
  3. insert into table1 values(3,’test3′)  
  4.   
  5. insert into table2 values(1,’test1′,null)  
  6. insert into table2 values(3,’test3′,”)  
  7. insert into table2 values(4,’test4′,’some text’)  
  8. insert into table2 values(5,’test5′,’other text’)  

Joins

The following are the various types of joins in SQL Server.

  • Cross Join

    A cross join that does not have a where clause. It produces the cartesian product of the tables involved in the join.

    The output rows of a cross join = number of rows in first_table * number of rows in second_table

    Syntax: select * from table1,table2

  • Inner Join

    An inner join selects all rows from both tables that have a match in both joined tables.

    Syntax: select table1.id,table2.name,table2.data from table1 inner join table2 on table1.id=table2.id

    Output

                                  inner join output

  • Left Outer Join

    In this join are all the rows in the first table and the matching rows of the second table. An unmatched row in the second table appears as null in the result.

    Syntax: select table1.id,table2.name,table2.data from table1 left outer join table2 on table1.id=table2.id

    Output

                                  left outer join

  • Right Outer Join

    In this join all the rows in the second table and the matching rows of the first table. An unmatched row in the first table appears as null in the result.

    Syntax: select table1.id,table2.name,table2.data from table1 right outer join table2 on table1.id=table2.id

    Output

                                  right outer join

  • Full Outer Join

    In this join all the rows are the result of whether or not they are matched.

    Syntax: select table1.id,table2.name,table2.data from table1 full outer join table2 on table1.id=table2.id

    Output

                                  full outer join

  • Self Join

    In this join one table is joined to itself. We need to create another table for explaining the self join.
    1. create table emp  
    2. (  
    3. id int identity(1,1),  
    4. name varchar(30),  
    5. managerid int  
    6. )  
    7.   
    8. insert into emp values(‘pramod’,0)  
    9. insert into emp values(‘prem’,1)  
    10. insert into emp values(‘sameer’,1)  
    11. insert into emp values(‘ravi’,2)  
    12. insert into emp values(‘rahul’,3)  
    Syntax: select distinct e1.id, e1.name from emp e1, emp e2 where e1.id=e2.managerid

    Output

                                  self join
I hope this article is helpful for you..

Thanks :)


Similar Articles