CODEWITHSUNDEEP
sqlsql-server
Giri Prasad
Giri Prasad

Reputation: 1205

Can I join a table with itself in SQL Server?

Is there any way that I can join a table with the same table?

Upvotes: 0

Views: 516

Answers (4)

Rahul Tripathi
Rahul Tripathi

Reputation: 172398

You can use the self join.

An example to explain the self join. Lets say you have a table which have Employee and Supervisor details like this:

EmployeeName      SupervisorName
ABC                XYZ
DEF                YZX
PQR                XYZ
HJK                PQR

Now if I want to find who all employee have the same Supervisor as employee ABC.

So possible approach:

Approach 1

SELECT employeename
FROM employee
WHERE SupervisorName = "ABC"

So for this we need to have the name of the Supervisor for which we want to get the details(An absurd one)

Approach 2:

SELECT employeename
FROM employee
WHERE SupervisorName in
( SELECT SupervisorName 
FROM employee
WHERE employee_name = "ABC")

This one is not as efficient as using the Self Join

Approach 3:- Using the Self Join

SELECT e1.employeename
FROM employee e1 self join employee e2
on e1.SupervisorName  = e2.SupervisorName 
AND e2.employeename="ABC";

Upvotes: 1

Sachin Gupta
Sachin Gupta

Reputation: 8358

Following link describes self join in sql server : https://technet.microsoft.com/en-us/library/ms177490%28v=sql.105%29.aspx

Upvotes: 0

shauryachats
shauryachats

Reputation: 10385

This is known as Self Join.

Quoting w3resource.com,

A self join is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

A typical example:

SELECT  a.ID, b.NAME, a.SALARY 
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

Upvotes: 3

Dani
Dani

Reputation: 181

Yes, if you set an alias to the table

... from my_table join my_table as 'othertable' ON ...

Upvotes: 0

Related Questions