Self Join In SQL Server

Hi Swati,


The SQL SELF JOIN is used to join a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Syntax:

The basic syntax of SELF JOIN is as follows:

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_filed = b.common_field;

Here WHERE clause could be any given expression based on your requirement.
Example:

Consider following two tables, (a) CUSTOMERS table is as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now let us join this table using SELF JOIN as follows:

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

This would produce following result:

+----+---------+---------+
| ID | NAME    | SALARY  |
+----+---------+---------+
|  2 | Ramesh  | 1500.00 |
|  2 | kaushik | 1500.00 |
|  1 | kaushik | 2000.00 |
|  2 | kaushik | 1500.00 |
|  3 | kaushik | 2000.00 |
|  6 | kaushik | 4500.00 |
|  1 | Hardik  | 2000.00 |
|  2 | Hardik  | 1500.00 |
|  3 | Hardik  | 2000.00 |
|  4 | Hardik  | 6500.00 |
|  6 | Hardik  | 4500.00 |
|  1 | Komal   | 2000.00 |
|  2 | Komal   | 1500.00 |
|  3 | Komal   | 2000.00 |
|  1 | Muffy   | 2000.00 |
|  2 | Muffy   | 1500.00 |
|  3 | Muffy   | 2000.00 |
|  4 | Muffy   | 6500.00 |
|  5 | Muffy   | 8500.00 |
|  6 | Muffy   | 4500.00 |
+----+---------+---------+



Please refer the below links

http://www.tutorialspoint.com/sql/sql-self-joins.htm

http://www.sql-statements.com/sql-self-join.html

http://www.sqltutorial.org/sqlselfjoin.aspx

http://www.udel.edu/evelyn/SQL-Class3/SQL3_self.html




Thanks

hi,

A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table.

please refer...

http://blog.sqlauthority.com/2007/06/03/sql-server-2005-explanation-and-example-self-join/
http://learnsqlserver.in/3/Self-Join.aspx

hope this will help you.

In a self join, a table is joined with itself, as result, one row in a table correlates with other rows in the same table. In a self join, a table name is used twice in the query. Therefore, to differentiate the two instances of a single table, the table is given two alias names.

Ex:

SELECT a.EmployeeID,a.Title as Employee_Des, a.ManagerID,b.Title As

Manager_Des From HumanResources.Employee a,HumanResources.Employee b where a.ManagerID=b.EmployeeID

 

The Following query joins the Employee table with itself to display the EmployeeId attribute and designation of all the employees with the designation of their managers.

Why require :

The Employee table stores only the designation and the EmployeeID of the manager for all the employees. The designation of the manager is not displayed. Therefore, You need to join the table with itself. Here we require the self join.

in the self join we join the table from it self means we join a table from itself.

To define self join we need a table and create a two aliase of same table

Like I have a COURSE Table