Can I join a table with itself in SQL Server?

Question

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

Yes, but if you need more help you should provide an example (data and expected output) — Giannis Paraskevopoulos, Mar 24 '15 at 10:47
Why should you need to join a table that you already selected? (bit curious) — , Mar 24 '15 at 10:49
possible duplicate of [Explanation of self-joins](http://stackoverflow.com/questions/2458519/explanation-of-self-joins) — shauryachats, Mar 24 '15 at 10:55

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

This is known as Self Join.

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;

Isn't comma joins a bit outdated? I would always use a standard `JOIN` clause. — Giannis Paraskevopoulos, Mar 24 '15 at 10:55

score 1 · Answer 2 · answered Mar 24 '15 at 11:18

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";

score 0 · Answer 3 · answered Mar 24 '15 at 10:52

0

Yes, if you set an alias to the table

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

answered Mar 24 '15 at 10:52

Dani

161
4

score 0 · Answer 4 · answered Mar 24 '15 at 10:54

0

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

answered Mar 24 '15 at 10:54

Sachin Gupta

7,805
4
30
45

Can I join a table with itself in SQL Server?

4 Answers4

This is known as Self Join.