Lección 5.7: AUTO JOIN - Uniendo una Tabla Consigo Misma

Un AUTO JOIN no es una palabra clave de SQL, sino simplemente un término común para una situación en la que una tabla se une a sí misma. En la práctica, esto se implementa utilizando tipos regulares de JOIN, más a menudo INNER JOIN o LEFT JOIN, dependiendo de la lógica requerida. Esto es útil para consultar datos jerárquicos o comparar filas dentro de la misma tabla.

¿Qué es un AUTO JOIN?

Para realizar un auto-join, debes tratar una tabla como si fuera dos tablas separadas. Para hacer esto, debes usar alias de tabla para darle a cada instancia de la tabla un nombre único. Sin alias, la base de datos no sabrá qué columna pertenece a qué instancia de la tabla.

Visualización (Jerarquía de Empleados): Imagina una tabla employee donde cada fila tiene un manager_id que apunta al employee_id de su supervisor.

   Tabla A (Empleados)          Tabla B (Gerentes)
   +----+-------+---------+     +----+-------+
   | id | nombre| mgr_id  |     | id | nombre|
   +----+-------+---------+     +----+-------+
   | 1  | Alice | NULL    |     | 1  | Alice |
   | 2  | Bob   | 1       | <-> | 1  | Alice | (El gerente de Bob es Alice)
   | 3  | Carol | 1       | <-> | 1  | Alice | (El gerente de Carol es Alice)
   +----+-------+---------+     +----+-------+

Sintaxis de AUTO JOIN

SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM
    employee AS e
LEFT JOIN
    employee AS m ON e.manager_id = m.id;

• employee AS e: La primera instancia (representando a los empleados).
• employee AS m: La segunda instancia (representando a los gerentes).
• ON e.manager_id = m.id: La condición que los vincula.

Ejemplos Prácticos (Base de Datos Sakila)

1. Encontrar Películas con la Misma Duración

Supongamos que queremos encontrar pares de películas que tienen exactamente la misma length. Podemos unir la tabla film consigo misma.

SELECT
    f1.title AS film_1,
    f2.title AS film_2,
    f1.length
FROM
    film AS f1
INNER JOIN
    film AS f2 ON f1.length = f2.length
WHERE
    f1.film_id <> f2.film_id -- Asegurarse de no emparejar una película consigo misma
LIMIT 10;

La condición f1.film_id <> f2.film_id es crítica. Sin ella, cada película coincidiría consigo misma (ya que tiene la misma duración que sí misma).

2. Encontrar Clientes de la Misma Ciudad

Si queremos ver qué clientes viven en la misma ciudad (basado en address_id en este ejemplo simplificado):

SELECT
    c1.first_name AS cust_1_first,
    c1.last_name AS cust_1_last,
    c2.first_name AS cust_2_first,
    c2.last_name AS cust_2_last,
    c1.address_id
FROM
    customer AS c1
INNER JOIN
    customer AS c2 ON c1.address_id = c2.address_id
WHERE
    c1.customer_id < c2.customer_id; -- Usar '<' en lugar de '<>' para evitar pares duplicados (A-B y B-A)

Conclusiones Clave de Esta Lección

• Un AUTO JOIN es un término para unir una tabla consigo misma, no una palabra clave SQL separada.
• Generalmente se implementa con tipos de JOIN regulares como INNER JOIN o LEFT JOIN.
• Alias de Tabla son obligatorios para distinguir entre las dos instancias de la tabla.
• Usa condiciones ON para definir la relación entre filas (por ejemplo, jerarquía o atributos compartidos).
• Usa condiciones de filtro como id1 <> id2 o id1 < id2 para evitar emparejar una fila consigo misma o devolver pares redundantes. En el caso de LEFT JOIN, parte de esta lógica puede colocarse no solo en WHERE, sino también en la condición ON.