JOIN SQL: combinaciones y relaciones entre tablas

La dirección de un cliente pertenece a los datos del cliente. La fecha de un pedido pertenece a los datos del pedido. Guardar estos datos en tablas separadas reduce la repetición, pero un informe suele necesitar datos de ambas. Una combinación une sus filas dentro de una consulta.

Lee las claves

Una clave primaria identifica una fila. Puede ser una columna o varias columnas juntas. Una clave foránea hace referencia a una clave primaria u otra clave única, ya sea de otra tabla o de la misma tabla. En la base de datos de la tienda, orders.customer_id apunta a customers.customer_id.

Los pedidos 101 y 102 hacen referencia al cliente 1, Ana. El pedido 103 hace referencia a Ben. El pedido 104 es una compra como invitado: su clave de cliente es NULL, por lo que no hace referencia a ningún cliente. El número de pedido identifica el pedido; no identifica al cliente. Combinar order_id con customer_id compararía identificadores sin relación.

Identifica la relación

La relación cliente–pedido es uno a muchos. Un cliente puede no tener pedidos, tener uno o tener varios. Eso no significa que la tabla de clientes almacene un identificador de pedido por cada compra. Cada pedido almacena su propia referencia al cliente.

Para estudiantes y cursos, ninguno de los lados puede contener una sola referencia que describa todos los vínculos. Una fila de enrollments registra un par estudiante–curso. Varias filas de inscripción pueden hacer referencia al mismo estudiante, y varias pueden hacer referencia al mismo curso.

Traza una ruta antes de escribir SQL

Para enumerar los productos comprados por cada cliente, sigue customers → orders → order_items → products. Los vínculos utilizan customer_id, después order_id y después product_id. Cada vínculo necesita su propia condición. Una línea de pedido proporciona la conexión que falta entre un pedido y un producto.

Interpreta el significado del esquema, no solo cómo se escriben las columnas. employees.manager_id puede hacer referencia a employees.emp_id, aunque sus nombres difieran. En cambio, customers.name y products.name nombran cosas de distinto tipo y no deben compararse solo porque ambas se llamen name.

Una clave foránea declarada es una guía útil, pero SQL también puede combinar tablas sin una restricción declarada. Un precio puede coincidir con un intervalo de precios, y dos empleados pueden coincidir porque comparten responsable. La condición debe expresar la relación que necesita la pregunta.

Una combinación construye filas nuevas al combinar filas de entrada. Empieza por la regla de coincidencia y después pregúntate cuántas parejas tiene cada fila. La respuesta determina tanto qué filas aparecen como cuántas veces se repiten sus valores.

Piensa en pares

Para una combinación interna, imagina cada cliente junto a cada pedido. Conserva un par solo cuando o.customer_id = c.customer_id sea verdadero. El pedido 101 se empareja con Ana; no se empareja con Ben, Chloe ni Dev. El pedido 102 también se empareja con Ana. Por tanto, un cliente puede aportar varias filas al resultado.

La condición puede ser verdadera, falsa o desconocida. Una igualdad en la que interviene NULL es desconocida, por lo que el pedido de invitado 104 no encuentra ningún cliente coincidente. Una combinación externa también conserva las filas sin coincidencia del lado elegido y rellena las columnas de la pareja ausente con NULL.

Esta representación par a par explica las combinaciones mejor que los círculos superpuestos. Una combinación no se limita a reunir elementos compartidos: construye filas combinadas, y las claves repetidas pueden generar más filas de salida de las que contiene cualquiera de las entradas. Si dos filas de un lado y tres del otro comparten una clave, esa clave produce seis pares coincidentes.

Elige las columnas independientemente de las coincidencias

La tabla de clientes de la tienda tiene tres columnas y la de pedidos tiene cuatro. Con JOIN ... ON, SELECT * devuelve las siete, incluidas ambas copias de customer_id. En su lugar, puedes seleccionar solo los campos que necesita el informe:

SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

El resultado es (Ana; 101; 22,00), (Ana; 102; 25,50) y (Ben; 103; 60,00). Las columnas clave realizan su función de comparación sin tener que aparecer en la salida seleccionada. Seleccionar menos columnas cambia la anchura del resultado, no los pares de entrada que coinciden.

Asigna a las tablas nombres cortos y claros

En FROM customers c, c es un alias de tabla. Haz referencia a las columnas de esa tabla como c.name y c.customer_id. La expresión c.name es un nombre de columna cualificado: la parte anterior al punto indica qué papel de tabla proporciona el valor.

Ambas tablas combinadas contienen customer_id. Escribir solo SELECT customer_id con la forma ON impide que la base de datos elija entre ellas y provoca un error por referencia de columna ambigua. Escribe c.customer_id u o.customer_id según el valor que necesites. Cualificar las demás columnas seleccionadas también facilita ver su origen.

AS antes de un alias de tabla es opcional en PostgreSQL, SQLite, MySQL y SQL Server. Oracle no lo acepta, por lo que FROM customers c es una forma común útil. Después de asignar un alias a una tabla, utilízalo de forma coherente.

Lee el orden lógico de la consulta

FROM y sus combinaciones construyen las filas combinadas. WHERE filtra esas filas. SELECT elige las columnas de salida y ORDER BY ordena el resultado. Un filtro puede utilizar una columna combinada aunque esa columna no esté seleccionada. Por ejemplo, WHERE c.city = 'Denver' puede filtrar un informe que solo muestra nombres y totales de pedidos.

Sin ORDER BY, la base de datos no garantiza ningún orden concreto de filas. Una consulta que hoy muestra primero a Ana puede mostrar otra fila primero más adelante; ordenar es una instrucción independiente de buscar coincidencias.

Utiliza una combinación interna cuando una fila de resultado necesite una coincidencia en ambas tablas. JOIN e INNER JOIN significan lo mismo. Ninguna de las dos formas conserva una fila que no encuentra pareja.

Construye la consulta en tres partes

Primero nombra las tablas y sus papeles. Después expresa la relación en ON. Por último, elige las columnas que quieres mostrar.

SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Lee la condición como «la referencia al cliente del pedido es igual al identificador de este cliente». Para el pedido 101, la referencia es 1, por lo que se empareja con Ana. El pedido 102 también tiene la referencia 1, y el pedido 103 tiene la referencia 2. El resultado completo es:

Ana aparece dos veces porque tiene dos pedidos coincidentes. Chloe y Dev no tienen ninguno, por lo que no aparecen. El pedido de invitado 104 tiene una referencia de cliente NULL, que no satisface ninguna igualdad con una clave de cliente.

Filtra y ordena las filas combinadas

Para mostrar solo los clientes de Denver, coloca WHERE c.city = 'Denver' después de la combinación y antes de ORDER BY. El resultado pasa a ser (Ana; 101; 22,00) y (Ana; 102; 25,50). Chloe también vive en Denver, pero la combinación interna nunca produjo una fila para ella.

En una combinación interna, colocar este filtro en ON da los mismos pares. Una convención que facilita la lectura es mantener las condiciones de coincidencia entre tablas en ON y los filtros del informe en WHERE. Esta equivalencia no se extiende a las combinaciones externas.

Puedes ordenar por una columna combinada que no aparezca en la salida seleccionada. También puedes omitir las claves de combinación de la salida: la consulta anterior compara por customer_id mientras muestra un nombre, un número de pedido y un total. La función de una columna en la coincidencia es independiente de su función en el informe.

Compara todas las partes de una clave

Algunos registros se identifican mediante un par de columnas. Una línea de envío que hace referencia a una línea de pedido puede necesitar tanto el identificador del pedido como el del producto:

SELECT s.order_id, s.product_id, oi.quantity
FROM shipment_lines s
JOIN order_items oi
  ON s.order_id = oi.order_id
 AND s.product_id = oi.product_id;

Ambas comparaciones deben ser verdaderas para que haya coincidencia. Comparar solo order_id asociaría cada línea de envío con cada línea de producto de ese pedido. La consulta podría ejecutarse correctamente y, aun así, describir cantidades de producto incorrectas.

Cambia el orden sin cambiar el significado

Escribir FROM orders o JOIN customers c ON c.customer_id = o.customer_id da los mismos pares cliente–pedido. Invertir los dos lados de = también conserva una igualdad. SELECT * puede disponer las columnas de otra forma después de intercambiar las tablas, y el orden de las filas sigue sin especificarse salvo que se ordenen.

Un cambio mucho más grave es ON o.order_id = c.customer_id. Ambas columnas contienen números, por lo que es SQL válido, pero identifican cosas distintas. En los datos de la tienda no devuelve ninguna fila: los identificadores de pedido 101–104 nunca son iguales a los identificadores de cliente 1–4. Comprueba la relación del esquema aunque la base de datos no indique ningún error de sintaxis.

ON hace explícita una condición, pero el SQL existente puede utilizar formas más cortas o antiguas. Lee cada forma preguntándote qué pares conserva. Algunas también cambian cómo aparecen las columnas compartidas en la salida.

USING nombra la clave compartida

Cuando las columnas clave tienen el mismo nombre, esta consulta utiliza una igualdad abreviada:

SELECT c.name, o.order_id
FROM customers c
JOIN orders o USING (customer_id)
ORDER BY o.order_id;

Devuelve (Ana, 101), (Ana, 102) y (Ben, 103). USING (customer_id) significa comparar las dos columnas customer_id. Con SELECT *, devuelve seis columnas y muestra la clave compartida una sola vez; JOIN ... ON incluye ambas columnas clave de entrada.

Para una clave compuesta, enumera ambos nombres: USING (order_id, product_id). Cada columna de la lista debe existir con ese nombre en ambas entradas.

PostgreSQL, SQLite, MySQL y Oracle admiten USING; SQL Server no. En Oracle, una columna nombrada en USING debe referenciarse sin cualificar, así que escribe SELECT customer_id, no SELECT c.customer_id. Las demás columnas pueden seguir utilizando sus alias de tabla.

NATURAL JOIN elige las columnas por ti

Un NATURAL JOIN compara todas las columnas cuyos nombres comparten las entradas, une esas pruebas de igualdad con AND y muestra las columnas compartidas una sola vez. Lo admiten PostgreSQL, SQLite, MySQL y Oracle, pero no SQL Server.

Esa elección automática puede cambiar una consulta sin avisar. Actualmente, clientes y pedidos solo comparten customer_id. Añadir una columna city a pedidos hace que una combinación natural exija que coincidan tanto el identificador del cliente como la ciudad. Una ciudad de entrega distinta de la del cliente puede entonces eliminar un pedido que antes coincidía.

Clientes y productos tienen name, pero una columna nombra una persona y la otra un producto. Su combinación natural compararía esos nombres sin relación y no devuelve filas en los datos de la tienda. Si no hay nombres de columna compartidos, una combinación natural devuelve todas las combinaciones. Utiliza ON o una lista USING elegida deliberadamente para mantener visible la regla de coincidencia.

Lee las combinaciones antiguas mediante comas

SELECT c.name, o.order_id
FROM customers c, orders o
WHERE o.customer_id = c.customer_id;

Esto devuelve los mismos tres pares que la combinación interna. La condición está ahora en WHERE. Si la olvidas, los cuatro clientes se emparejan con los cuatro pedidos y producen 16 filas. JOIN ... ON mantiene cada tabla junto a su propia condición, lo que facilita comprobar consultas más largas.

Relaciona un valor con un intervalo

Una equicombinación utiliza la igualdad. Una combinación por desigualdad utiliza otra comparación, como una prueba de intervalo:

SELECT p.name, b.band
FROM products p
JOIN price_bands b
  ON p.price BETWEEN b.min_price AND b.max_price;

Los intervalos son Budget 0,00–4,99, Standard 5,00–19,99 y Premium 20,00–999,99. Mug a 8,00 y Stapler a 12,00 coinciden con Standard; Pen a 1,50 y Notebook a 4,25 coinciden con Budget. Premium no tiene ningún producto coincidente y no aporta ninguna fila.

BETWEEN incluye ambos extremos. Un nuevo intervalo Gift de 8,00 a 15,00 daría a Mug y Stapler una segunda coincidencia a cada uno. El hueco entre 4,99 y 5,00 dejaría un precio de 4,995 sin coincidencia. Los intervalos determinan el resultado igual que las claves: un valor puede tener cero, una o varias coincidencias.

Un informe que pide «todos los clientes, incluidos los clientes sin pedidos» necesita una combinación externa izquierda. LEFT JOIN conserva todas las filas izquierdas. Incorpora cada fila derecha coincidente o proporciona una fila de valores NULL cuando no hay coincidencia. La palabra OUTER es opcional.

Sigue la tabla conservada

SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Ana sigue aportando dos filas. Una combinación izquierda no reduce los pedidos de un cliente a uno. Chloe y Dev aportan exactamente una fila cada uno porque ninguno tiene coincidencia. Hay cinco filas en total: tres pares coincidentes y dos clientes sin coincidencia.

La combinación devuelve al menos tantas filas como su entrada izquierda antes de aplicar filtros posteriores. Para cada fila izquierda, cuenta sus coincidencias; si no tiene ninguna, cuenta una fila completada con NULL. Esto explica por qué cuatro clientes pueden producir cinco filas.

El pedido de invitado 104 no aparece. Conservar todos los clientes no conserva también los pedidos sin coincidencia. Para conservar todos los pedidos, empieza con orders o LEFT JOIN customers c ON c.customer_id = o.customer_id. Seleccionar después o.order_id, c.name da (101, Ana), (102, Ana), (103, Ben) y (104, NULL). Chloe y Dev ya no aparecen.

Encuentra filas sin pareja

Una anticombinación izquierda conserva solo las filas que no encontraron coincidencia:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

La combinación izquierda crea primero las cinco filas. Después, WHERE conserva a Chloe y Dev, cuyas claves de pedido son NULL. Su pareja ausente es exactamente lo que busca la consulta.

Elige una columna del lado derecho que no pueda ser NULL en una coincidencia real. La clave primaria de un pedido es una buena prueba. Comprobar un coupon_code opcional también conservaría pedidos reales coincidentes sin cupón, por lo que no significaría «sin pedido». Utiliza IS NULL: = NULL se evalúa como desconocido y no conserva ninguna fila en WHERE.

El mismo patrón encuentra productos sin vender:

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_id IS NULL;

Devuelve (Stapler), el único producto sin línea de pedido. Aquí, una línea coincidente real tiene necesariamente un identificador de pedido, por lo que la prueba detecta la línea ausente.

Interpreta un valor ausente antes de sustituirlo

Una fila de combinación externa completada con NULL significa que no coincidió ninguna pareja. En cambio, un NULL almacenado en un pedido real puede significar que no se proporcionó un campo. Son situaciones distintas aunque ambas se muestren como NULL.

COALESCE(o.total, 0) elige el primer argumento que no sea NULL. Mostraría 0 para Chloe y Dev. Eso puede ser útil para un informe de gasto, pero un cero en una columna titulada «total del pedido» puede sugerir un pedido real de valor cero. Mantén el encabezado y el significado del informe en consonancia.

A veces, las filas necesarias están a la derecha de una combinación. A veces, deben conservarse ambas entradas para poder comparar los registros ausentes. Las combinaciones externas derechas y las completas hacen explícitas estas elecciones.

Reescribe una combinación derecha como una izquierda

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;

Esto conserva todos los pedidos: (Ana, 101), (Ana, 102), (Ben, 103) y (NULL, 104). La tabla derecha es orders, por lo que el pedido de invitado se conserva. Chloe y Dev son filas izquierdas sin coincidencia y no se conservan.

Intercambiar las entradas da la forma equivalente orders o LEFT JOIN customers c ON o.customer_id = c.customer_id. Mantener la misma lista explícita de selección da los mismos valores. Con SELECT *, puede cambiar el orden predeterminado de las columnas. Utilizar combinaciones izquierdas en todo un informe suele facilitar el seguimiento del lado que debe conservarse.

Conserva las filas sin coincidencia de ambas entradas

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

El resultado contiene (Ana, 101), (Ana, 102), (Ben, 103), (Chloe, NULL), (Dev, NULL) y (NULL, 104). Son tres pares coincidentes, dos clientes sin coincidencia y un pedido sin coincidencia: seis filas. Las columnas de cliente son NULL para el pedido de invitado 104; las columnas de pedido son NULL para Chloe y Dev.

Una combinación completa es útil para la conciliación de resultados, como comprobar una lista de existencias de almacén frente a un recuento físico. Las claves coincidentes aparecen juntas; las que solo se encuentran en un lado permanecen visibles. En la consulta de la tienda, añadir WHERE c.customer_id IS NULL OR o.order_id IS NULL conserva solo a Chloe, Dev y el pedido 104.

Al conciliar dos listas con el mismo tipo de clave, cualquiera de las claves de entrada puede ser NULL en la salida. COALESCE(w.sku, k.sku) AS sku selecciona el SKU disponible del alias de almacén w o del alias de lista de recuento k. Una columna clave combinada permite que el informe identifique los registros ausentes de cualquiera de las listas.

Comprueba la compatibilidad de la sintaxis

PostgreSQL, SQL Server y Oracle admiten combinaciones externas derechas y completas. SQLite admite ambas a partir de la versión 3.39.0, publicada en 2022. Las versiones anteriores de SQLite las rechazan. MySQL admite combinaciones derechas, pero no tiene FULL OUTER JOIN; una alternativa combina los resultados de combinaciones izquierdas y derechas con UNION.

El SQL antiguo de Oracle puede marcar el lado opcional con (+) en WHERE. Por ejemplo, WHERE c.customer_id = o.customer_id(+) conserva los clientes y rellena las columnas de pedido ausentes con NULL, como una combinación izquierda desde clientes. El marcador pertenece al lado que puede estar ausente. Utiliza las formas de combinación con nombre LEFT, RIGHT y FULL al escribir consultas nuevas.

Una combinación externa separa dos decisiones: qué pares cuentan como coincidencias y qué filas del resultado terminado conserva el informe. ON toma la primera decisión. WHERE toma la segunda, después de que las filas sin coincidencia hayan recibido valores NULL.

Conserva a todos, pero limita sus pedidos coincidentes

Supón que un informe necesita todos los clientes y solo sus pedidos a partir del 10 de marzo:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= '2026-03-10';

La prueba de fecha forma parte de la coincidencia. El pedido 101 de Ana es demasiado temprano, pero el pedido 102 cumple la condición. El pedido 103 de Ben también la cumple. Chloe y Dev no tienen ninguna pareja que la cumpla, por lo que cada uno recibe una fila completada con NULL. El resultado es (Ana, 102), (Ben, 103), (Chloe, NULL) y (Dev, NULL).

Filtra las filas después de la combinación

Mueve la misma prueba de fecha a WHERE:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-03-10';

Ahora la combinación crea primero cinco filas, incluidas las filas completadas con NULL para Chloe y Dev. La comparación de fecha es desconocida para esos valores NULL, por lo que WHERE las elimina. También elimina el pedido temprano de Ana. Solo quedan (Ana, 102) y (Ben, 103).

La versión con WHERE produce las mismas filas coincidentes que una combinación interna con ese filtro de fecha.

Filtra deliberadamente la tabla conservada

Una condición del lado izquierdo en ON tampoco puede eliminar filas izquierdas. Considera ON o.customer_id = c.customer_id AND c.city = 'Denver'. Ana puede coincidir con ambos pedidos, Chloe no tiene ninguno, y Ben y Dev no cumplen la parte de ciudad de ninguna coincidencia posible. Los cuatro clientes permanecen, con NULL en las columnas de pedido para Ben, Chloe y Dev.

Para pedir solo los clientes de Denver, coloca WHERE c.city = 'Denver' después de la condición de clave habitual. El resultado es (Ana, 101), (Ana, 102) y (Chloe, NULL). Chloe se conserva porque el filtro comprueba su propia ciudad, no un campo de pedido ausente.

La prueba de anticombinación va después de la combinación porque examina el resultado completado con NULL. Las combinaciones internas no tienen filas sin coincidencia conservadas, por lo que mover un filtro entre ON y WHERE no cambia sus pares coincidentes. Al revisar una combinación externa, lee cada prueba WHERE del lado opcional y comprueba qué hace con una fila completada con NULL.

El responsable de un empleado también es un empleado. Una autocombinación lee la misma tabla con distintos alias para que una fila pueda representar al empleado y otra al responsable. Utiliza la sintaxis de combinación habitual; no existe una palabra clave SELF JOIN.

Nombra los papeles antes que las columnas

Los datos de empleados son:

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id;

e proporciona la fila del empleado. Su manager_id apunta al emp_id del responsable en el papel m. Hassan apunta a 1, por lo que se empareja con Grace; Iris también se empareja con Grace. Jamal apunta a 2 y se empareja con Hassan. El resultado es (Hassan, Grace), (Iris, Grace) y (Jamal, Hassan).

Grace no tiene responsable. Su manager_id es NULL, por lo que la combinación interna la excluye. Sustituir JOIN por LEFT JOIN añade (Grace, NULL) y produce cuatro filas. Los alias distintos permiten que la base de datos distinga los papeles aunque ambas entradas procedan de la misma tabla almacenada.

Formula la pregunta en la otra dirección

Para encontrar personas que no tienen a nadie a su cargo, empieza por el papel de responsable y busca un empleado ausente:

SELECT m.name
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.emp_id
WHERE e.emp_id IS NULL;

Esto devuelve (Iris) y (Jamal). Grace coincide con Hassan e Iris; Hassan coincide con Jamal. Iris y Jamal no tienen empleados directamente a su cargo. Comprobar la clave del empleado después de la combinación encuentra su pareja ausente.

Un tercer alias sigue un nivel más. employees e JOIN employees m ON e.manager_id = m.emp_id JOIN employees g ON m.manager_id = g.emp_id, seleccionando sus nombres, devuelve (Jamal, Hassan, Grace). Cada papel adicional sigue un vínculo más. Una consulta fija alcanza un número fijo de niveles; una jerarquía de profundidad arbitraria necesita una consulta recursiva.

Empareja a los compañeros una sola vez

La misma tabla también puede proporcionar pares de compañeros de trabajo:

SELECT a.name, b.name
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
 AND a.emp_id < b.emp_id;

Hassan e Iris comparten el responsable 1. La prueba de menor que excluye a una persona emparejada consigo misma y conserva solo un orden de cada par. El resultado es (Hassan, Iris).

Con solo la igualdad de responsable, el resultado tiene cinco filas: Hassan–Hassan, Hassan–Iris, Iris–Hassan, Iris–Iris y Jamal–Jamal. La clave de responsable NULL de Grace no coincide con nada, ni siquiera con otro NULL. Sustituir < por <> excluye los emparejamientos de una persona consigo misma, pero conserva tanto (Hassan, Iris) como (Iris, Hassan). Elige < cuando la pregunta necesite cada par no ordenado una sola vez.

Una combinación cruzada construye todos los pares de filas, sin condición de coincidencia. Esto resulta útil cuando la pregunta pide posibilidades, como todas las combinaciones de talla y color que una tienda podría tener en existencias.

Construye la lista de combinaciones

sizes contiene S, M y L. colors contiene Red y Blue.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Los seis pares son (S, Red), (S, Blue), (M, Red), (M, Blue), (L, Red) y (L, Blue). Cada talla aparece junto a ambos colores. Estas son las filas del resultado; su orden de presentación no está garantizado sin ORDER BY.

Para dos entradas, multiplica sus números de filas. Tres tallas y dos colores dan seis filas. Con una tercera tabla que contiene cuatro opciones, el resultado tiene 24 filas. Si alguna entrada está vacía, no puede formarse ninguna combinación completa y el resultado tiene cero filas.

Empieza con una cuadrícula completa para informes

Los registros de ventas solo muestran las combinaciones que se produjeron. Para mostrar cada tienda en cada mes, forma primero una cuadrícula con stores CROSS JOIN months. Después, utiliza una combinación izquierda para incorporar los registros de ventas coincidentes:

SELECT s.store_id, m.month, x.total
FROM stores s
CROSS JOIN months m
LEFT JOIN sales x
  ON x.store_id = s.store_id
 AND x.month = m.month;

La cuadrícula conserva un par tienda–mes aunque no haya ventas, con NULL en x.total. Ambas partes de la clave importan: comparar solo la tienda incorporaría también las ventas de esa tienda de otros meses. La combinación cruzada define qué combinaciones debe mostrar el informe, y la combinación izquierda proporciona los datos disponibles sobre ellas.

Detecta una combinación cruzada no intencionada

FROM customers, orders sin una condición de combinación genera 16 pares a partir de los cuatro clientes y los cuatro pedidos. Ana se empareja con sus propios pedidos, con el pedido de Ben y con el pedido de invitado. Lo mismo sucede con cada cliente. Eso solo resulta útil si realmente se desean todas las combinaciones cliente–pedido.

Un número elevado de filas es una pista, pero examina también los emparejamientos. Con 1000 clientes y 1000 productos, una combinación cruzada intencionada genera 1 000 000 de filas. Una combinación con una condición incorrecta pero selectiva también puede devolver un resultado menor e incorrecto. Compara el recuento y el significado de cada par con la pregunta.

Reconoce las formas equivalentes

CROSS JOIN, una combinación mediante comas sin condición de coincidencia y una combinación interna con ON 1 = 1 conservan todos los pares. SQLite y MySQL también aceptan un JOIN simple sin ON y lo tratan como una combinación cruzada; PostgreSQL exige ON o USING para una combinación simple. Escribe CROSS JOIN cuando quieras todas las combinaciones.

DISTINCT elimina las filas seleccionadas idénticas, no las relaciones incorrectas. Si una combinación cruzada accidental empareja a Ana con el pedido de Ben, esos valores siguen formando una fila distinta. Corrige la condición ausente en lugar de ocultar parte de la salida repetida.

Un estudiante puede cursar varios cursos y un curso puede tener varios estudiantes. Una tabla intermedia registra estos vínculos muchos a muchos como pares individuales. También se llama tabla puente, de enlace o de asociación.

Lee un vínculo por fila

Los datos de ejemplo contienen los estudiantes 1 Mia, 2 Noah y 3 Omar; los cursos 10 Algebra, 20 Biology y 30 Chemistry; y los pares de inscripción (1, 10), (1, 20) y (2, 10).

El estudiante 1 aparece dos veces porque Mia cursa dos cursos. El curso 10 aparece dos veces porque Algebra tiene dos estudiantes. Ninguna columna tiene que ser única por sí sola. El par puede formar una clave primaria compuesta, PRIMARY KEY (student_id, course_id), que impide inscribir al mismo estudiante dos veces en el mismo curso.

Un vínculo puede tener sus propios datos. enrolled_on pertenece a la inscripción porque describe cuándo un estudiante concreto se incorporó a un curso concreto. Del mismo modo, order_items.quantity describe qué cantidad de un producto concreto pertenece a un pedido concreto.

Atraviesa la tabla intermedia con dos combinaciones

SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id;

La primera condición incorpora las filas de inscripción de cada estudiante. La segunda sustituye la referencia al curso de cada inscripción por datos de la tabla de cursos. El resultado completo es (Mia, Algebra), (Mia, Biology) y (Noah, Algebra). Cada fila representa una inscripción.

No hay ninguna clave que vincule estudiantes con cursos en las tablas de los extremos. Comparar s.student_id = c.course_id compararía identificadores sin relación y omitiría los datos de inscripción. La tabla intermedia proporciona la relación.

La misma ruta funciona en ambas direcciones. Añade WHERE c.title = 'Algebra' para obtener (Mia, Algebra) y (Noah, Algebra). Añade en su lugar WHERE s.name = 'Mia' para obtener (Mia, Algebra) y (Mia, Biology). Los vínculos siguen siendo los mismos; el filtro selecciona cuáles responden a la pregunta.

Incluye personas o cursos sin vínculos

Empieza por el lado que debe conservarse. students s LEFT JOIN enrollments e ON e.student_id = s.student_id conserva a Omar con NULL en las columnas de inscripción. Para mostrar los títulos de los cursos y conservarlo, la combinación posterior de inscripciones con cursos también debe conservar la inscripción ausente.

Para encontrar cursos que nadie cursa, utiliza el lado de cursos y una anticombinación:

SELECT c.title
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
WHERE e.student_id IS NULL;

Esto devuelve (Chemistry). En una inscripción coincidente real, student_id forma parte de la clave primaria y no puede ser NULL, por lo que la prueba identifica el vínculo ausente.

Almacena una relación por fila de la tabla intermedia. Una lista de texto como «10,20» en un campo de estudiante no puede compararse por igualdad con los identificadores individuales de curso 10 y 20. Los pares separados permiten que cada condición de combinación compare una referencia con una clave.

Una combinación más larga sigue la misma regla que una de dos tablas: incorpora la siguiente tabla mediante una condición con sentido. Antes de escribir la consulta, traza las claves y decide qué debe representar una fila de resultado.

Añade los datos detallados de la tienda

Las líneas de pedido (order_id, product_id, quantity) son (101, 1, 2), (101, 2, 4), (102, 3, 6), (103, 1, 6), (103, 2, 8) y (104, 2, 10). Cada línea vincula un pedido con un producto y registra una cantidad.

Para la pregunta «¿Qué compró cada cliente?», sigue customers → orders → order_items → products. Las claves de la ruta son el identificador del cliente, el del pedido y el del producto. Cuatro tablas en esta cadena requieren tres vínculos.

Incorpora una tabla nueva cada vez

SELECT c.name, o.order_id, p.name AS product,
       oi.quantity, oi.quantity * p.price AS line_total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_id;

Cada ON hace referencia a la tabla recién introducida y a otra que ya está disponible. Los productos se incorporan a través de las líneas de pedido, no directamente a través de los clientes. El resultado es:

La primera combinación excluye el pedido de invitado 104 porque su referencia de cliente es NULL. Los tres pedidos restantes contienen cinco líneas, por lo que el resultado final tiene cinco filas. Para el pedido 101, dos tazas cuestan 16,00 y cuatro bolígrafos cuestan 6,00, lo que coincide con su total de pedido de 22,00.

Comprende qué cuenta una fila

Una fila de resultado representa una línea de pedido. Ana y el pedido 101 aparecen dos veces porque ese pedido tiene dos productos. Por tanto, es normal que se repitan los nombres de los clientes. Contar estas filas cuenta líneas, no clientes ni pedidos distintos.

Un filtro puede hacer referencia a cualquier tabla combinada. Añadir WHERE p.name = 'Pen' antes de ORDER BY conserva (Ana; 101; Pen; 4; 6,00) y (Ben; 103; Pen; 8; 12,00). Ningún filtro de producto puede recuperar el pedido de invitado ya excluido en el vínculo con el cliente.

Mantén visible cada vínculo

En una cadena de combinaciones internas, puede cambiar el orden escrito de las tablas sin cambiar las combinaciones coincidentes, siempre que cada condición haga referencia únicamente a tablas ya introducidas. Empezar por el tema del informe y seguir sus claves suele ser lo más fácil de leer.

Una condición ausente puede multiplicar un resultado intermedio en SQLite o MySQL, que aceptan un JOIN simple sin ON. PostgreSQL y SQL Server rechazan esa sintaxis. Una condición ausente en una consulta mediante comas también puede generar todas las combinaciones en ese paso.

A veces, una tabla desempeña dos papeles. Un informe de vuelos puede combinar airports como dep mediante dep.code = f.from_code y de nuevo como arr mediante arr.code = f.to_code. Los alias distintos dan a los datos de salida y llegada lugares separados en el resultado.

Una combinación izquierda conserva filas en un paso. Las combinaciones y los filtros posteriores siguen actuando sobre el resultado de ese paso. Para conservar una fila padre sin coincidencia a lo largo de una ruta más larga, sigue lo que sucede con sus columnas completadas con NULL en cada etapa.

Observa dónde desaparece una fila conservada

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id;

El primer paso genera cinco filas: los dos pedidos de Ana, el pedido de Ben y una fila completada con NULL para Chloe y otra para Dev. En el siguiente paso, los valores de o.order_id de Chloe y Dev son NULL. No pueden satisfacer la igualdad con una clave de línea de pedido, por lo que la combinación interna los elimina. La consulta devuelve cinco filas de líneas de pedido y pierde a ambos clientes sin pedidos.

Conserva la ruta opcional

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.order_id;

Ahora la segunda combinación conserva también esas filas completadas con NULL. El resultado contiene (Ana, 101, 1), (Ana, 101, 2), (Ana, 102, 3), (Ben, 103, 1), (Ben, 103, 2), (Chloe, NULL, NULL) y (Dev, NULL, NULL): siete filas.

Cuando las combinaciones posteriores dependen de las columnas de una tabla opcional, normalmente deben seguir siendo izquierdas para conservar las parejas ausentes. Una combinación interna posterior sobre las columnas de una tabla obligatoria tiene otra función, así que examina la dependencia real de cada condición.

Sigue la misma regla a través de una tabla intermedia

SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id
LEFT JOIN courses c ON c.course_id = e.course_id;

Esto devuelve (Mia, Algebra), (Mia, Biology), (Noah, Algebra) y (Omar, NULL). Convertir la última combinación en interna elimina a Omar porque su inscripción ausente no puede coincidir con ningún curso.

Los paréntesis ofrecen otra forma de expresar la agrupación: students s LEFT JOIN (enrollments e JOIN courses c ON c.course_id = e.course_id) ON e.student_id = s.student_id. Las coincidencias inscripción–curso forman el grupo opcional; la combinación externa incorpora ese grupo a cada estudiante y conserva a Omar.

Comprueba los filtros posteriores y la dirección de la combinación

Añadir WHERE c.title = 'Algebra' a la consulta de estudiantes que utiliza solo combinaciones izquierdas deja únicamente a Mia y Noah en Algebra. La comparación es desconocida para el título de curso NULL de Omar. Esto responde a «¿Quién cursa Algebra?», pero ya no responde a «Muestra todos los estudiantes y sus cursos». Una prueba IS NULL se comporta de otra forma porque acepta deliberadamente un valor ausente.

Una combinación derecha posterior vuelve a cambiar el lado conservado: conserva la tabla derecha recién añadida y pueden desaparecer las filas anteriores sin coincidencia. Coloca primero la tabla que debe conservarse e incorpora las rutas opcionales con combinaciones izquierdas cuando eso exprese el informe con claridad.

Construye y examina la consulta añadiendo una combinación cada vez. Anota tanto el número de filas como las claves que permanecen. Una disminución del recuento puede revelar la pérdida de filas sin coincidencia; un aumento inesperado puede revelar que falta una parte de la clave. Algunos recuentos no cambian cuando se pierden y multiplican filas a la vez, así que examina también claves representativas.

Una combinación puede ser correcta mientras que una agregación sobre su resultado es incorrecta. La pregunta clave es qué representa una fila después de la combinación. Un valor almacenado una vez por pedido puede aparecer varias veces en una lista de líneas de pedido.

Sigue un total de pedido repetido

Ana tiene el pedido 101 por 22,00 y el pedido 102 por 25,50. Sumar los dos pedidos da 47,50. El pedido 101 tiene dos líneas, por lo que combinar con order_items repite 22,00 dos veces; el pedido 102 tiene una línea. Sumar los valores repetidos da 69,50.

SELECT c.customer_id, c.name, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

GROUP BY reúne las filas de cada cliente y SUM suma los valores de ese grupo. Esta consulta devuelve (1; Ana; 47,50) y (2; Ben; 60,00). Añadir la combinación con líneas de pedido antes de agrupar inflaría el total de Ana a 69,50 y el de Ben a 120,00. Los totales de ambos clientes pasarían a ser 189,50 en lugar de 107,50.

Para los cuatro pedidos, incluido el pedido de invitado 104, combinar los pedidos directamente con sus líneas hace que SUM(o.total) devuelva 204,50 en lugar de 122,50. Esta multiplicación de filas por coincidencias múltiples se debe al vínculo uno a muchos, no a un error de sintaxis.

Suma los datos en el nivel en el que se almacenan

Para los totales de pedido, suma los pedidos sin añadir la tabla de detalle. Para un total de importes de línea, calcula el valor de cada línea:

SELECT SUM(oi.quantity * p.price) AS sales
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id;

Los seis importes de línea son 16,00; 6,00; 25,50; 48,00; 12,00 y 15,00. Suman 122,50, el total de los cuatro pedidos. Cada producto de cantidad por precio pertenece a una línea, por lo que se suma una vez.

Cuenta las coincidencias reales

Después de combinar clientes con pedidos y líneas, Ana ocupa tres filas. COUNT(*) cuenta esas líneas; COUNT(DISTINCT o.order_id) cuenta sus dos pedidos distintos.

Después de una sola combinación izquierda de clientes con pedidos, COUNT(*) cuenta una fila completada con NULL para Chloe y otra para Dev. Cuenta en su lugar la clave del pedido opcional:

SELECT c.customer_id, c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

Esto devuelve (1, Ana, 2), (2, Ben, 1), (3, Chloe, 0) y (4, Dev, 0). COUNT(column) ignora los valores NULL; COUNT(*) cuenta filas.

Dos lados muchos se multiplican entre sí

Si un cliente tiene tres pedidos y dos incidencias, combinar ambas tablas hijas de forma independiente con ese cliente genera seis filas. Cada pedido se empareja con cada incidencia. Los recuentos simples de cualquiera de las claves hijas pasan a ser seis. Cuenta las claves hijas distintas para obtener tres y dos, o calcula los totales de las tablas hijas por separado.

DISTINCT c.customer_id, c.name resulta útil para una lista de clientes sin duplicados. Mantener la clave evita fusionar clientes distintos que comparten nombre. No corrige una condición de combinación ausente. SUM(DISTINCT o.total) tampoco es seguro: fusiona dos pedidos distintos que casualmente cuestan el mismo importe.

Una consulta puede ejecutarse y aun así describir relaciones incorrectas. Antes de utilizar el resultado de una combinación, predice qué debe conservarse, cuántas veces debe aparecer cada clave y qué debe significar una fila de resultado.

Predice el número de filas

Si la referencia de cliente de un pedido coincide con una clave única de cliente, cada pedido tiene como máximo un cliente como pareja. Por tanto, una combinación interna devuelve como máximo una fila por pedido. En los datos de la tienda, tres de los cuatro pedidos tienen cliente, por lo que la combinación interna cliente–pedido devuelve tres filas.

Desde el lado del cliente, una combinación izquierda devuelve los tres pares coincidentes más una fila para Chloe y otra para Dev: cinco filas. Una combinación cruzada de los cuatro clientes y los cuatro pedidos da 16. Estas predicciones proporcionan comprobaciones distintas para cada tipo de combinación; el número de tablas por sí solo no determina el recuento.

Investiga la forma de un resultado incorrecto

Comparar solo store_id cuando los registros se identifican mediante (store_id, month) mezcla meses distintos. Compara todos los componentes con AND. Una consulta que funciona por casualidad mientras una tienda tiene un mes puede fallar en cuanto se carga un segundo mes.

Distingue los valores ausentes de las claves incorrectas

Con la igualdad habitual, las claves NULL no coinciden, ni siquiera NULL con NULL. Combinar pedidos consigo mismos por customer_id da cuatro pares de los dos pedidos de Ana y uno del pedido de Ben, para un total de cinco filas. El pedido de invitado no aporta ninguna. Se necesita una combinación externa cuando debe conservarse una fila con una referencia ausente.

Los valores de búsqueda duplicados multiplican solo las coincidencias de esos valores. Si un código de país aparece dos veces en una tabla de búsqueda, cada cliente con ese código obtiene dos filas como pareja. Los códigos que aparecen una vez siguen dando una pareja. Examina una tabla de búsqueda que debería ser única con:

SELECT code, COUNT(*) AS copies
FROM countries
GROUP BY code
HAVING COUNT(*) > 1;

GROUP BY reúne las filas de cada código; HAVING conserva los grupos cuyo recuento supera uno. Cargar dos veces toda la tabla de búsqueda duplicaría las filas de cada cliente coincidente.

Examina también los tipos y formatos de las claves. El texto «0042» y el número 42 pueden producir comportamientos distintos según la base de datos: ninguna coincidencia, un error de tipo o una conversión automática. SQLite puede relacionar una columna TEXT que contiene «0042» con una columna INTEGER que contiene 42. Utiliza tipos y formatos de clave coherentes, o una conversión explícita elegida según el significado deseado.

Repasa los requisitos del informe

Pregúntate qué tabla debe conservar todas sus filas. Traza su ruta completa de claves y comprueba cada condición con el esquema. Decide por qué cada vínculo es interno o externo y después comprueba si cada filtro pertenece a la búsqueda de coincidencias o debe aplicarse después. Compara los números de filas tras cada paso y examina una fila coincidente conocida, una sin coincidencia y otra con varias parejas. Por último, comprueba que cada recuento y suma mide el tipo de registro deseado.