Estos son los dos JOIN que más desconciertan a quien empieza, y por motivos opuestos. El SELF JOIN parece imposible —¿cómo se une una tabla consigo misma sin entrar en un bucle?— y resulta ser la única forma de recorrer las relaciones reflexivas que llevas viendo desde 01-05: empleados.jefe_id y clientes.referido_por_id. El CROSS JOIN parece un error —es el producto cartesiano que en 03-01 presentábamos como accidente— y resulta ser una herramienta deliberada para generar combinaciones completas.

Ninguno de los dos es un "tipo" nuevo de emparejamiento. El SELF JOIN es una técnica que puede usar cualquier tipo (INNER, LEFT...); el CROSS JOIN es el caso degenerado de un JOIN sin condición.

1. SELF JOIN: unir una tabla consigo misma
2. La jerarquía de empleados
3. La red de referidos de clientes
4. SELF JOIN no jerárquico: parejas dentro del mismo grupo
5. Empleados de la misma ciudad
6. Jerarquías de profundidad arbitraria
7. CROSS JOIN: el producto cartesiano deliberado
8. Cuándo es útil y cuándo es un accidente
9. CROSS JOIN con generate_series para calendarios
10. Errores Comunes y Consejos
11. Ejercicios
12. Conclusión

Un SELF JOIN no tiene sintaxis propia. Es un JOIN normal en el que las dos tablas son la misma:

Nada de bucles infinitos: el motor trata cada aparición de la tabla como una relación independiente. Es exactamente el producto cartesiano de 03-01, con empleados a los dos lados, filtrado por la condición del ON.

En 03-01 dijimos que los alias de tabla eran una comodidad. En un SELF JOIN son imprescindibles, y por una razón física: sin ellos, el nombre empleados designaría dos cosas distintas a la vez.

PostgreSQL ni siquiera intenta adivinar: se niega. Con alias distintos, cada aparición tiene identidad propia y todo funciona.

Convención del curso: en un SELF JOIN, los alias no se abrevian por inicial sino que se nombran por el papel que juega cada copia. empleados AS e y empleados AS jefe; clientes AS c y clientes AS referidor; productos AS p1 y productos AS p2 cuando los dos papeles son simétricos. Un alias como e1/e2 es aceptable en parejas simétricas, pero e/jefe es siempre más legible que e1/e2 cuando los papeles son distintos.

empleados.jefe_id es una clave foránea que apunta a empleados.id: la relación reflexiva 1:N que dibujaste en el diagrama ER de 01-06. Cada empleado tiene como mucho un jefe, y un jefe puede tener varios subordinados.

7 filas de 8. Falta Rosa Alcázar Vives, la directora general, porque su jefe_id es NULL y no casa con nadie. Es exactamente el mecanismo de 03-02: una FK nula nunca encuentra pareja.

Y es un resultado peligroso, porque parece completo. Un organigrama que no incluye a la directora general es un organigrama equivocado.

8 filas: el equipo completo. El NULL de Rosa significa "raíz de la jerarquía", y en un informe se presentaría como "—" o "Dirección" con COALESCE (06-04).

Regla: en un SELF JOIN jerárquico, la raíz del árbol siempre tiene la FK a NULL. Si quieres que aparezca, el JOIN tiene que ser LEFT. Es el error más frecuente al construir organigramas, árboles de categorías o estructuras de carpetas.

Cambiando el sentido de la condición se obtiene la relación inversa. Basta con leer el ON al revés:

Devuelve las mismas 7 parejas, presentadas desde el punto de vista del jefe. Fíjate en que la condición ON es idéntica; lo único que cambia es qué alias se llama cómo y qué columnas se proyectan. Es la misma lección de 03-04 sobre LEFT y RIGHT: el emparejamiento no cambia, cambia la lectura.

clientes.referido_por_id funciona igual, pero con un matiz de negocio distinto: aquí el NULL no significa "raíz de una jerarquía" sino "llegó por su cuenta".

15 filas: 8 clientes referidos y 7 sin referidor, exactamente los recuentos que 01-06 anunciaba.

La red que dibujan estos datos:

Marketing quiere premiar a los clientes que más gente han traído. La consulta parte del referidor:

Tres clientes traídos por Lucía, la primera clienta de TiendaVerde. En el módulo 4 contaremos estos referidos por persona con GROUP BY para saber quién encabeza el programa; aquí nos quedamos en el detalle.

Fíjate en un detalle sutil de escritura: aunque el alias ref esté escrito primero en el FROM, sigue siendo la tabla "padre" de la relación. Quién va primero es una decisión de legibilidad, no de significado: lo que fija la dirección es la condición ON c.referido_por_id = ref.id.

No todo SELF JOIN recorre una relación reflexiva declarada. Otro uso muy frecuente es emparejar filas que comparten un atributo: productos de la misma categoría, empleados de la misma ciudad, pedidos del mismo día.

El equipo de marketing quiere diseñar lotes de dos productos de la misma categoría. Primer intento:

Esto devuelve 78 filas, y tiene dos problemas graves:

La solución cabe en tres caracteres: p1.id < p2.id.

6 filas: las 6 parejas posibles entre los 4 productos de Cosmética natural. Sin el WHERE de recorte, la consulta devuelve 29 parejas en todo el catálogo.

Este truco merece un párrafo entero, porque es el patrón canónico y se reutiliza en mil sitios.

Para dos productos cualesquiera A y B de la misma categoría, el producto cartesiano genera cuatro combinaciones:

La condición hace dos trabajos a la vez con un solo operador:

• < en lugar de <= elimina los pares reflexivos (A, A), porque ningún id es menor que sí mismo.
• < en lugar de <> elimina el duplicado (B, A), porque de las dos ordenaciones posibles solo una satisface la desigualdad estricta.

La aritmética confirma el efecto: con n productos en una categoría, sin condición hay n² combinaciones; con <> hay n² − n; con < hay exactamente n(n−1)/2, que es el número de parejas de la combinatoria.

Nota: este es un JOIN con una condición de desigualdad, lo que en 03-01 llamábamos non-equi join. La igualdad p1.categoria_id = p2.categoria_id define el grupo; la desigualdad p1.id < p2.id selecciona una de las dos ordenaciones. Es habitual que un non-equi join acompañe a uno de igualdad, no que lo sustituya.

El mismo patrón, aplicado a empleados. RR. HH. quiere organizar comidas de equipo por oficina y necesita las parejas de compañeros que trabajan en la misma ciudad:

(10 primeras de 21 filas.)

21 parejas, todas de Valencia: siete de los ocho empleados trabajan allí, y 7 × 6 / 2 = 21. Laia Puig Sanchis no aparece en ninguna fila, porque es la única que trabaja en Castellón y no tiene con quién emparejarse.

Ese detalle es importante: un SELF JOIN de este tipo excluye automáticamente a los elementos únicos de su grupo. Si quisieras un listado en el que Laia también apareciera (con NULL como compañero), harías falta un LEFT JOIN con la misma condición en el ON.

También conviene notar que aquí ciudad admite NULL. Si dos empleados tuvieran la ciudad sin rellenar, NULL = NULL no es verdadero y no se emparejarían, cosa que en este caso es lo correcto: no sabemos si trabajan juntos.

Un SELF JOIN recorre exactamente un nivel de la jerarquía. Para subir dos niveles hacen falta dos SELF JOIN encadenados:

Funciona, y en TiendaVerde es suficiente porque el organigrama tiene solo tres niveles. Pero fíjate en la limitación: el número de niveles está codificado en la consulta. Si la empresa crece a cinco niveles, hay que reescribirla añadiendo dos LEFT JOIN más; si un empleado está a siete niveles de la dirección, esta consulta nunca lo alcanzará.

Para jerarquías de profundidad desconocida hace falta otra herramienta: las CTE recursivas (WITH RECURSIVE), que se estudian en la lección 10-02. Con ellas se recorre un árbol de cualquier profundidad con una sola consulta, y se puede calcular el nivel de cada empleado o la cadena de mando completa. Lo mismo vale para la red de referidos: "todos los clientes que descienden de Lucía, directa o indirectamente" es una consulta recursiva, no un SELF JOIN.

El CROSS JOIN combina cada fila de una tabla con cada fila de la otra, sin condición alguna. Es el paso 1 del modelo mental de 03-01, sin el paso 2.

Devuelve 6 × 20 = 120 filas. Ninguna condición, ningún ON: CROSS JOIN no admite ON, y si lo escribes da error de sintaxis.

El tamaño crece como el producto de los cardinales, lo que en tablas reales es explosivo:

Esa última fila —un billón de filas— es la razón por la que un CROSS JOIN accidental puede dejar sin memoria a un servidor. La regla es simple: un CROSS JOIN solo es aceptable cuando al menos una de las dos tablas es pequeña y de tamaño conocido.

Son idénticas para el motor. La primera es una declaración de intenciones; la segunda es exactamente lo que aparece cuando alguien olvida la condición de emparejamiento con la sintaxis antigua de 03-01.

Convención del curso: si quieres un producto cartesiano, escribe CROSS JOIN en mayúsculas y con un comentario que explique por qué. Es la diferencia entre "esto está pensado" y "esto es un descuido".

Este es el motivo por el que el CROSS JOIN existe en el día a día de un analista.

Imagina el informe "ventas por categoría y mes de 2025". Si lo construyes solo con las ventas reales, los meses sin ventas simplemente no aparecen: una categoría que no vendió nada en agosto no tendrá fila de agosto, y el gráfico saltará de julio a septiembre como si agosto no existiera.

La solución profesional consiste en generar primero el esqueleto completo —todas las combinaciones categoría × mes— y unirle después las ventas con un LEFT JOIN:

El esqueleto:

(8 primeras de 72 filas.)

72 filas = 6 categorías × 12 meses. Ninguna combinación falta, y sobre esa base el informe queda cuadrado aunque una categoría no venda nada en todo el año. La parte de sumar las ventas es del módulo 4; el esqueleto es de aquí.

El otro caso clásico no necesita ni tablas: se construye con listas literales usando VALUES (que estudiarás a fondo en 05-02).

Devuelve 12 filas: las 4 tallas × 3 colores de un catálogo textil. Es la lista de referencias que habría que dar de alta. TiendaVerde no vende ropa, pero el patrón aparece en cualquier tienda con variantes de producto.

generate_series es una función generadora de filas de PostgreSQL. Produce una serie de valores y se usa en el FROM como si fuera una tabla:

Con fechas es donde resulta más útil, porque admite un intervalo como paso:

Y cruzada con categorias genera el calendario completo del informe anterior, ahora con fechas reales:

72 filas, listas para recibir las ventas con un LEFT JOIN.

Nota de dialecto: generate_series es específico de PostgreSQL. Los equivalentes en otros motores:

Motor	Cómo generar una serie
PostgreSQL	generate_series(inicio, fin [, paso])
SQL Server	GENERATE_SERIES (desde 2022); antes, una CTE recursiva o una tabla de calendario
Oracle	CONNECT BY LEVEL <= n
MySQL 8+	CTE recursiva WITH RECURSIVE
SQLite	CTE recursiva, o la extensión generate_series

La alternativa portable en cualquier motor es mantener una tabla de calendario permanente con una fila por día o por mes. Es lo que hacen la mayoría de los almacenes de datos, y evita depender del dialecto.

Existe una variante avanzada del CROSS JOIN en la que la tabla de la derecha puede referirse a columnas de la izquierda:

Se llama CROSS JOIN LATERAL (o LEFT JOIN LATERAL) y sirve, por ejemplo, para "las tres reseñas más recientes de cada producto". Necesita subconsultas, que son el módulo 7, así que aquí solo lo mencionamos para que reconozcas la palabra si la ves. LATERAL es estándar SQL y está disponible en PostgreSQL, Oracle y SQL Server (donde se llama CROSS APPLY / OUTER APPLY).

• Olvidar los alias en un SELF JOIN. ERROR: table name "empleados" specified more than once. Cada copia necesita su propio nombre.
• Usar INNER JOIN en una jerarquía. Pierdes la raíz del árbol: Rosa Alcázar Vives desaparece del organigrama porque su jefe_id es NULL. Usa LEFT JOIN.
• Olvidar la condición p1.id < p2.id al generar parejas. Obtienes cada elemento emparejado consigo mismo y cada pareja por duplicado: 78 filas en vez de 29.
• Usar <> en lugar de <. Elimina los pares reflexivos pero no los duplicados: 58 filas en vez de 29.
• Creer que un SELF JOIN recorre toda la jerarquía. Recorre exactamente un nivel. Dos niveles, dos JOIN. Profundidad desconocida, WITH RECURSIVE (10-02).
• Emparejar por una columna que admite NULL en un SELF JOIN de grupo. Dos empleados con ciudad a NULL no se emparejan, porque NULL = NULL no es verdadero.
• Escribir un CROSS JOIN con la sintaxis de comas. Es correcto, pero indistinguible de un olvido. Escribe CROSS JOIN explícito.
• Intentar poner ON en un CROSS JOIN. No lo admite: si necesitas condición, no es un CROSS JOIN, es un INNER JOIN.
• Cruzar dos tablas grandes "para ver qué sale". Comprueba antes el producto de sus recuentos. 10 000 × 10 000 son cien millones de filas.
• Consejo: dibuja el árbol antes de escribir el SELF JOIN. Saber quién es padre y quién es hijo evita invertir la condición ON, que es el error más común y el más difícil de ver.
• Consejo: cuenta las parejas esperadas antes de ejecutar. n(n−1)/2 para las parejas de un grupo de n. Si el resultado no coincide, sabes de inmediato que falta la condición o sobra.
• Consejo: para informes por periodo, construye siempre primero el esqueleto. CROSS JOIN de dimensiones + LEFT JOIN de los hechos. Es la forma estándar de que no haya huecos, y la usarás en cuanto empieces a agregar en el módulo 4.

Marketing quiere una ficha del programa de referidos que muestre, para cada cliente: su nombre completo, quién lo refirió (o NULL si llegó por su cuenta) y quién refirió a su referidor (el "abuelo" de la red).

1. Escribe la consulta usando dos SELF JOIN encadenados sobre clientes.
2. Restríngela a los clientes que sí tienen referidor y comenta el resultado.
3. ¿Por qué esta consulta no puede responder a "todos los clientes que descienden de Lucía, a cualquier profundidad"?

Compras quiere lanzar lotes de dos productos de la misma categoría cuyo precio conjunto no supere los 10 €, usando solo productos activos. Escribe la consulta que devuelva la categoría, los dos productos con sus precios y el precio del lote, ordenada por precio del lote.

Explica qué papel juega cada una de las condiciones del ON y del WHERE.

Responde razonando, sin ejecutar:

1. ¿Cuántas filas devuelve SELECT * FROM empleados AS e1 CROSS JOIN empleados AS e2;?
2. ¿Y SELECT * FROM empleados AS e1 JOIN empleados AS e2 ON e1.id <> e2.id;?
3. ¿Y SELECT * FROM empleados AS e1 JOIN empleados AS e2 ON e1.id < e2.id;?
4. Escribe el esqueleto de un informe proveedor × trimestre de 2025 usando CROSS JOIN y generate_series. ¿Cuántas filas tiene?

Solución 1

1. Con dos SELF JOIN encadenados:

Devuelve las 15 filas de clientes, con dos columnas que van quedando a NULL según se agota la cadena.

2. Solo los clientes con referidor (basta cambiar el primer LEFT JOIN por un INNER JOIN; el segundo debe seguir siendo LEFT):

8 filas. Solo dos clientes tienen "abuelo" en la red: Ana (traída por Carlos, que a su vez vino de Lucía) y Núria (traída por Ana, que vino de Carlos). El resto desciende directamente de alguien que llegó por su cuenta.

Fíjate en que la cadena Lucía → Carlos → Ana → Núria tiene tres saltos, y esta consulta solo llega a ver dos: en la fila de Núria aparece Carlos como abuelo, pero Lucía —la bisabuela— ya no cabe.

3. Por qué no sirve para "todos los descendientes de Lucía": porque el número de niveles está escrito en la consulta. Cada nivel adicional exige un LEFT JOIN más, y para responder a esa pregunta habría que conocer de antemano la profundidad máxima de la red. Con una cadena de siete referidos, harían falta siete JOIN. La herramienta correcta es una CTE recursiva (WITH RECURSIVE), que recorre el árbol hasta agotarlo con una sola consulta: lección 10-02.

Solución 2

5 lotes posibles, todos de Alimentación y Bebidas: son las dos categorías con productos baratos. En Cosmética natural, el lote más económico sería el bálsamo (4,60 €) con el champú (8,40 €), que ya suma 13 €.

Papel de cada condición:

Las condiciones de emparejamiento van en el ON y las de filtrado en el WHERE, siguiendo la convención de 03-01. Como todos los JOIN son INNER, aquí sería equivalente ponerlas en cualquiera de los dos sitios (03-02), pero la separación mantiene la consulta legible.

Solución 3

1. CROSS JOIN de empleados consigo misma: 8 × 8 = 64 filas. Todas las combinaciones, incluidos los ocho pares de cada empleado consigo mismo.

2. Con ON e1.id <> e2.id: 56 filas. Se eliminan los 8 pares reflexivos (64 − 8), pero cada pareja sigue apareciendo dos veces, en los dos órdenes.

3. Con ON e1.id < e2.id: 28 filas. Es 8 × 7 / 2, el número de parejas distintas de 8 elementos. Cada pareja una sola vez y ninguna consigo misma.

La progresión 64 → 56 → 28 resume toda la sección 4.

4. Esqueleto proveedor × trimestre de 2025:

20 filas = 5 proveedores × 4 trimestres. Los trimestres generados empiezan el 1 de enero, el 1 de abril, el 1 de julio y el 1 de octubre de 2025; el límite superior es 2025-10-01 porque generate_series incluye el extremo final, y poner 2025-12-01 produciría un quinto valor no deseado.

Sobre este esqueleto, un LEFT JOIN con las compras reales daría el informe trimestral por proveedor sin huecos, incluido el proveedor 5 (EcoNordic Supplies, inactivo), que aparecería con todos sus trimestres a NULL.

Cierras los dos JOIN especiales:

• Un SELF JOIN es un JOIN normal en el que la misma tabla aparece dos veces. Los alias son obligatorios —ERROR: table name specified more than once— y conviene nombrarlos por el papel que juegan: e / jefe, c / referidor, p1 / p2.
• La jerarquía de empleados se recorre con e.jefe_id = jefe.id. Con INNER JOIN se pierde la raíz (7 filas, sin Rosa); con LEFT JOIN aparece el equipo completo (8 filas). En toda jerarquía, la raíz tiene la FK a NULL.
• La red de referidos de clientes funciona igual: 8 clientes referidos y 7 que llegaron por su cuenta. Lucía Martínez Soler trajo a tres.
• Para parejas dentro de un mismo grupo —productos de la misma categoría, empleados de la misma ciudad— el patrón canónico es ON a.grupo = b.grupo AND a.id < b.id. El < estricto elimina de un golpe los pares reflexivos y los duplicados invertidos: 29 parejas en vez de 78.
• Un SELF JOIN recorre un nivel por JOIN. Para profundidad arbitraria hacen falta CTE recursivas (10-02).
• El CROSS JOIN es el producto cartesiano explícito, sin ON. Es un accidente cuando se olvida la condición de emparejamiento, y una herramienta cuando genera deliberadamente el esqueleto de un informe (categoría × mes) o una matriz de variantes (talla × color).
• generate_series de PostgreSQL produce series numéricas y de fechas usables en el FROM. Cruzado con una dimensión, da calendarios completos sin huecos; en otros motores se sustituye por CTE recursivas o por una tabla de calendario.

Solo queda una forma de combinar tablas que aún no conoces. Todos los JOIN de este módulo añaden columnas: cogen una fila de aquí, una de allá y las pegan de lado. En la lección siguiente, UNION, INTERSECT y EXCEPT, harás lo contrario: apilar resultados enteros en vertical, añadiendo filas en lugar de columnas. Construirás una lista unificada de contactos a partir de clientes, empleados y proveedores; averiguarás en qué ciudades hay a la vez clientes y empleados; y volverás a encontrar los tres productos nunca vendidos, esta vez sin ningún JOIN.