Explicación de auto-joins

Es bastante común cuando tienes una tabla que se hace referencia a sí misma. Ejemplo: una tabla de empleados donde cada empleado puede tener un gerente, y desea enumerar todos los empleados y el nombre de su gerente.

SELECT e.name, m.name
FROM employees e LEFT OUTER JOIN employees m
ON e.manager = m.id

Una unión propia es una unión de una tabla consigo misma.

Un caso de uso común es cuando la tabla almacena entidades (registros) que tienen una relación jerárquica entre ellos. Por ejemplo, una tabla que contiene información de la persona (Nombre, DOB, Dirección...) e incluir una columna en la que se incluya el documento de identidad del Padre (y/o de la madre). Luego con una pequeña consulta como

SELECT Child.ID, Child.Name, Child.PhoneNumber, Father.Name, Father.PhoneNumber
FROM myTableOfPersons As Child
LEFT OUTER JOIN  myTableOfPersons As Father ON Child.FatherId = Father.ID
WHERE Child.City = 'Chicago'  -- Or some other condition or none

Podemos obtener información sobre el niño y el padre (y la madre, con un segundo auto unirse, etc. e incluso abuelos, etc...) en la misma consulta.

Digamos que tienes una tabla users, configurada así:

• ID de usuario
• nombre de usuario
• ID del administrador del usuario

En esta situación, si desea extraer la información del usuario y la información del administrador en una consulta, puede hacer esto:

SELECT users.user_id, users.user_name, managers.user_id AS manager_id, managers.user_name AS manager_name INNER JOIN users AS manager ON users.manager_id=manager.user_id

Son útiles si su tabla es autorreferencial. Por ejemplo, para una tabla de páginas, cada página puede tener un enlace next y previous. Estos serían los ID de otras páginas de la misma tabla. Si en algún momento desea obtener un triple de páginas sucesivas, haría dos auto-joins en las columnas next y previous con la misma columna id de la tabla.

Sin la capacidad de que una tabla se haga referencia a sí misma, tendríamos que crear tantas tablas para niveles jerárquicos como el número de capas en la jerarquía. Pero como esa funcionalidad está disponible, se une la tabla a sí misma y sql la trata como dos tablas separadas, por lo que todo se almacena muy bien en un solo lugar.

Imagine una tabla llamada Employee como se describe a continuación. Todos los empleados tienen un gerente que también es un empleado (tal vez excepto el CEO, cuyo manager_id sería nulo)

Table (Employee): 

int id,
varchar name,
int manager_id

Puede usar la siguiente selección para encontrar a todos los empleados y sus gerentes:

select e1.name, e2.name as ManagerName
from Employee e1, Employee e2 where
where e1.manager_id = e2.id

Hay muchas respuestas correctas aquí, pero hay una variación que es igualmente correcta. Usted puede colocar sus condiciones de combinación en la instrucción join en lugar de la cláusula where.

SELECT e1.emp_id AS 'Emp_ID'
  , e1.emp_name AS 'Emp_Name'
  , e2.emp_id AS 'Manager_ID'
  , e2.emp_name AS 'Manager_Name'
FROM Employee e1 RIGHT JOIN Employee e2 ON e1.emp_id = e2.emp_id

Tenga en cuenta que a veces desea e1.manager_id > e2.id

La ventaja de conocer ambos escenarios es que a veces tiene un montón de condiciones WHERE o JOIN y desea colocar sus condiciones self join en la cláusula other para mantener su código legible.

Nadie abordó lo que sucede cuando un empleado no tiene un gerente. Eh? No se incluyen en el conjunto de resultados. ¿Qué pasa si desea incluir empleados que no tienen gerentes pero no desea que se devuelvan combinaciones incorrectas?

Pruebe este cachorro;

SELECT e1.emp_id AS 'Emp_ID'
   , e1.emp_name AS 'Emp_Name'
   , e2.emp_id AS 'Manager_ID'
   , e2.emp_name AS 'Manager_Name'
FROM Employee e1 LEFT JOIN Employee e2 
   ON e1.emp_id = e2.emp_id
   AND e1.emp_name = e2.emp_name
   AND e1.every_other_matching_column = e2.every_other_matching_column

Aparte de las respuestas mencionadas anteriormente (que están muy bien explicadas), me gustaría agregar un ejemplo para que el uso de Self Join pueda mostrarse fácilmente. Supongamos que tiene una tabla llamada CLIENTES que tiene los siguientes atributos: CustomerID, CustomerName, ContactName, City, Country. Ahora desea enumerar todos los que son de la" misma ciudad". Tendrás que pensar en una réplica de esta mesa para que podamos unirnos a ellos sobre la base de la CIUDAD. La consulta a continuación mostrará claramente lo que means:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, 
A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City 
ORDER BY A.City;

Un caso de uso es buscar registros duplicados en una base de datos.

SELECT A.Id FROM My_Bookings A, My_Bookings B
WHERE A.Name = B.Name
AND A.Date = B.Date
AND A.Id != B.Id

Es el equivalente de base de datos de una lista/árbol enlazado, donde una fila contiene una referencia en alguna capacidad a otra fila.

Aquí está la exaplanación de self join en términos laicos. Self join no es un tipo diferente de join. Si ha entendido otros tipos de uniones (Internas, Externas y Cruzadas), entonces la unión propia debe ser directa. En LAS UNIONES INTERNAS, EXTERNAS y CRUZADAS, se unen 2 o más tablas diferentes. Sin embargo, en self join te unes a la misma tabla con itslef. Aquí, no tenemos 2 tablas diferentes, pero tratamos la misma tabla como una tabla diferente usando alias de tabla. Si esto todavía no está claro, lo haría recomendamos ver los siguientes videos de youtube.

Auto Unirse con un ejemplo