Self Join Sql

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. One practical use for self-joins: obtaining running counts and running totals in an SQLquery.

Joining a Table to Itself

Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join. For example, to find the name of Lorentz’s manager, you need to:

•Find Lorentz in the EMPLOYEES table by looking at the LAST_NAME column

•Find the manager number for Lorentz by looking at the MANAGER_ID column. Lorentz’s manager number is 103.

•Find the name of the manager with EMPLOYEE_ID 103 by looking at the LAST_NAME column. Hunold’s employee number is 103, so Hunold is Lorentz’s manager.

In this process, you look in the table twice. The first time you look in the table to find Lorentz in the LAST_NAME column and MANAGER_ID value of 103. The second time you look in the EMPLOYEE_ID column to find 103 and the LAST_NAME column to find Hunold.

Example :-

SELECT e.last_name emp, m.last_name mgr

FROM employees e JOIN employees m

ON (e.manager_id = m.employee_id);