SIGN UP MEMBER LOGIN:
   
ARTICLE

How to use self join in SQL Server 2008

Posted by Rohatash Articles | SQL Server October 08, 2010
In this article we will learn how to use self join in SQL Server 2008.
Reader Level:

In this article we will learn how to use self join in SQL server 2008.

Self join

Self join operation performed to a single table in this case table is joined with itself. A single column of the table is compared with itself. The comparison of the column with itself means that the table name appears twice in the from clause of a select statement. This can be accomplished using aliases.

For example

USE master

go

create table emp

(

emp_id integer,

emp_name varchar(30),

company_id integer

);

go

insert into emp   values(1,'rajesh',null);

go

insert into emp  values(2,'suresh raina',1);

go

insert into emp  values(3,'ravindra',1);

go

insert into emp  values(4,'tendulkar',2);

go

insert into emp  values(5,'yuvraj singh',2);

go

insert into emp  values(6,'rp singh',5);

go

insert into emp  values(7,'prveen kumar',6);

go

select * from emp;

OUTPUT:

 s1.gif

Using self join Query:

USE master

go

select e1.emp_name 'manager',e2.emp_name 'employee'

from emp  e1 join emp e2

on e1.emp_id=e2.company_id

go

OUTPUT:

s2.gif

In the above self join query, emp table is joined with itself using table aliases e1 and e2.

Login to add your contents and source code to this article
This Feature is Sponsored By
post comment
COMMENT USING