Producto cartesiano en SQL: Guía completa

El producto cartesiano es un concepto fundamental de SQL que puede ser tan potente como complejo. Entender cómo utilizarlo eficazmente puede mejorar mucho tus capacidades de manipulación de datos. En esta guía, exploraremos los entresijos de los productos cartesianos, aportando ideas prácticas y ejemplos. 

Para los nuevos en SQL, considera empezar con nuestro curso SQL Intermedio para construir una base sólida. Además, la Hoja de trucos de SQL Básico, que puedes descargar, es una referencia útil porque contiene todas las funciones SQL más comunes.

¿Qué es un producto cartesiano?

El producto cartesiano en SQL es el resultado de combinar cada fila de una tabla con cada fila de otra. Lo más frecuente es que esta operación se genere a través de un CROSS JOIN, formando un conjunto de resultados que contiene todos los pares posibles de filas de las dos tablas. Aunque es potente, puede dar lugar a conjuntos de resultados muy grandes, sobre todo cuando se trabaja con tablas que contienen muchas filas, por lo que debe utilizarse con precaución. De hecho, apuesto a que muchos desarrolladores probablemente descubren por primera vez los productos cartesianos por las malas, omitiendo accidentalmente una condición de unión de algún tipo, para acabar con un conjunto de resultados masivo.

Antecedentes matemáticos

En matemáticas, un producto cartesiano (denotado como A × B) es un conjunto de todos los pares ordenados posibles creados emparejando cada elemento del conjunto A con cada elemento del conjunto B. En SQL, esto se traduce en emparejar cada fila de una tabla con cada fila de otra.

Ejemplo de producto cartesiano. Imagen del autor.

Unión cruzada SQL

La operación de unión cruzada en SQL crea un producto cartesiano entre dos tablas uniendo cada fila de la primera tabla con cada fila de la segunda. Como resultado, la unión cruzada genera todas las combinaciones de filas posibles para los datos. Veamos un ejemplo de realización de una operación de unión cruzada en SQL.

Supongamos que tienes dos tablas, Students y Courses:

Mesa de estudiantes

Tabla de cursos

En la siguiente consulta, se empareja a cada alumno con cada curso, mostrando su calificación y niveles de dificultad.

-- Perform the cross join to list each student with every course
SELECT Students.name, Students.grade, Courses.course, Courses.level
FROM Students
CROSS JOIN Courses;Con la tecnología de

Ejemplo de producto cartesiano en SQL resultante de una unión cruzada. Imagen del Autor.

Cuándo utilizar y cuándo no utilizar el producto cartesiano en SQL

El producto cartesiano en SQL puede ser muy útil cuando se comprende su comportamiento y se utiliza intencionadamente. Veamos los casos de uso (y los posibles escollos) de utilizar el producto cartesiano.

Casos prácticos

A continuación se exponen los supuestos en los que puede ser útil utilizar el producto cartesiano:

• Generar combinaciones: Un producto cartesiano es ideal para generar todas las combinaciones de dos conjuntos de valores. Por ejemplo, puede que quieras emparejar productos con descuentos, emparejar empleados con turnos o crear todos los emparejamientos posibles entre distintas categorías.
• Escenarios de prueba: Al ejecutar casos de prueba, un producto cartesiano puede ser útil para simular todos los escenarios posibles haciendo coincidir varios conjuntos de parámetros o configuraciones.
• Rellenar los datos que faltan: Un producto cartesiano también puede ayudar a identificar lagunas generando todas las combinaciones posibles y luego uniendo por la izquierda los resultados para encontrar las entradas que faltan, como las fechas que faltan en un análisis de series temporales.

Peligros potenciales

Aunque el producto cartesiano es útil para algunas situaciones, existen algunos riesgos, entre los que se incluyen los siguientes:

• Problemas de rendimiento: Como un producto cartesiano multiplica las filas de una tabla por las de otra, el resultado puede crecer exponencialmente. Si el conjunto de datos es demasiado grande, puede provocar tiempos de ejecución de las consultas lentos, un uso excesivo de la memoria e incluso fallos en la base de datos.
• Duplicación involuntaria de datos: Sin un filtrado cuidadoso, los productos cartesianos pueden producir filas que parezcan duplicadas o irrelevantes para el análisis, lo que lleva a interpretaciones sesgadas de los datos o a resultados incorrectos si los duplicados no se esperan o gestionan correctamente.
• Depuración difícil: Los productos cartesianos no intencionados, como olvidar especificar una condición de unión, pueden dar lugar a una unión cruzada en lugar de una unión interna o externa. Esto puede dar lugar a resultados inesperados que pueden ser difíciles de rastrear y corregir.

Aplicaciones reales del producto cartesiano

Aunque el producto cartesiano puede utilizarse con moderación debido a los riesgos potenciales, es útil en situaciones en las que necesitas combinar todos los pares posibles de elementos de datos.

Escenarios de combinación de datos

El producto cartesiano es útil para calcular métricas relativas de rendimiento, analizar la compatibilidad o crear sistemas de clasificación. Cuando necesites comparar todos los elementos de dos conjuntos de datos, puedes utilizar el producto cartesiano para emparejar cada elemento con todos los demás. 

Por ejemplo, para comparar el rendimiento de cada empleado con el de todos los demás, el producto cartesiano crearía todos los pares de empleados posibles:

-- Select two columns with employee names from the Employees table
SELECT 
    A.employee_name AS Employee1,
    B.employee_name AS Employee2
FROM Employees AS A
-- Cross join to create all possible pairs of employees
CROSS JOIN Employees AS B;Con la tecnología de

El producto cartesiano también es importante en el análisis del comercio electrónico para explorar toda la gama de emparejamientos producto-descuento. Supongamos que tienes tablas para Products y Discounts, una unión cruzada podría crear una lista de todas las combinaciones producto-descuento:

-- Select product names and discount types
SELECT 
    Products.product_name,
    Discounts.discount_type
FROM Products
-- Generate all product and discount combinations
CROSS JOIN Discounts;Con la tecnología de

Pruebas y validación

Un producto cartesiano permite crear todas las combinaciones potenciales de parámetros en entornos de prueba, lo que es esencial para una prueba de escenarios completa. Por ejemplo, si se prueba un sistema con varias configuraciones de sistema operativo, dispositivo y navegador, el uso de una unión cruzada garantiza que se prueban todas las configuraciones posibles.

-- Select combinations of operating systems, devices, and browsers
SELECT 
    OS.name AS OperatingSystem,
    Device.name AS Device,
    Browser.name AS Browser
FROM OperatingSystems AS OS
-- All combinations with devices
CROSS JOIN Devices AS Device
-- All combinations with browsers           
CROSS JOIN Browsers AS Browser;Con la tecnología de

Un producto cartesiano puede ayudar a identificar los registros que faltan, por lo que es útil para garantizar la integridad de los datos. Por ejemplo, al analizar los datos de ventas diarias y confirmar que existen datos para cada producto cada día, puedes crear un producto cartesiano de las tablas Products y Dates. A continuación, puedes unir por la izquierda los datos de ventas reales para identificar las entradas de fecha-producto que falten.

-- Select dates and products with no sales
SELECT 
    Dates.date,
    Products.product_name,
    Sales.sales_amount
FROM Dates
-- Create all date and product combinations
CROSS JOIN Products                          
-- Match sales by date and product
LEFT JOIN 
    Sales ON Dates.date = Sales.date 
           AND Products.product_name = Sales.product_name  
-- Filter for combinations with no sales
WHERE Sales.sales_amount IS NULL;Con la tecnología de

Algunas ideas sobre el rendimiento

Como los productos cartesianos generan posibles combinaciones de datos entre dos tablas, puede haber problemas de rendimiento importantes cuando se trata de tablas grandes. Sugiero las siguientes técnicas de optimización de consultas y gestión de grandes conjuntos de datos para mejorar el rendimiento de la base de datos.

Optimización de consultas

Considera los siguientes métodos para escribir consultas eficientes que incluyan productos cartesianos.

• Limitar el número de filas antes de unir: Para reducir el número de filas implicadas en el producto cartesiano, aplica filtros a cada tabla antes de realizar la unión cruzada. Por ejemplo, utiliza las cláusulas WHERE para filtrar cada tabla por criterios relevantes, minimizando el conjunto de datos resultante.

• Utiliza SELECCIONAR con columnas específicas: Cuando realices un producto cartesiano, especifica sólo las columnas que necesites en la cláusula SELECT para reducir el uso de memoria y mejorar el rendimiento. Evita utilizar SELECT *, ya que devuelve todas las columnas, lo que puede dar lugar a resultados grandes y poco manejables.

• Aplicar condiciones para convertir uniones cruzadas en uniones internas: Si sólo son relevantes determinadas combinaciones de filas, considera la posibilidad de añadir condiciones adicionales de unión cruzada que funcionen como INNER JOIN. Esto ayuda a restringir la salida limitando qué filas de cada tabla deben emparejarse.

• Procesamiento por lotes: Para grandes conjuntos de datos, divide las consultas en trozos manejables para procesarlos por lotes. Esto puede evitar el desbordamiento de memoria y mejorar el tiempo de respuesta, especialmente cuando se realizan consultas sobre tablas grandes.

Gestión de grandes conjuntos de datos

Utiliza las siguientes técnicas para gestionar los grandes conjuntos de datos que resultan de utilizar productos cartesianos.

• Indexación: Añade índices en las columnas unidas con frecuencia para mejorar el rendimiento al filtrar o emparejar tablas grandes. Aunque la indexación no acelera directamente las uniones cruzadas, es útil cuando más tarde filtras o unes el producto cartesiano con otras tablas, permitiendo que la base de datos acceda a las filas más rápidamente.

• Particionar tablas grandes: Particionar tablas puede mejorar el rendimiento de las consultas que implican productos cartesianos. El particionado divide las tablas en trozos más pequeños y manejables en función de las columnas especificadas, lo que permite a la base de datos leer sólo las particiones relevantes durante la ejecución de la consulta.

• Limitar filas de resultados: Utiliza la cláusula LIMIT para restringir el número de filas devueltas en los casos en que sólo necesites una muestra del producto cartesiano con fines de prueba o verificación.

• Utilizar Tablas Temporales para Resultados Intermedios: Almacena los resultados intermedios en tablas temporales si necesitas reutilizar un producto cartesiano varias veces para reducir los recálculos y acelerar las operaciones posteriores.

Otras operaciones SQL de ayuda

En situaciones en las que generar un producto cartesiano completo puede no ser eficiente, existen métodos alternativos. Los métodos que se describen a continuación te permiten conseguir los resultados deseados sin saturar la base de datos.

Juntas interiores y exteriores

Un INNER JOIN devuelve sólo las filas con una coincidencia entre las columnas especificadas en ambas tablas. Esta técnica limita el resultado a las combinaciones relevantes y evita las múltiples filas que conlleva un producto cartesiano. La unión interna es adecuada cuando sólo necesitas las filas que cumplen criterios específicos en ambas tablas. 

En el ejemplo siguiente, la página INNER JOIN sólo devuelve los productos y descuentos que comparten la misma categoría.

-- Select products and corresponding discounts by category
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products and discounts by category
INNER JOIN Discounts AS B ON A.category_id = B.category_id;Con la tecnología de

La unión externa (LEFT JOIN, RIGHT JOIN, y FULL JOIN) devuelve filas coincidentes y no coincidentes de una o ambas tablas. Esta técnica es útil para recuperar todas las filas de una tabla y las filas coincidentes de la otra, pero sin crear todas las combinaciones posibles. La unión externa es adecuada cuando quieres incluir datos no emparejados para el análisis, pero no un emparejamiento cartesiano completo.

El LEFT JOIN de la consulta siguiente devuelve todos los productos, incluidos los que no tienen un descuento correspondiente, con NULL valores para los descuentos no coincidentes.

-- Select products with corresponding discounts, (NULL if no matching discount)
SELECT 
    A.product_name,
    B.discount_type
FROM Products AS A
-- Match products with discounts by category
LEFT JOIN Discounts AS B ON A.category_id = B.category_id;  Con la tecnología de

Te recomiendo que sigas el curso Introducción a SQL Server de DataCamp para aprender más sobre cómo unir tablas y recuperar datos de varias tablas.

Subconsultas y CTEs

Las subconsultas te permiten seleccionar datos concretos de una tabla y utilizarlos para filtrar resultados en la consulta principal. Esto ayuda a evitar combinaciones de filas innecesarias, filtrando los datos sólo a las entradas relevantes. Las subconsultas son útiles cuando necesitas prefiltrar datos o calcular valores específicos para utilizarlos en la consulta principal sin necesidad de una unión cartesiana completa.

La subconsulta filtra los descuentos con valores superiores a 10 antes de unirlos, reduciendo los datos procesados y evitando una unión cruzada innecesaria.

-- Select products with discounts greater than 10 by category
SELECT 
    A.product_name,
    B.discount
FROM Products AS A
-- Subquery to filter discounts over 10
INNER JOIN 
    (SELECT discount, category_id   
     FROM Discounts 
     WHERE discount > 10) AS B
-- Match products with filtered discounts by category
ON A.category_id = B.category_id;   Con la tecnología de

Las CTE, o cláusulas WITH, permiten descomponer las consultas complejas en pasos. Este método ayuda a evitar los productos cartesianos filtrando o agregando datos antes de unirlos. Las CTE son adecuadas cuando se simplifican las consultas de varios pasos, especialmente cuando se preagrupan datos o se realizan uniones múltiples, pero se quiere evitar un resultado cartesiano completo.

La consulta siguiente utiliza una Expresión de Tabla Común (CTE) para filtrar los descuentos superiores a 10 antes de unirlos con los productos basados en la categoría.

-- Filter discounts and join with products by category
WITH FilteredDiscounts AS (
    SELECT discount, category_id
    FROM Discounts
    WHERE discount > 10
)
SELECT 
    A.product_name,
    FD.discount
FROM Products AS A
-- Join with filtered discounts by category
INNER JOIN 
    FilteredDiscounts AS FD ON A.category_id = FD.category_id;Con la tecnología de

Aplicaciones innovadoras del producto cartesiano

Aunque el producto cartesiano se utiliza mucho en las operaciones básicas de SQL, tiene aplicaciones más allá de los casos de uso tradicionales de las bases de datos. Veamos cómo puede utilizarse el producto cartesiano en la preparación de datos de aprendizaje automático y en la generación de conjuntos de datos sintéticos para simulación.

Preparación de datos de aprendizaje automático

Los conjuntos de datos diversos y completos son importantes a la hora de preparar los datos para el aprendizaje automático con el fin de entrenar modelos sólidos. Los productos cartesianos pueden crear un conjunto de datos completo que abarque todas las combinaciones posibles de valores de características, lo que permite a un modelo aprender de un conjunto más amplio de escenarios. 

Por ejemplo, al construir modelos de detección de fraude, podrías combinar tipos de transacciones, regiones y tipos de dispositivos para simular diversas condiciones, ayudando al modelo a generalizar entre distintos casos.

Generación de datos sintéticos

Los datos sintéticos se utilizan a menudo para probar sistemas en una amplia gama de condiciones o para proteger la privacidad evitando utilizar datos reales. Los productos cartesianos pueden ayudar a generar diversos conjuntos de datos sintéticos, útiles en aplicaciones que van desde las simulaciones hasta el aumento de datos para modelos de aprendizaje automático.

Por ejemplo, podrías combinar diferentes condiciones de mercado, tipos de interés y perfiles de clientes en simulaciones financieras para probar cómo se comporta un modelo o algoritmo en función de estas variables.

A continuación se muestra un ejemplo sencillo de cómo puedes crear datos sintéticos "de la nada", por así decirlo, en SQL y luego utilizar un CROSS JOIN para ampliarlos a un conjunto de datos mayor. La idea es que cada pequeña tabla represente una dimensión de nuestros datos de prueba y que, al cruzarlas, obtengamos todas las combinaciones posibles.

Ejemplo 1: Combinar varias dimensiones

Supón que quieres simular distintos escenarios financieros en función de las condiciones del Mercado, los tipos de interés y los perfiles de los Clientes. Podemos definir cada dimensión mediante una Expresión de Tabla Común (CTE) o una tabla temporal utilizando valores literales. Después, un CROSS JOIN los fusiona en todas las combinaciones posibles:

WITH MarketConditions AS (
    SELECT 'Bull' AS condition
    UNION ALL
    SELECT 'Bear'
    UNION ALL
    SELECT 'Sideways'
),
InterestRates AS (
    SELECT 1.5 AS rate
    UNION ALL
    SELECT 2.0
    UNION ALL
    SELECT 3.25
),
CustomerProfiles AS (
    SELECT 'Younger' AS profile
    UNION ALL
    SELECT 'Mid-career'
    UNION ALL
    SELECT 'Retired'
)
SELECT 
    mc.condition,
    ir.rate,
    cp.profile
FROM 
    MarketConditions mc
CROSS JOIN 
    InterestRates ir
CROSS JOIN 
    CustomerProfiles cp;Con la tecnología de

El conjunto de resultados tendrá 3×3×3 = 27 filas. Cada fila es una combinación única de condiciones del mercado, tipo de interés y perfil del cliente.

Ejemplo de generación de datos sintéticos utilizando el producto cartesiano. Imagen del autor.

Ejemplo 2: Generar filas utilizando secuencias o VALORES simples

Otra técnica consiste en generar un rango de números o intervalos de tiempo cruzando pequeñas tablas entre sí. Se puede utilizar para producir grandes volúmenes de filas sintéticas para pruebas rápidas. Por ejemplo, podemos utilizar la siguiente consulta para generar los números del 1 al 10.

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT n
FROM Nums;Con la tecnología de

Utilizar el producto cartesiano para generar filas mediante secuencias. Imagen del autor.

A continuación, podemos unir en cruz el mismo conjunto de 10 filas consigo mismo para obtener 10×10=100:

WITH
Nums AS (
    SELECT 1 AS n
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
)
SELECT A.n AS n1, B.n AS n2
FROM Nums A
CROSS JOIN Nums B
ORDER BY A.n, B.n;Con la tecnología de

Para los resultados, obtendrás 100 filas, cada fila con un par de valores (n1,n2). Puedes ampliar esta lógica a cientos, miles o incluso millones de filas, aunque ten en cuenta el rendimiento y el almacenamiento.

Utilizando estos enfoques -CTEs (o cláusulas VALUES ) para las definiciones de dimensión y CROSS JOIN para la combinación- puedes crear rápidamente conjuntos de datos sintéticos en SQL sin depender de las tablas existentes.

Conclusión

Comprender el producto cartesiano en SQL abre un abanico de posibilidades para la manipulación y el análisis de datos. Si dominas este concepto, podrás mejorar tu capacidad para trabajar con conjuntos de datos complejos y optimizar tus consultas a bases de datos. Para obtener conocimientos más avanzados, considera nuestro curso de Fundamentos de SQL Server para profundizar en tu experiencia. 

Dependiendo de la orientación de tu carrera, te recomiendo que te inscribas en el itinerario profesional de Analista de Datos Asociado en SQL de DataCamp para aprender los conocimientos del sector necesarios para convertirte en analista de datos, o que pruebes nuestro itinerario profesional de Ingeniero de Datos Asociado en SQL para convertirte en ingeniero de datos.