Varios JOINs, alias y self-join

Alias de tabla

Escribir productos.nombre y categorias.nombre una y otra vez se hace pesado. Un alias es un nombre corto que asignas a una tabla con AS (o sin él):

SELECT p.nombre, c.nombre AS categoria
FROM productos AS p
INNER JOIN categorias AS c ON p.categoria_id = c.id;

p y c son los alias. A partir de ahí te refieres a las columnas como p.nombre y c.nombre. Es más corto y más legible.

Varios JOINs encadenados

Puedes combinar más de dos tablas: cada JOIN añade una tabla nueva con su propio ON. Se evalúan de arriba abajo, encadenando los emparejamientos:

SELECT p.nombre, c.nombre, e.nombre
FROM productos AS p
INNER JOIN categorias AS c ON p.categoria_id = c.id
INNER JOIN empleados  AS e ON c.responsable_id = e.id;

Self-join: una tabla consigo misma

A veces una tabla se relaciona consigo misma. Pensemos en empleados, donde cada uno tiene un jefe_id que apunta a otro empleado de la misma tabla:

Para mostrar cada empleado junto al nombre de su jefe unimos empleados con empleados. Como no podemos tener la misma tabla dos veces con el mismo nombre, los alias son obligatorios: uno para el empleado, otro para el jefe.

SELECT e.nombre AS empleado, j.nombre AS jefe
FROM empleados AS e
INNER JOIN empleados AS j ON e.jefe_id = j.id;

Aquí e es "el empleado" y j es "su jefe": ambos son la misma tabla, pero el ON los relaciona como dos roles distintos.

Con INNER JOIN, Ana (que no tiene jefe) no aparece, porque su jefe_id es NULL y no empareja con nadie. Si quisieras incluirla con su jefe en NULL, usarías LEFT JOIN.

Ejemplos

Self-join: cada empleado y el nombre de su jefe

SELECT e.nombre AS empleado, j.nombre AS jefe
FROM empleados AS e
INNER JOIN empleados AS j ON e.jefe_id = j.id
ORDER BY e.nombre;