Curso SQL · Northwind
Tema 23 de 64
Bloque 5 · Combinar tablas PostgreSQL 17 northwind

CROSS JOIN y self-join

Requiereleft-right-y-full-join

1. Qué es

Dos JOIN que parecen rarezas y no lo son.

CROSS JOIN empareja cada fila con todas las de la otra tabla: el producto cartesiano. Sin ON, porque no hay condición. 8 categorías × 4 regiones = 32 filas.

Self-join es una tabla unida consigo misma. No es un tipo de JOIN distinto —se escribe con JOIN o LEFT JOIN normales— sino un uso: aparece siempre que una fila se relaciona con otra fila de su misma tabla. Jerarquías (reports_to), duplicados, comparaciones entre pares.

Los dos comparten un rasgo: cuando aparecen sin querer, son bugs; cuando aparecen a propósito, resuelven cosas que ninguna otra herramienta resuelve.

2. Sintaxis

-- CROSS JOIN: sin ON
FROM a CROSS JOIN b

-- La forma antigua, idéntica y peligrosa
FROM a, b

-- Self-join: la misma tabla, dos alias distintos
FROM employees e
JOIN employees j ON j.employee_id = e.reports_to

En un self-join los alias no son opcionales: son lo que hace posible la consulta. e y j son la misma tabla vista dos veces, y sin alias no habría forma de decir de cuál hablas.

3. CROSS JOIN

3.1 El producto cartesiano

SELECT count(*) FROM categories CROSS JOIN region;    -- 32

SELECT c.category_name, r.region_description
FROM categories c CROSS JOIN region r
ORDER BY 1, 2 LIMIT 5;

 category_name | region_description
---------------+--------------------
 Beverages     | Eastern
 Beverages     | Northern
 Beverages     | Southern
 Beverages     | Western
 Condiments    | Eastern

8 × 4 = 32. El tamaño se multiplica, y eso es lo que lo hace peligroso: dos tablas de 10.000 filas dan cien millones.

3.2 Cuando aparece sin querer

SELECT count(*) FROM products, categories;            -- 616
SELECT count(*) FROM products, categories
WHERE products.category_id = categories.category_id;  -- 77

616 contra 77. Con la sintaxis de coma, olvidar la condición no da error: da un producto cartesiano que parece un resultado. Con JOIN ... ON, olvidar el ON da error de sintaxis y te enteras al momento.

Esa es toda la razón por la que la sintaxis de coma se abandonó. No es estilo: es que una de las dos formas te avisa y la otra no.

3.3 Cuando se usa a propósito

El uso legítimo casi siempre es fabricar un esqueleto: todas las combinaciones que deberían existir, para luego comprobar cuáles faltan.

SELECT c.category_id, g.mes
FROM categories c
CROSS JOIN generate_series('1997-01-01'::date, '1997-12-01'::date, '1 month') AS g(mes);

96 filas: 8 categorías × 12 meses. Ninguna viene de los datos — es un calendario completo, inventado a propósito.

¿Para qué? Porque un GROUP BY sobre las ventas solo devuelve los meses que tuvieron ventas (group-by). Si una categoría no vendió nada en marzo, marzo desaparece y el gráfico dibuja una línea continua sobre un agujero. El CROSS JOIN genera el eje completo, y un LEFT JOIN contra las ventas rellena lo que hay. Es el patrón entero de fechas-sin-actividad.

Los otros dos usos reales: plantillas de informe (todas las combinaciones categoría × región aunque estén vacías) y generar datos de prueba.

4. Self-join

4.1 Jerarquías: quién es el jefe de quién

employees.reports_to apunta a employees.employee_id. La tabla se relaciona consigo misma:

SELECT e.first_name || ' ' || e.last_name AS empleado,
       j.first_name || ' ' || j.last_name AS jefe
FROM employees e
LEFT JOIN employees j ON j.employee_id = e.reports_to
ORDER BY e.employee_id;

     empleado     |      jefe
------------------+-----------------
 Nancy Davolio    | Andrew Fuller
 Andrew Fuller    |
 Janet Leverling  | Andrew Fuller
 Margaret Peacock | Andrew Fuller
 Steven Buchanan  | Andrew Fuller
 Michael Suyama   | Steven Buchanan
 Robert King      | Steven Buchanan
 Laura Callahan   | Andrew Fuller
 Anne Dodsworth   | Steven Buchanan

Tiene que ser LEFT JOIN. Andrew Fuller no reporta a nadie (reports_to es NULL), así que con un INNER JOIN desaparecería el director de la lista de empleados — el error más irónico posible.

Y dado la vuelta, cuántos subordinados tiene cada jefe:

SELECT j.first_name || ' ' || j.last_name AS jefe, count(*) AS subordinados
FROM employees e
JOIN employees j ON j.employee_id = e.reports_to
GROUP BY 1 ORDER BY 2 DESC;

      jefe       | subordinados
-----------------+--------------
 Andrew Fuller   |            5
 Steven Buchanan |            3

Dos niveles: Fuller arriba, Buchanan en medio con 3 a su cargo. Un self-join recorre un solo nivel de jerarquía. Para bajar por un organigrama de profundidad desconocida hace falta una CTE recursiva (cte-recursivas).

4.2 Pares: clientes de la misma ciudad

SELECT a.company_name AS cliente_a, b.company_name AS cliente_b, a.city
FROM customers a
JOIN customers b ON b.city = a.city AND b.customer_id > a.customer_id
ORDER BY a.city LIMIT 5;

           cliente_a            |       cliente_b        |     city
--------------------------------+------------------------+--------------
 Cactus Comidas para llevar     | Océano Atlántico Ltda. | Buenos Aires
 Cactus Comidas para llevar     | Rancho grande          | Buenos Aires
 Océano Atlántico Ltda.         | Rancho grande          | Buenos Aires
 Furia Bacalhau e Frutos do Mar | Princesa Isabel Vinhos | Lisboa
 Around the Horn                | B's Beverages          | London

El b.customer_id > a.customer_id es la pieza clave. Sin él:

SELECT count(*) FROM customers a JOIN customers b ON b.city = a.city;                          -- 179
SELECT count(*) FROM customers a JOIN customers b ON b.city = a.city
                                                 AND b.customer_id > a.customer_id;            -- 44

179 contra 44. Los 179 se descomponen así: 91 filas donde cada cliente se empareja consigo mismo, más 88 = 44 parejas contadas dos veces (A-B y B-A).

Condición Qué obtienes
b.city = a.city Cada cliente consigo mismo + cada pareja duplicada
... AND b.customer_id <> a.customer_id Quita los espejos, deja las parejas por duplicado (88)
... AND b.customer_id > a.customer_id 44 parejas, cada una una vez

En un self-join de pares, > es la condición correcta, no <>. Con <> cada pareja sale dos veces y cualquier count o sum sale al doble.

4.3 Comparar filas de la misma tabla

Productos que cuestan menos de la décima parte que otro de su misma categoría:

SELECT p1.product_name AS caro, p2.product_name AS barato,
       p1.unit_price, p2.unit_price
FROM products p1
JOIN products p2 ON p2.category_id = p1.category_id
                AND p2.unit_price  < p1.unit_price / 10
ORDER BY p1.unit_price DESC LIMIT 3;

     caro      |          barato           | unit_price | unit_price
---------------+---------------------------+------------+------------
 Côte de Blaye | Laughing Lumberjack Lager |     263.50 |      14.00
 Côte de Blaye | Rhönbräu Klosterbier      |     263.50 |       7.75
 Côte de Blaye | Outback Lager             |     263.50 |      15.00

Fíjate en que el ON no lleva ninguna igualdad entre claves: lleva una desigualdad. Es un non-equi join, es legítimo, y es caro: el motor no puede usar los atajos de un emparejamiento por igualdad.

4.4 Duplicados sospechosos

El caso de distinct-y-duplicados, ahora con las dos filas al lado:

SELECT o1.order_id AS pedido_1, o2.order_id AS pedido_2,
       o1.customer_id, o1.order_date
FROM orders o1
JOIN orders o2 ON o2.customer_id = o1.customer_id
              AND o2.order_date  = o1.order_date
              AND o2.order_id    > o1.order_id
ORDER BY o1.order_id LIMIT 4;

 pedido_1 | pedido_2 | customer_id | order_date
----------+----------+-------------+------------
    10410 |    10411 | BOTTM       | 1997-01-10
    10456 |    10457 | KOENE       | 1997-02-25
    10616 |    10617 | GREAL       | 1997-07-31
    10713 |    10714 | SAVEA       | 1997-10-22

7 parejas. Con GROUP BY ... HAVING count(*) > 1 sabías cuántos había; con el self-join ves los dos ids concretos, que es lo que hace falta para investigar. Y aquí saltan a la vista ids consecutivos —10410/10411, 10456/10457—: dos pedidos creados uno detrás de otro. Eso es exactamente lo que parece un doble clic... y también lo que parece un cliente que pide dos cosas seguidas. El SQL localiza los candidatos; decidir cuáles son errores no es SQL.

5. Errores comunes

  1. FROM a, b sin condición. Producto cartesiano silencioso.
  2. Un CROSS JOIN sobre tablas grandes. 10.000 × 10.000 = cien millones de filas. Se nota.
  3. Self-join con INNER cuando la referencia puede ser NULL. Desaparece el director.
  4. Self-join de pares con <> en vez de >. Todo sale por duplicado.
  5. Olvidar los alias en un self-join. column reference is ambiguous en el mejor caso.
  6. Usar self-join para recorrer una jerarquía de más de un nivel. Un self-join = un nivel. Para n niveles, n joins o una CTE recursiva.
  7. Non-equi joins sobre tablas grandes sin pensar. Correctos y caros.

6. Diferencias con SQL Server

PostgreSQL SQL Server
CROSS JOIN ✅ ✅
FROM a, b ✅ (no lo uses) ✅ (no lo uses)
Self-join ✅ Igual ✅ Igual
Generar series generate_series(...) ❌ No existe → tabla de números, master.dbo.spt_values o una CTE recursiva
Correlación en el FROM CROSS JOIN LATERAL CROSS APPLY

generate_series es la diferencia práctica, y aparece cada vez que hay que fabricar un calendario. En SQL Server lo habitual es mantener una tabla de fechas permanente en la base — que, dicho sea de paso, también es buena idea en PostgreSQL cuando los informes de calendario son frecuentes.

CROSS APPLY de SQL Server es el equivalente exacto de CROSS JOIN LATERAL — se ve en lateral.

7. Ejercicios

  1. Genera todas las combinaciones de categoría y región. ¿Cuántas son?
  2. Lista cada empleado con su jefe y el cargo del jefe. Los 9 tienen que salir.
  3. Encuentra las parejas de productos de la misma categoría cuyo precio difiere en menos de 0,50. Cada pareja una sola vez.
  4. ¿Cuántos empleados hay en cada nivel de la jerarquía: sin jefe, con jefe directo, y con jefe que a su vez tiene jefe?
  5. Sin ejecutarla, di cuántas filas devuelve y por qué: sql SELECT count(*) FROM employees e1, employees e2;

8. Pregunta de negocio

El responsable de datos sospecha que hay clientes duplicados: la misma empresa dada de alta dos veces con códigos distintos. Te pide que los busques.

No hay una columna que lo diga. Hay que definir qué significa "el mismo cliente".

? RespuestaRespóndela antes de abrir

Primero hay que definir "el mismo cliente". Ninguna definición es perfecta, así que se prueban varias y se enseña el resultado de cada una:

-- Candidato 1: mismo teléfono
SELECT a.customer_id, a.company_name, b.customer_id, b.company_name, a.phone
FROM customers a JOIN customers b
  ON b.phone = a.phone AND b.customer_id > a.customer_id;

-- Candidato 2: misma dirección y ciudad
SELECT a.customer_id, a.company_name, b.customer_id, b.company_name, a.address, a.city
FROM customers a JOIN customers b
  ON b.address = a.address AND b.city = a.city AND b.customer_id > a.customer_id;

-- Candidato 3: mismo nombre de contacto y ciudad
SELECT a.customer_id, a.company_name, b.customer_id, b.company_name, a.contact_name
FROM customers a JOIN customers b
  ON b.contact_name = a.contact_name AND b.city = a.city AND b.customer_id > a.customer_id;

-- Candidato 4: nombre de empresa parecido, ignorando mayúsculas y espacios
SELECT a.customer_id, a.company_name, b.customer_id, b.company_name
FROM customers a JOIN customers b
  ON lower(replace(b.company_name, ' ', '')) = lower(replace(a.company_name, ' ', ''))
 AND b.customer_id > a.customer_id;

Las cuatro devuelven 0 filas. No hay duplicados en Northwind.

Y eso es la respuesta, entregada así:

  1. "No he encontrado ninguno" solo vale si dices qué buscaste. Un "no hay duplicados" a secas es indistinguible de "no supe buscarlos". Las cuatro consultas son la prueba, y cada una define "el mismo cliente" de una forma distinta.
  2. Ninguna de las cuatro encontraría los duplicados de verdad difíciles. Ana Trujillo Emparedados y Ana Trujillo Emparedados y helados son el mismo negocio y ninguna condición de igualdad los junta. Para eso hace falta similitud de texto —pg_trgm, distancia de edición— y entonces aparecen falsos positivos y alguien tiene que revisarlos a mano.
  3. La conclusión útil no es el resultado, es la propuesta: si los duplicados de clientes son un problema recurrente, se resuelve con una restricción UNIQUE en el momento del alta, no con una consulta que los busca después (restricciones). Buscar duplicados es limpiar; impedirlos es arreglar.
✓ SolucionesResuélvelos antes de abrir

1.

SELECT count(*) FROM categories CROSS JOIN region;   -- 32

8 × 4. El único count de todo el curso que se puede predecir multiplicando — precisamente porque el CROSS JOIN no depende de los datos.

2.

SELECT e.first_name || ' ' || e.last_name AS empleado,
       coalesce(j.first_name || ' ' || j.last_name, '(nadie)') AS jefe,
       coalesce(j.title, '—') AS cargo_del_jefe
FROM employees e
LEFT JOIN employees j ON j.employee_id = e.reports_to
ORDER BY e.employee_id;

LEFT JOIN + coalesce: los 9 salen, y Andrew Fuller aparece con (nadie) en vez de una celda vacía que parece un fallo de datos.

3.

SELECT p1.product_name AS producto_a, p2.product_name AS producto_b,
       p1.unit_price, p2.unit_price
FROM products p1
JOIN products p2 ON p2.category_id = p1.category_id
                AND p2.product_id  > p1.product_id
                AND abs(p1.unit_price - p2.unit_price) < 0.50
ORDER BY p1.unit_price DESC;

Devuelve 13 parejas. El p2.product_id > p1.product_id evita los espejos; abs() hace que la comparación funcione en los dos sentidos sin escribirla dos veces.

4.

SELECT CASE WHEN e.reports_to IS NULL THEN 'nivel 1 — sin jefe'
            WHEN j.reports_to IS NULL THEN 'nivel 2 — reporta al director'
            ELSE                           'nivel 3 — reporta a un mando'
       END      AS nivel,
       count(*) AS empleados
FROM employees e
LEFT JOIN employees j ON j.employee_id = e.reports_to
GROUP BY 1 ORDER BY 1;

             nivel             | empleados
-------------------------------+-----------
 nivel 1 — sin jefe            |         1
 nivel 2 — reporta al director |         5
 nivel 3 — reporta a un mando  |         3

1 + 5 + 3 = 9. ✓ El self-join da acceso a la fila del jefe, y por eso se puede preguntar por j.reports_to — el jefe del jefe. Cada nivel más exigiría otro JOIN; ahí es donde deja de compensar frente a una CTE recursiva.

5. 81 filas. 9 empleados × 9 empleados, producto cartesiano completo: no hay WHERE ni ON. Es la misma consulta que employees CROSS JOIN employees, escrita de la forma que no avisa.