Al final del capítulo 6 quedó una pregunta colgando: ¿cuál es el ticket promedio por ciudad?
Y no se podía contestar. El ticket está en pedidos, la ciudad
está en clientes, y son dos tablas distintas. Todo lo que hemos
hecho hasta ahora vive dentro de una sola.
Los JOIN son eso: la instrucción que junta dos tablas por la
columna que tienen en común. Es lo que hace que una base de datos sea una base
de datos y no cinco Excel en la misma carpeta 🗂️
En la documentación y en las ofertas de trabajo los vas a ver casi siempre en plural y a la inglesa, JOINs. Es la misma palabra, y cuando alguien te pida "que domines los JOINs" está pidiendo exactamente este capítulo 💛
SELECT c.ciudad, COUNT(*) AS pedidos, ROUND(AVG(p.monto), 2) AS ticket FROM pedidos p JOIN clientes c ON c.id = p.id_cliente GROUP BY c.ciudad ORDER BY ticket DESC;
ciudad pedidos ticket -------- ------- ------ Piura 167 657.46 Arequipa 168 637.94 Lima 104 631.17 Cusco 112 590.6 Trujillo 113 572.77 Chiclayo 212 568.53
Ahí está la respuesta, y encima es interesante: Piura tiene el ticket más alto y Chiclayo el más bajo, aunque Chiclayo es la que más pedidos hace. Vender mucho y vender caro no son lo mismo 💡
Cómo se lee un JOIN
SELECT p.id, p.fecha, p.monto, c.nombre, c.ciudad FROM pedidos p JOIN clientes c ON c.id = p.id_cliente ORDER BY p.id LIMIT 5;
id fecha monto nombre ciudad -- ---------- ------ -------------------------- -------- 1 2025-05-30 892.06 MARKET CENTRAL 069 Trujillo 2 2025-06-03 731.09 Distribuidora Paz 095 Arequipa 3 2025-09-23 407.39 Restaurante Miraflores 063 Chiclayo 4 2025-02-18 358.23 Distribuidora Paz 114 Chiclayo 5 2025-07-09 844.94 Almacenes Vega 067 Piura
Se lee así: "trae los pedidos, y al lado de cada uno pégame la fila de
clientes cuyo id sea igual al id_cliente del
pedido".
Tres cosas de la sintaxis, y son iguales en los cuatro motores:
- 🏷️
pedidos ple pone alias a la tabla. Sin alias, esa consulta se vuelve ilegible en cuanto haya tres tablas. - 🔗
ONdice por dónde se pegan. Casi siempre es una clave contra otra:c.id = p.id_cliente. - 📛
p.montoyc.nombredicen de qué tabla sale cada columna. Y eso no es decoración.
Prueba a quitar el prefijo cuando las dos tablas tienen una columna que se llama igual.
SELECT id, fecha FROM pedidos JOIN clientes ON clientes.id = pedidos.id_cliente LIMIT 3;
OperationalError: ambiguous column name: id
ambiguous column name: id. Las dos tablas tienen id y
la base no adivina cuál quieres. Este error lo vas a ver mil veces y siempre se
arregla igual: ponle el prefijo de la tabla a todas las columnas, siempre, desde
el primer día 💛
Los cuatro JOIN, con números de verdad
Aquí es donde casi todos los tutoriales te ponen dos círculos que se cruzan y tú asientes sin entender nada. Vamos a hacerlo con la base.
Dos datos que ya sabemos de los capítulos anteriores: hay 24 pedidos
sin cliente (el id_cliente viene nulo) y hay
1 cliente que nunca compró. Con esos dos huecos, los cuatro
JOIN se explican solos.
SELECT COUNT(*) AS filas_del_join FROM pedidos p JOIN clientes c ON c.id = p.id_cliente;
filas_del_join -------------- 876
INNER JOIN (que es lo que hace
JOIN a secas): solo las filas que hacen pareja. 876, o sea los 900
pedidos menos los 24 huérfanos.
SELECT COUNT(*) AS filas_del_left FROM pedidos p LEFT JOIN clientes c ON c.id = p.id_cliente;
filas_del_left -------------- 900
LEFT JOIN: todas las de la izquierda, hagan
pareja o no. Los 900 pedidos, y a los 24 huérfanos les rellena las columnas de
cliente con NULL.
SELECT COUNT(*) AS con_right FROM pedidos p RIGHT JOIN clientes c ON c.id = p.id_cliente;
con_right --------- 877
RIGHT JOIN: todas las de la derecha. 877, o sea
las 876 con pareja más el cliente que nunca compró.
SELECT COUNT(*) AS con_full FROM pedidos p FULL OUTER JOIN clientes c ON c.id = p.id_cliente;
con_full -------- 901
FULL OUTER JOIN: todo. 901 = 876 con pareja +
24 pedidos sin cliente + 1 cliente sin pedidos.
Esos cuatro números son el diagrama de círculos, pero contados. Y fíjate que
RIGHT es exactamente LEFT con las tablas al revés: por
eso casi nadie usa RIGHT y yo tampoco, es más fácil ordenar la
consulta para que la tabla que te importa quede a la izquierda.
Los JOIN que no están en todos
| PostgreSQL | INNER, LEFT, RIGHT, FULL OUTER y CROSS: los tiene todos |
| MySQL | no tiene FULL OUTER JOIN, hay que armarlo con LEFT UNION RIGHT |
| SQL Server | los tiene todos |
| SQLite | los tiene todos, pero RIGHT y FULL solo desde la versión 3.39 |
MySQL es el que se queda fuera, y no es un detalle: el FULL OUTER JOIN es justo el que usas para cuadrar dos tablas y ver qué falta de cada lado. Y ojo con SQLite viejito, que hasta 2022 tampoco los tenía.
Para qué sirve de verdad el LEFT JOIN
Para encontrar lo que falta. Esa es su gracia y casi nadie la usa así.
SELECT c.id, c.nombre, c.ciudad FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id WHERE p.id IS NULL;
id nombre ciudad --- ------------------- ------ 120 Comercial Rojas 120 Cusco
Ahí está: Comercial Rojas 120, de Cusco, dado de alta y sin comprar nunca. Un
LEFT JOIN más un WHERE ... IS NULL es la forma
estándar de preguntar "¿qué hay en esta tabla que no esté en la otra?", y
funciona igual en los cuatro motores.
Y del otro lado, los pedidos que no tienen a quién cobrarle.
SELECT COUNT(*) AS pedidos_huerfanos FROM pedidos p LEFT JOIN clientes c ON c.id = p.id_cliente WHERE c.id IS NULL;
pedidos_huerfanos ----------------- 24
Esas dos consultas son lo primero que corro cuando me pasan una base que no conozco. En diez segundos sabes si los datos cuadran o si vas a estar explicando diferencias toda la semana 🕵️♀️
El COUNT que se te cuela
Con LEFT JOIN hay que tener cuidado con qué cuentas.
SELECT c.ciudad, COUNT(p.id) AS pedidos, COUNT(*) AS filas FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id GROUP BY c.ciudad ORDER BY pedidos DESC;
ciudad pedidos filas -------- ------- ----- Chiclayo 212 212 Arequipa 168 168 Piura 167 167 Trujillo 113 113 Cusco 112 113 Lima 104 104
Mira Cusco: 112 pedidos y 113 filas. Esa fila de más es Comercial Rojas 120,
que aparece en el resultado con todas las columnas de pedido en
NULL.
COUNT(*) la cuenta porque es una fila. COUNT(p.id)
no la cuenta porque ese id es nulo. Con
LEFT JOIN, cuenta siempre una columna de la tabla de la derecha, no
*. Si no, los clientes sin pedidos te salen con un pedido
cada uno y el reporte queda inflado 😳
La trampa del WHERE que mata al LEFT JOIN
Esta es la que más veces he visto en código de gente que ya sabe SQL, y no da error.
La pregunta: todos mis clientes, con cuántos pedidos grandes hizo cada uno, incluidos los que no hicieron ninguno.
SELECT c.id, c.nombre, COUNT(p.id) AS pedidos_grandes FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id WHERE p.monto > 800 GROUP BY c.id, c.nombre ORDER BY pedidos_grandes, c.id LIMIT 4;
id nombre pedidos_grandes -- --------------------- --------------- 3 Minimarket El Sol 003 1 4 Mayorista Peru 004 1 5 Bodega La Esquina 005 1 6 Cafe del Puerto 006 1
Ordené de menor a mayor y el mínimo es 1. ¿Dónde están los que hicieron cero?
No están. Ese WHERE p.monto > 800 se aplica
después del JOIN, y a los clientes sin pedidos grandes el
LEFT JOIN les puso monto en NULL. Y
NULL > 800 no es verdadero, así que el WHERE los
tira. Tu LEFT JOIN se convirtió en un INNER JOIN sin
que nadie te avise.
La condición tiene que ir en el ON, no en el
WHERE.
SELECT c.id, c.nombre, COUNT(p.id) AS pedidos_grandes FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id AND p.monto > 800 GROUP BY c.id, c.nombre ORDER BY pedidos_grandes, c.id LIMIT 4;
id nombre pedidos_grandes -- --------------------- --------------- 17 Distribuidora Paz 017 0 19 Mayorista Peru 019 0 24 Minimarket Aurora 024 0 35 Cafe del Puerto 035 0
Ahora sí aparecen los ceros. La regla, y es de las que hay que memorizar:
- 🔗 Lo que filtra la tabla de la derecha va en el
ON. - 🚦 Lo que filtra la tabla de la izquierda va en el
WHERE.
Y hay un caso donde el WHERE sobre la derecha sí es correcto:
cuando es IS NULL, o sea cuando lo que buscas es justamente lo que
no hizo pareja. Eso es lo que hicimos dos secciones más arriba.
SELECT COUNT(*) AS clientes_sin_pedidos_grandes FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id AND p.monto > 800 WHERE p.id IS NULL;
clientes_sin_pedidos_grandes ---------------------------- 20
Veinte clientes de 120 nunca han hecho un pedido de más de S/800. Esa es una lista para el equipo comercial, no un número para un dashboard 📞
La suma que se multiplica
Y ahora el error más caro de todo el capítulo, porque el número que sale es grande y creíble.
SELECT COUNT(*) AS filas, ROUND(SUM(p.monto), 2) AS suma_inflada FROM pedidos p JOIN detalle d ON d.id_pedido = p.id;
filas suma_inflada ----- ------------ 2682 1591227.04
SELECT ROUND(SUM(monto), 2) AS suma_correcta FROM pedidos;
suma_correcta ------------- 532653.85
Un millón y medio contra medio millón. La venta de la tienda se triplicó sola 🫠
El motivo es puramente aritmético: cada pedido tiene unas 3 líneas de detalle, así que el JOIN repite la fila del pedido una vez por línea, y con ella repite el monto. 2.682 filas donde había 900.
Esto es lo que se llama fan-out, y es el error que más veces he encontrado en reportes de empresa. Nadie lo nota porque no hay error y porque el número inflado igual "parece" plausible.
Dos formas de no caer:
- 🧮 Suma en el nivel donde el dato vive una sola vez. El monto vive en
pedidos; las cantidades viven endetalle. - 🔍 Cuenta filas antes y después de cada JOIN. Si el número crece y tú no esperabas que creciera, ahí está el problema.
Cuando lo que quieres es el detalle, súmalo del detalle y no del pedido.
SELECT c.ciudad, ROUND(SUM(d.cantidad * d.precio_unit), 2) AS soles FROM clientes c JOIN pedidos p ON p.id_cliente = c.id JOIN detalle d ON d.id_pedido = p.id GROUP BY c.ciudad ORDER BY soles DESC;
ciudad soles -------- --------- Chiclayo 390277.6 Arequipa 280506.11 Piura 277342.0 Cusco 199618.96 Trujillo 181652.49 Lima 177604.92
Ahí sí está bien, porque cantidad * precio_unit es un dato de la
línea y cada línea aparece una sola vez.
Y un aviso honesto sobre esta base: el monto del pedido y la
suma de sus líneas no cuadran entre sí, porque tienda.db está hecha
para practicar y las dos cosas se generaron por separado. En una base de verdad
sí deberían cuadrar, y comprobar que cuadran es exactamente el tipo de consulta
que vas a escribir tu primera semana en un trabajo 🐣
Tres tablas, o cuatro
Los JOIN se encadenan. No hay límite y no hay sintaxis nueva: se pega uno detrás de otro.
SELECT p.id, p.fecha, c.nombre, pr.nombre AS producto, d.cantidad, d.precio_unit FROM pedidos p JOIN clientes c ON c.id = p.id_cliente JOIN detalle d ON d.id_pedido = p.id JOIN productos pr ON pr.id = d.id_producto ORDER BY p.id, pr.id LIMIT 5;
id fecha nombre producto cantidad precio_unit -- ---------- ---------------------- ----------- -------- ----------- 1 2025-05-30 MARKET CENTRAL 069 Producto 03 22 59.12 1 2025-05-30 MARKET CENTRAL 069 Producto 09 12 46.33 1 2025-05-30 MARKET CENTRAL 069 Producto 32 9 74.88 1 2025-05-30 MARKET CENTRAL 069 Producto 33 6 47.64 1 2025-05-30 MARKET CENTRAL 069 Producto 34 12 62.78
Cuatro tablas en una consulta, y ahí está el pedido 1 completo: quién lo hizo, qué compró y a qué precio. Eso es lo que en el Excel del cliente son cuatro pestañas y un BUSCARV que se rompe.
Fíjate en el papel de detalle, que es distinto al de las otras.
Un pedido tiene muchos productos y un producto está en muchos pedidos, y eso no
se puede guardar con una sola clave foránea: hace falta una tabla en medio con
las dos. Eso se llama tabla puente, y la vas a reconocer
enseguida porque casi no tiene datos propios, solo dos identificadores y un par
de columnas más 🌉
Fíjate que el nombre del cliente sale con espacios de más: es uno de los dieciocho sucios del capítulo 5, y el JOIN no lo limpia. Los problemas de datos no se arreglan solos al juntarlos, se propagan 🧽
Cuando la columna se llama igual en las dos tablas
| PostgreSQL | JOIN clientes USING (id_cliente) |
| MySQL | JOIN clientes USING (id_cliente) |
| SQL Server | no existe USING: siempre ON a.id_cliente = b.id_cliente |
| SQLite | JOIN clientes USING (id_cliente) |
USING es más corto y además deja una sola columna en el resultado en vez de dos repetidas. Pero pide que las columnas se llamen exactamente igual en las dos tablas, y en tienda.db no pasa: es id contra id_cliente. Con ON funcionas siempre y en los cuatro.
El CROSS JOIN, que casi nunca quieres
SELECT COUNT(*) AS cruz FROM clientes CROSS JOIN productos;
cruz ---- 4800
120 clientes por 40 productos igual a 4.800 filas: cada cliente contra cada producto, sin condición ninguna. Se usa para armar rejillas completas, tipo "todos los meses contra todas las sucursales aunque no hubiera ventas".
Lo importante es reconocerlo cuando aparece sin querer. Si escribes
un JOIN y te olvidas del ON, o pones las tablas separadas por comas
sin condición, sale esto: un número enorme y una consulta que tarda una
eternidad.
-- La forma antigua, con comas. Corre en los cuatro y no la escribas. SELECT COUNT(*) FROM pedidos p, clientes c WHERE c.id = p.id_cliente; -- La misma con JOIN, que es la que se lee y la que no se puede olvidar a medias. SELECT COUNT(*) FROM pedidos p JOIN clientes c ON c.id = p.id_cliente;
Las dos dan 876. La diferencia es que en la primera, si te olvidas el
WHERE, tienes un producto cartesiano silencioso; en la segunda, si
te olvidas el ON, se te nota.
Una tabla consigo misma
SELECT a.id, a.direccion, b.id, b.direccion FROM direcciones a JOIN direcciones b ON b.id_cliente = a.id_cliente AND b.id > a.id ORDER BY a.id LIMIT 3;
id direccion id direccion -- --------------- -- --------------- 2 Av. 283 nro 161 3 Av. 672 nro 178 2 Av. 283 nro 161 4 Av. 757 nro 160 3 Av. 672 nro 178 4 Av. 757 nro 160
La misma tabla dos veces, con dos alias distintos. Sirve para comparar filas entre sí: aquí saca las parejas de direcciones que pertenecen al mismo cliente, que es como se buscan duplicados.
El b.id > a.id del ON es el truco: sin él, cada
pareja saldría dos veces y además cada fila se emparejaría consigo misma.
Ejercicios
Siete sobre tienda.db. Intenta antes de abrir 💛
1. Los cinco clientes que más compran
Nombre, ciudad, soles y número de pedidos, de mayor a menor.
SELECT c.nombre, c.ciudad, ROUND(SUM(p.monto), 2) AS soles, COUNT(p.id) AS pedidos FROM clientes c JOIN pedidos p ON p.id_cliente = c.id GROUP BY c.id, c.nombre, c.ciudad ORDER BY soles DESC LIMIT 5;
nombre ciudad soles pedidos -------------------------- -------- -------- ------- Distribuidora Paz 058 Lima 10131.72 17 Almacenes Vega 067 Piura 9516.06 12 Market Central 087 Chiclayo 8148.56 12 Restaurante Miraflores 093 Trujillo 8119.95 12 MARKET CENTRAL 078 Chiclayo 7977.9 14
Ojo con el GROUP BY c.id, c.nombre, c.ciudad: hay que poner las
tres aunque el id ya sea único, porque la regla del capítulo 6 pide
que todo lo del SELECT esté en el GROUP BY. Y va el id primero,
porque dos clientes podrían llamarse igual.
2. Ventas por categoría de producto
Unidades y soles por categoría, juntando
detalle con productos.
SELECT pr.categoria, SUM(d.cantidad) AS unidades, ROUND(SUM(d.cantidad * d.precio_unit), 2) AS soles FROM detalle d JOIN productos pr ON pr.id = d.id_producto GROUP BY pr.categoria ORDER BY soles DESC;
categoria unidades soles ---------------- -------- --------- Limpieza 10076 361951.33 Snacks 6548 338311.89 Bebidas 5488 316973.5 Abarrotes 7454 310302.68 Cuidado personal 3601 218079.75
Limpieza vende más unidades y más soles, y en el capítulo 6 vimos que es la categoría más barata del catálogo. Vende por volumen. Cuidado personal es lo contrario: la más cara y la que menos mueve.
3. Ticket por segmento
El ticket promedio y el número de pedidos de cada segmento de cliente.
SELECT c.segmento, ROUND(AVG(p.monto), 2) AS ticket, COUNT(p.id) AS pedidos FROM clientes c JOIN pedidos p ON p.id_cliente = c.id GROUP BY c.segmento ORDER BY ticket DESC;
segmento ticket pedidos ---------- ------ ------- Minimarket 630.43 174 Horeca 610.05 257 Mayorista 607.59 194 Bodega 596.05 251
Entre 596 y 630 los cuatro. Otra vez el mismo hallazgo del capítulo 6: el tipo de cliente casi no cambia cuánto gasta por pedido. Lo que cambia es cuántos pedidos hace.
4. Ciudades completas, incluso las flojas
Por ciudad: cuántos clientes, cuántos pedidos y cuántos soles, sin perder a nadie por el camino.
SELECT c.ciudad, COUNT(DISTINCT c.id) AS clientes, COUNT(p.id) AS pedidos,
ROUND(SUM(p.monto), 2) AS soles
FROM clientes c
LEFT JOIN pedidos p ON p.id_cliente = c.id
GROUP BY c.ciudad
ORDER BY soles DESC;
ciudad clientes pedidos soles -------- -------- ------- --------- Chiclayo 25 212 115979.43 Piura 24 167 107165.17 Arequipa 22 168 101432.51 Cusco 17 112 64966.23 Trujillo 17 113 64150.66 Lima 15 104 63748.53
Tres detalles que hacen que esto esté bien: LEFT JOIN para no
perder a los clientes sin pedidos, COUNT(DISTINCT c.id) porque el
JOIN repite al cliente una vez por pedido, y COUNT(p.id) en vez de
COUNT(*).
Si hubiera puesto COUNT(c.id) sin DISTINCT, Chiclayo
tendría 212 clientes en vez de 25 🫠
5. El producto que más se vende
Los tres productos con más unidades, con su nombre y su categoría.
SELECT pr.nombre, pr.categoria, SUM(d.cantidad) AS unidades FROM productos pr JOIN detalle d ON d.id_producto = pr.id GROUP BY pr.id, pr.nombre, pr.categoria ORDER BY unidades DESC LIMIT 3;
nombre categoria unidades ----------- ---------------- -------- Producto 22 Cuidado personal 1075 Producto 23 Bebidas 1016 Producto 38 Abarrotes 1013
Y compáralo con el ranking por soles del capítulo 6: los tres de aquí no son los tres de allá. Unidades y soles son dos preguntas distintas, y la que te pidan casi nunca es la que estás contestando.
6. ¿Hay pedidos sin líneas?
Comprueba si algún pedido se quedó sin detalle.
SELECT COUNT(*) AS pedidos_sin_detalle FROM pedidos p LEFT JOIN detalle d ON d.id_pedido = p.id WHERE d.id IS NULL;
pedidos_sin_detalle ------------------- 0
Cero, o sea que la base está sana por ese lado. Estas consultas se corren aunque den cero: un cero comprobado vale muchísimo más que un "debería estar bien" 🌟
7. Escríbelo para los cuatro
Sin ejecutar: todos los clientes y todos los pedidos, cuadren o no, en una sola tabla.
-- PostgreSQL, SQL Server y SQLite (3.39 o más nuevo) SELECT c.nombre, p.id, p.monto FROM clientes c FULL OUTER JOIN pedidos p ON p.id_cliente = c.id; -- MySQL, que no tiene FULL OUTER JOIN SELECT c.nombre, p.id, p.monto FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id UNION SELECT c.nombre, p.id, p.monto FROM clientes c RIGHT JOIN pedidos p ON p.id_cliente = c.id;
Ese UNION sin ALL es a propósito: quita los
duplicados, que son justo las filas que ya salieron por los dos lados. Con
UNION ALL tendrías las 876 con pareja contadas dos veces.
El UNION lo vemos entero en el capítulo 8, junto con las
subconsultas.
Lo que te llevas
- 🔗
JOIN ... ONjunta dos tablas por una columna común. Ponle alias a las tablas y prefijo a todas las columnas desde el primer día. - ⭕
INNERson las que hacen pareja (876),LEFTsuma las sueltas de la izquierda (900),RIGHTlas de la derecha (877) yFULL OUTERlas dos (901). - 🇲 MySQL no tiene
FULL OUTER JOIN; se arma conLEFT UNION RIGHT. - 🔍
LEFT JOIN+WHERE ... IS NULLes la forma de encontrar lo que falta. Es la primera consulta que corro en una base nueva. - 💥 Un
WHEREsobre la tabla derecha convierte tuLEFT JOINenINNER. Esa condición va en elON. - 🧮 Con
LEFT JOINcuentaCOUNT(columna_derecha), nuncaCOUNT(*). - 🎈 El fan-out: unir pedidos con detalle triplicó la venta, de S/532.653 a S/1.591.227. Suma en el nivel donde el dato vive una sola vez.
En el capítulo 8 vienen las subconsultas y los CTE, que es lo que te deja
escribir una consulta larga en pedacitos que se entienden. Y ahí WITH
se escribe igual en los cuatro, para variar 🌸
Que tengas lindo día! 🌸