Tutorial SQL: CROSS JOIN y SELF JOIN

Producto cartesiano CROSS JOIN

El CROSS JOIN representa una de las operaciones más básicas pero potentes en SQL, permitiendo combinar cada fila de una tabla con todas las filas de otra tabla. Esta operación genera lo que en matemáticas se conoce como producto cartesiano entre dos conjuntos de datos.

Cuando realizamos un CROSS JOIN entre dos tablas, obtenemos una tabla resultante que contiene todas las combinaciones posibles entre las filas de ambas tablas. Si la primera tabla tiene M filas y la segunda tiene N filas, el resultado contendrá M × N filas.

Sintaxis básica

Existen dos formas principales de escribir un CROSS JOIN en SQL:

-- Sintaxis explícita
SELECT columnas
FROM tabla1
CROSS JOIN tabla2;

-- Sintaxis implícita (usando coma)
SELECT columnas
FROM tabla1, tabla2;

Ambas sintaxis producen el mismo resultado, aunque la primera (explícita) es más clara y se recomienda por su legibilidad.

Ejemplo práctico

Imaginemos que tenemos dos tablas simples:

-- Tabla de colores
CREATE TABLE colores (
    id INT PRIMARY KEY,
    nombre VARCHAR(20)
);

-- Tabla de tamaños
CREATE TABLE tamanios (
    id INT PRIMARY KEY,
    nombre VARCHAR(20)
);

-- Insertamos algunos datos
INSERT INTO colores VALUES (1, 'Rojo'), (2, 'Azul'), (3, 'Verde');
INSERT INTO tamanios VALUES (1, 'Pequeño'), (2, 'Mediano'), (3, 'Grande');

Si queremos generar todas las combinaciones posibles de colores y tamaños (por ejemplo, para un catálogo de productos), podemos usar un CROSS JOIN:

SELECT c.nombre AS color, t.nombre AS tamanio
FROM colores c
CROSS JOIN tamanios t;

El resultado sería:

color    | tamanio
---------+---------
Rojo     | Pequeño
Rojo     | Mediano
Rojo     | Grande
Azul     | Pequeño
Azul     | Mediano
Azul     | Grande
Verde    | Pequeño
Verde    | Mediano
Verde    | Grande

Como podemos ver, cada color se ha combinado con cada tamaño, generando 9 filas (3 colores × 3 tamaños).

Casos de uso

El CROSS JOIN es útil en varios escenarios:

• Generación de combinaciones: Como en el ejemplo anterior, para crear todas las variantes posibles de productos.

• Creación de tablas de referencia: Por ejemplo, generar una tabla con todas las fechas de un período:

-- Generar una tabla con todas las combinaciones de años y meses
SELECT a.anio, m.mes
FROM (SELECT 2022 AS anio UNION SELECT 2023 UNION SELECT 2024) a
CROSS JOIN (SELECT 1 AS mes UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
            UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
            UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) m
ORDER BY a.anio, m.mes;

• Cálculos matriciales: Para operaciones que requieren comparar cada elemento de un conjunto con todos los elementos de otro conjunto.

Consideraciones de rendimiento

Es importante tener en cuenta que un CROSS JOIN puede generar conjuntos de resultados muy grandes rápidamente:

• Si la tabla A tiene 1,000 filas y la tabla B tiene 1,000 filas, el resultado tendrá 1,000,000 de filas.
• Esto puede consumir muchos recursos de memoria y procesamiento.

Por esta razón, es recomendable:

• Limitar el tamaño de las tablas involucradas cuando sea posible.
• Filtrar los resultados después del JOIN para reducir el conjunto final.
• Evitar CROSS JOIN en tablas grandes a menos que realmente necesites todas las combinaciones.

Diferencia con INNER JOIN

Es importante distinguir entre CROSS JOIN e INNER JOIN:

-- CROSS JOIN: todas las combinaciones posibles
SELECT * FROM tabla1 CROSS JOIN tabla2;

-- INNER JOIN: solo las filas que cumplen la condición de unión
SELECT * FROM tabla1 INNER JOIN tabla2 ON tabla1.id = tabla2.id;

El CROSS JOIN no tiene una condición de unión, mientras que el INNER JOIN requiere una condición que relacione las tablas.

Ejemplo en MySQL y PostgreSQL

Tanto MySQL como PostgreSQL soportan la sintaxis estándar de CROSS JOIN:

-- Ejemplo en MySQL y PostgreSQL
SELECT p.nombre AS producto, c.nombre AS categoria
FROM productos p
CROSS JOIN categorias c;

En PostgreSQL, también puedes usar la palabra clave CROSS JOIN o la sintaxis de coma, aunque la primera es preferida por claridad:

-- Ambas consultas son equivalentes en PostgreSQL
SELECT * FROM tabla1 CROSS JOIN tabla2;
SELECT * FROM tabla1, tabla2;

Aplicación práctica: Matriz de distancias

Un caso de uso común es crear una matriz de distancias entre ubicaciones:

-- Crear una matriz de distancias entre ciudades
SELECT 
    c1.nombre AS origen,
    c2.nombre AS destino,
    ROUND(
        SQRT(POW(c1.coord_x - c2.coord_x, 2) + POW(c1.coord_y - c2.coord_y, 2))
    ) AS distancia
FROM ciudades c1
CROSS JOIN ciudades c2
WHERE c1.id <> c2.id  -- Excluimos la distancia de una ciudad a sí misma
ORDER BY origen, destino;

Este ejemplo calcula la distancia euclidiana entre cada par de ciudades, excluyendo la distancia de una ciudad a sí misma.

Combinación con otras cláusulas

El CROSS JOIN se puede combinar con otras cláusulas SQL para filtrar o manipular los resultados:

-- Combinación con WHERE para filtrar resultados
SELECT p.nombre AS producto, c.nombre AS color
FROM productos p
CROSS JOIN colores c
WHERE p.categoria = 'Electrónica'
ORDER BY p.nombre, c.nombre;

Esta consulta genera todas las combinaciones posibles de productos electrónicos con todos los colores disponibles.

SELF JOIN

El SELF JOIN es una técnica especial en SQL donde una tabla se une consigo misma. A diferencia de otros tipos de JOIN que combinan datos de tablas diferentes, el SELF JOIN permite relacionar filas dentro de la misma tabla, creando conexiones entre registros que comparten alguna relación lógica.

Esta técnica es particularmente útil cuando trabajamos con datos jerárquicos o cuando necesitamos comparar registros dentro de la misma tabla.

Sintaxis básica

Para realizar un SELF JOIN, necesitamos usar alias de tabla para distinguir entre las diferentes instancias de la misma tabla:

SELECT a.columna1, b.columna2
FROM tabla a
JOIN tabla b ON a.columna_relacion = b.otra_columna_relacion;

Donde a y b son simplemente alias que representan la misma tabla física, pero tratada como dos tablas lógicamente distintas en la consulta.

Ejemplo: Estructura jerárquica de empleados

Uno de los casos de uso más comunes para SELF JOIN es representar relaciones jerárquicas, como un organigrama de empleados:

CREATE TABLE empleados (
    id INT PRIMARY KEY,
    nombre VARCHAR(100),
    cargo VARCHAR(100),
    id_supervisor INT,
    FOREIGN KEY (id_supervisor) REFERENCES empleados(id)
);

INSERT INTO empleados VALUES 
(1, 'Ana García', 'CEO', NULL),
(2, 'Carlos Rodríguez', 'Director Comercial', 1),
(3, 'Elena Martínez', 'Director Técnico', 1),
(4, 'Pablo Sánchez', 'Vendedor', 2),
(5, 'Laura Fernández', 'Desarrolladora', 3),
(6, 'Miguel López', 'Desarrollador Senior', 3);

Para obtener cada empleado junto con el nombre de su supervisor, usaríamos:

SELECT e.nombre AS empleado, e.cargo, s.nombre AS supervisor, s.cargo AS cargo_supervisor
FROM empleados e
LEFT JOIN empleados s ON e.id_supervisor = s.id;

El resultado sería:

empleado          | cargo               | supervisor        | cargo_supervisor
------------------+---------------------+-------------------+------------------
Ana García        | CEO                 | NULL              | NULL
Carlos Rodríguez  | Director Comercial  | Ana García        | CEO
Elena Martínez    | Director Técnico    | Ana García        | CEO
Pablo Sánchez     | Vendedor            | Carlos Rodríguez  | Director Comercial
Laura Fernández   | Desarrolladora      | Elena Martínez    | Director Técnico
Miguel López      | Desarrollador Senior| Elena Martínez    | Director Técnico

Usamos LEFT JOIN en lugar de INNER JOIN para asegurarnos de incluir también a los empleados que no tienen supervisor (como el CEO).

Tipos de SELF JOIN

Podemos aplicar cualquier tipo de JOIN en un SELF JOIN:

• INNER SELF JOIN: Solo devuelve filas donde existe la relación.

SELECT a.nombre, b.nombre AS relacionado
FROM personas a
INNER JOIN personas b ON a.id_familiar = b.id;

• LEFT SELF JOIN: Incluye todas las filas de la primera instancia de la tabla, incluso si no tienen relación.

SELECT a.nombre, b.nombre AS relacionado
FROM personas a
LEFT JOIN personas b ON a.id_familiar = b.id;

• RIGHT SELF JOIN: Incluye todas las filas de la segunda instancia de la tabla.

SELECT a.nombre, b.nombre AS relacionado
FROM personas a
RIGHT JOIN personas b ON a.id_familiar = b.id;

• FULL SELF JOIN: Incluye todas las filas de ambas instancias (aunque en un SELF JOIN puro, esto puede ser redundante).

-- En PostgreSQL
SELECT a.nombre, b.nombre AS relacionado
FROM personas a
FULL JOIN personas b ON a.id_familiar = b.id;

Ejemplo: Encontrar pares de productos similares

Otro caso de uso común es encontrar registros relacionados o similares:

CREATE TABLE productos (
    id INT PRIMARY KEY,
    nombre VARCHAR(100),
    categoria VARCHAR(50),
    precio DECIMAL(10,2)
);

INSERT INTO productos VALUES
(1, 'Laptop HP', 'Electrónica', 899.99),
(2, 'Laptop Dell', 'Electrónica', 949.99),
(3, 'Monitor Samsung', 'Electrónica', 299.99),
(4, 'Teclado Logitech', 'Accesorios', 49.99),
(5, 'Mouse Logitech', 'Accesorios', 29.99);

Para encontrar productos de la misma categoría:

SELECT a.nombre AS producto1, b.nombre AS producto2, a.categoria
FROM productos a
JOIN productos b ON a.categoria = b.categoria AND a.id < b.id;

El resultado sería:

producto1        | producto2        | categoria
-----------------+------------------+------------
Laptop HP        | Laptop Dell      | Electrónica
Laptop HP        | Monitor Samsung  | Electrónica
Laptop Dell      | Monitor Samsung  | Electrónica
Teclado Logitech | Mouse Logitech   | Accesorios

Observa la condición a.id < b.id que evita duplicados y comparaciones de un producto consigo mismo.

Ejemplo: Encontrar registros consecutivos

El SELF JOIN también es útil para analizar secuencias o encontrar registros consecutivos:

CREATE TABLE mediciones (
    id INT PRIMARY KEY,
    fecha DATE,
    temperatura DECIMAL(5,2)
);

INSERT INTO mediciones VALUES
(1, '2023-01-01', 12.5),
(2, '2023-01-02', 13.2),
(3, '2023-01-03', 15.7),
(4, '2023-01-04', 14.3),
(5, '2023-01-05', 10.1);

Para calcular la variación de temperatura entre días consecutivos:

SELECT 
    a.fecha AS fecha_actual,
    a.temperatura AS temp_actual,
    b.fecha AS fecha_anterior,
    b.temperatura AS temp_anterior,
    (a.temperatura - b.temperatura) AS variacion
FROM mediciones a
LEFT JOIN mediciones b ON a.id = b.id + 1
ORDER BY a.fecha;

El resultado:

fecha_actual | temp_actual | fecha_anterior | temp_anterior | variacion
-------------+-------------+----------------+---------------+----------
2023-01-01   | 12.50       | NULL           | NULL          | NULL
2023-01-02   | 13.20       | 2023-01-01     | 12.50         | 0.70
2023-01-03   | 15.70       | 2023-01-02     | 13.20         | 2.50
2023-01-04   | 14.30       | 2023-01-03     | 15.70         | -1.40
2023-01-05   | 10.10       | 2023-01-04     | 14.30         | -4.20

Consideraciones de rendimiento

Al trabajar con SELF JOIN, es importante tener en cuenta:

• Tamaño de la tabla: Un SELF JOIN puede generar resultados muy grandes si no se filtra adecuadamente.
• Índices: Asegúrate de que las columnas utilizadas en la condición de JOIN estén indexadas.
• Condiciones de filtrado: Usa condiciones adicionales para limitar los resultados y evitar comparaciones innecesarias.

-- Ejemplo con filtrado adicional
SELECT a.nombre AS cliente, b.nombre AS cliente_similar
FROM clientes a
JOIN clientes b ON a.region = b.region AND a.id <> b.id
WHERE a.fecha_registro > '2023-01-01'
AND b.fecha_registro > '2023-01-01';

Aplicaciones prácticas en MySQL y PostgreSQL

Tanto MySQL como PostgreSQL soportan SELF JOIN con la misma sintaxis básica. Aquí hay algunos ejemplos adicionales:

Encontrar rutas de transporte con conexiones

CREATE TABLE rutas (
    origen VARCHAR(50),
    destino VARCHAR(50),
    distancia INT,
    PRIMARY KEY (origen, destino)
);

INSERT INTO rutas VALUES
('Madrid', 'Barcelona', 620),
('Barcelona', 'Valencia', 350),
('Valencia', 'Sevilla', 650),
('Madrid', 'Sevilla', 530);

-- Encontrar rutas con una escala
SELECT 
    r1.origen AS inicio,
    r1.destino AS escala,
    r2.destino AS final,
    (r1.distancia + r2.distancia) AS distancia_total
FROM rutas r1
JOIN rutas r2 ON r1.destino = r2.origen
WHERE r1.origen <> r2.destino
ORDER BY distancia_total;

Comparar ventas entre períodos

CREATE TABLE ventas_mensuales (
    mes INT,
    anio INT,
    total DECIMAL(10,2),
    PRIMARY KEY (mes, anio)
);

INSERT INTO ventas_mensuales VALUES
(1, 2023, 45000),
(2, 2023, 52000),
(3, 2023, 48000),
(1, 2024, 47500),
(2, 2024, 55000),
(3, 2024, 51000);

-- Comparar ventas con el mismo mes del año anterior
SELECT 
    actual.mes,
    actual.anio AS anio_actual,
    actual.total AS ventas_actuales,
    anterior.anio AS anio_anterior,
    anterior.total AS ventas_anteriores,
    (actual.total - anterior.total) AS diferencia,
    ROUND(((actual.total - anterior.total) / anterior.total) * 100, 2) AS porcentaje
FROM ventas_mensuales actual
JOIN ventas_mensuales anterior ON actual.mes = anterior.mes AND actual.anio = anterior.anio + 1
ORDER BY actual.mes;

Combinación con otras cláusulas SQL

El SELF JOIN se puede combinar con otras cláusulas SQL para análisis más complejos:

-- Encontrar empleados que ganan más que su supervisor
SELECT 
    e.nombre AS empleado,
    e.salario AS salario_empleado,
    s.nombre AS supervisor,
    s.salario AS salario_supervisor
FROM empleados e
JOIN empleados s ON e.id_supervisor = s.id
WHERE e.salario > s.salario
ORDER BY e.salario DESC;

-- Agrupar y contar subordinados directos por supervisor
SELECT 
    s.nombre AS supervisor,
    COUNT(e.id) AS num_subordinados
FROM empleados s
LEFT JOIN empleados e ON s.id = e.id_supervisor
GROUP BY s.id, s.nombre
ORDER BY num_subordinados DESC;

El SELF JOIN es una técnica versátil que permite resolver problemas complejos de datos relacionales utilizando una sola tabla, especialmente útil para estructuras jerárquicas, comparaciones entre registros y análisis de secuencias temporales.