Self Join In Sql Server 2000 2005

Self Join in SQL Server 2000/2005 helps in retrieving the records having some relation or similarity with other records in the same database table. A common example of employees table can do more clearly about the self join in sql. Self join in sql means joining the single table to itself. It creates the partial view of the single table and retrieves the related records. You can use aliases for the same table to set a self join between the single table and retrieve the records satisfying the condition in where clause.

You can see the example of Joins using table aliases in SQL Server 2005 here.

For self join in sql you can try the following example:

Create Table Employees

Now to get the names of managers from the above single table you can use sub queries or simply the self join. 

Self Join SQL Query

Following is an example of sql query to get the names of manager and employees.

SELECT		e1.emp_name as 'manager',
			e2.emp_name as 'employee'
FROM		employees e1 
JOIN		employees e2
ON			e1.emp_id = e2.emp_manager_id

Result

Understanding the Self Join Example

In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table. 

...
FROM		employees e1 
JOIN		employees e2
ON			e1.emp_id = e2.emp_manager_id

Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.

Continue to next tutorial: SQL Inner Equi Join Examples to learn the implementation of equi join using SQL query.