Self join

[computerzworld]

Hello,
I am having one table which contains 7 values with one field as primary key. Here is the structure of that table.

tbl_users

Id     Name
-------------------
1      A
2      B
3      C
4      D
5      E
6      F

And here is my self join query.

select * from tbl_users as u1 join tbl_users u2 on u1.Id = u2.Id+1;

And it returns me the result as 2,3,4,5,6 with corresponding Names. So, how these comparison exactly works? And what basically self join does and for what purpose it is used? Please help me. Thanks in advance.

[tomhath]

There is nothing special about a self join, it works the same as any other join between two tables. In this case a row from u1 where Id=3 matches a row from u2 where Id=2.

The fact that u1 and u2 are selecting rows from the same actual table doesn't matter, think of it as if there were two exact copies of the table.

[Sxooter]

A common use if for things like employees and bosses.