{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Juntar dos tablas\n",
    "\n",
    "INNER, LEFT, RIGHT y FULL OUTER contados con filas de verdad, y los dos errores que no dan error.\n",
    "\n",
    "Cuaderno de soluciones del capítulo 7 de **SQL desde cero**, de Miss Yera.\n",
    "\n",
    "Corre de arriba abajo. Si lo abres en Google Colab no necesitas instalar nada.\n",
    "\n",
    "Capítulo completo: https://missyera.com/guias/sql-desde-cero/joins/\n",
    "\n",
    "Este es el cuaderno de **soluciones**. Trae el código de cada ejercicio, la\n",
    "explicación de la trampa y la respuesta del quiz. Si vienes del cuaderno de\n",
    "práctica sin haberlo intentado, vuelve 🙂"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Antes de empezar\n",
    "\n",
    "Se baja la base y se deja lista una función `q()` que corre las consultas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import urllib.request\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "urllib.request.urlretrieve(\"https://missyera.com/static/datasets/tienda.db\", \"tienda.db\")\n",
    "con = sqlite3.connect(\"tienda.db\")\n",
    "\n",
    "def q(sql):\n",
    "    \"\"\"Corre las sentencias del bloque y devuelve la ultima como tabla.\n",
    "\n",
    "    Parte por sentencias igual que la consola de SQLite, porque un bloque puede\n",
    "    traer varias y un CREATE TRIGGER lleva punto y coma dentro de su cuerpo.\n",
    "    \"\"\"\n",
    "    resultado = None\n",
    "    trozo = \"\"\n",
    "    for linea in sql.splitlines(keepends=True):\n",
    "        trozo += linea\n",
    "        if sqlite3.complete_statement(trozo):\n",
    "            if trozo.strip():\n",
    "                cur = con.execute(trozo.strip())\n",
    "                resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                          columns=[d[0] for d in cur.description])\n",
    "                             if cur.description else None)\n",
    "            trozo = \"\"\n",
    "    if trozo.strip():\n",
    "        cur = con.execute(trozo.strip())\n",
    "        resultado = (pd.DataFrame(cur.fetchall(),\n",
    "                                  columns=[d[0] for d in cur.description])\n",
    "                     if cur.description else None)\n",
    "    con.commit()\n",
    "    return resultado\n",
    "\n",
    "q(\"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Al final del capítulo 6 quedó una pregunta colgando: ¿cuál es el ticket\n",
    "promedio por ciudad?\n",
    "\n",
    "Y no se podía contestar. El ticket está en `pedidos`, la ciudad\n",
    "está en `clientes`, y son dos tablas distintas. Todo lo que hemos\n",
    "hecho hasta ahora vive dentro de una sola.\n",
    "\n",
    "Los `JOIN` son eso: la instrucción que junta dos tablas por la\n",
    "columna que tienen en común. Es lo que hace que una base de datos sea una base\n",
    "de datos y no cinco Excel en la misma carpeta 🗂️\n",
    "\n",
    "En la documentación y en las ofertas de trabajo los vas a ver casi siempre en\n",
    "plural y a la inglesa, **JOINs**. Es la misma palabra, y cuando\n",
    "alguien te pida \"que domines los JOINs\" está pidiendo exactamente este\n",
    "capítulo 💛\n",
    "\n",
    "Y una pregunta que se contesta rápido: **¿has hecho alguna vez un BUSCARV en Excel?** Si la respuesta es sí, ya sabes lo que hace un JOIN. Lo único nuevo acá es que la base lo hace con millones de filas y sin arrastrar la fórmula 🔗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad, COUNT(*) AS pedidos, ROUND(AVG(p.monto), 2) AS ticket\n",
    "FROM pedidos p\n",
    "JOIN clientes c ON c.id = p.id_cliente\n",
    "GROUP BY c.ciudad\n",
    "ORDER BY ticket DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahí está la respuesta, y encima es interesante: Piura tiene el ticket más\n",
    "alto y Chiclayo el más bajo, aunque Chiclayo es la que más pedidos hace. Vender\n",
    "mucho y vender caro no son lo mismo 💡"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cómo se lee un JOIN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT p.id, p.fecha, p.monto, c.nombre, c.ciudad\n",
    "FROM pedidos p\n",
    "JOIN clientes c ON c.id = p.id_cliente\n",
    "ORDER BY p.id\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se lee así: \"trae los pedidos, y al lado de cada uno pégame la fila de\n",
    "clientes cuyo `id` sea igual al `id_cliente` del\n",
    "pedido\".\n",
    "\n",
    "Tres cosas de la sintaxis, y son iguales en los cuatro motores:\n",
    "\n",
    "- 🏷️ `pedidos p` le pone **alias** a la tabla. Sin\n",
    "alias, esa consulta se vuelve ilegible en cuanto haya tres tablas.\n",
    "\n",
    "- 🔗 `ON` dice **por dónde se pegan**. Casi siempre es\n",
    "una clave contra otra: `c.id = p.id_cliente`.\n",
    "\n",
    "- 📛 `p.monto` y `c.nombre` dicen **de qué tabla\n",
    "sale cada columna**. Y eso no es decoración.\n",
    "\n",
    "Prueba a quitar el prefijo cuando las dos tablas tienen una columna que se\n",
    "llama igual."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Esto revienta a propósito.** Se ejecuta dentro de un `try` para que puedas seguir con \"ejecutar todo\" y aun así ver la queja."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    q(\"\"\"\n",
    "    SELECT id, fecha\n",
    "    FROM pedidos\n",
    "    JOIN clientes ON clientes.id = pedidos.id_cliente\n",
    "    LIMIT 3;\n",
    "    \"\"\")\n",
    "except Exception as e:\n",
    "    print(f'{type(e).__name__}: {e}')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y la queja que tiene que salir es esta:\n",
    "\n",
    "```\n",
    "OperationalError: ambiguous column name: id\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*ambiguous column name: id*. Las dos tablas tienen `id` y\n",
    "la base no adivina cuál quieres. Este error lo vas a ver mil veces y siempre se\n",
    "arregla igual: ponle el prefijo de la tabla a todas las columnas, siempre, desde\n",
    "el primer día 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Los cuatro JOIN, con números de verdad"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aquí es donde casi todos los tutoriales te ponen dos círculos que se cruzan y\n",
    "tú asientes sin entender nada. Vamos a hacerlo con la base.\n",
    "\n",
    "Dos datos que ya sabemos de los capítulos anteriores: hay **24 pedidos\n",
    "sin cliente** (el `id_cliente` viene nulo) y hay\n",
    "**1 cliente que nunca compró**. Con esos dos huecos, los cuatro\n",
    "JOIN se explican solos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS filas_del_join\n",
    "FROM pedidos p\n",
    "JOIN clientes c ON c.id = p.id_cliente;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`INNER JOIN`** (que es lo que hace\n",
    "`JOIN` a secas): solo las filas que hacen pareja. 876, o sea los 900\n",
    "pedidos menos los 24 huérfanos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS filas_del_left\n",
    "FROM pedidos p\n",
    "LEFT JOIN clientes c ON c.id = p.id_cliente;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`LEFT JOIN`**: todas las de la izquierda, hagan\n",
    "pareja o no. Los 900 pedidos, y a los 24 huérfanos les rellena las columnas de\n",
    "cliente con `NULL`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS con_right\n",
    "FROM pedidos p\n",
    "RIGHT JOIN clientes c ON c.id = p.id_cliente;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`RIGHT JOIN`**: todas las de la derecha. 877, o sea\n",
    "las 876 con pareja más el cliente que nunca compró."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS con_full\n",
    "FROM pedidos p\n",
    "FULL OUTER JOIN clientes c ON c.id = p.id_cliente;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**`FULL OUTER JOIN`**: todo. 901 = 876 con pareja +\n",
    "24 pedidos sin cliente + 1 cliente sin pedidos.\n",
    "\n",
    "Esos cuatro números son el diagrama de círculos, pero contados. Y fíjate que\n",
    "`RIGHT` es exactamente `LEFT` con las tablas al revés: por\n",
    "eso casi nadie usa `RIGHT` y yo tampoco, es más fácil ordenar la\n",
    "consulta para que la tabla que te importa quede a la izquierda."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los JOIN que no están en todos\n",
    "\n",
    "| PostgreSQL | `INNER, LEFT, RIGHT, FULL OUTER y CROSS: los tiene todos` |\n",
    "|---|---|\n",
    "| MySQL | `no tiene FULL OUTER JOIN, hay que armarlo con LEFT UNION RIGHT` |\n",
    "| SQL Server | `los tiene todos` |\n",
    "| SQLite | `los tiene todos, pero RIGHT y FULL solo desde la versión 3.39` |\n",
    "\n",
    "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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Para qué sirve de verdad el LEFT JOIN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para encontrar lo que falta. Esa es su gracia y casi nadie la usa así."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.id, c.nombre, c.ciudad\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE p.id IS NULL;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahí está: Comercial Rojas 120, de Cusco, dado de alta y sin comprar nunca. Un\n",
    "`LEFT JOIN` más un `WHERE ... IS NULL` es la forma\n",
    "estándar de preguntar \"¿qué hay en esta tabla que no esté en la otra?\", y\n",
    "funciona igual en los cuatro motores.\n",
    "\n",
    "Y del otro lado, los pedidos que no tienen a quién cobrarle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS pedidos_huerfanos\n",
    "FROM pedidos p\n",
    "LEFT JOIN clientes c ON c.id = p.id_cliente\n",
    "WHERE c.id IS NULL;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esas dos consultas son lo primero que corro cuando me pasan una base que no\n",
    "conozco. En diez segundos sabes si los datos cuadran o si vas a estar explicando\n",
    "diferencias toda la semana 🕵️‍♀️"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El COUNT que se te cuela"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Con `LEFT JOIN` hay que tener cuidado con qué cuentas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad, COUNT(p.id) AS pedidos, COUNT(*) AS filas\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "GROUP BY c.ciudad\n",
    "ORDER BY pedidos DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mira Cusco: 112 pedidos y 113 filas. Esa fila de más es Comercial Rojas 120,\n",
    "que aparece en el resultado con todas las columnas de pedido en\n",
    "`NULL`.\n",
    "\n",
    "`COUNT(*)` la cuenta porque es una fila. `COUNT(p.id)`\n",
    "no la cuenta porque ese `id` es nulo. **Con\n",
    "`LEFT JOIN`, cuenta siempre una columna de la tabla de la derecha, no\n",
    "`*`.** Si no, los clientes sin pedidos te salen con un pedido\n",
    "cada uno y el reporte queda inflado 😳"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La trampa del WHERE que mata al LEFT JOIN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Esta es la que más veces he visto en código de gente que ya sabe SQL, y no da\n",
    "error.\n",
    "\n",
    "La pregunta: *todos mis clientes, con cuántos pedidos grandes hizo cada\n",
    "uno, incluidos los que no hicieron ninguno.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.id, c.nombre, COUNT(p.id) AS pedidos_grandes\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE p.monto > 800\n",
    "GROUP BY c.id, c.nombre\n",
    "ORDER BY pedidos_grandes, c.id\n",
    "LIMIT 4;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ordené de menor a mayor y el mínimo es 1. ¿Dónde están los que hicieron\n",
    "cero?\n",
    "\n",
    "No están. Ese `WHERE p.monto > 800` se aplica\n",
    "**después** del JOIN, y a los clientes sin pedidos grandes el\n",
    "`LEFT JOIN` les puso `monto` en `NULL`. Y\n",
    "`NULL > 800` no es verdadero, así que el `WHERE` los\n",
    "tira. Tu `LEFT JOIN` se convirtió en un `INNER JOIN` sin\n",
    "que nadie te avise.\n",
    "\n",
    "La condición tiene que ir en el `ON`, no en el\n",
    "`WHERE`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.id, c.nombre, COUNT(p.id) AS pedidos_grandes\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id AND p.monto > 800\n",
    "GROUP BY c.id, c.nombre\n",
    "ORDER BY pedidos_grandes, c.id\n",
    "LIMIT 4;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahora sí aparecen los ceros. La regla, y es de las que hay que memorizar:\n",
    "\n",
    "- 🔗 Lo que filtra **la tabla de la derecha** va en el\n",
    "`ON`.\n",
    "\n",
    "- 🚦 Lo que filtra **la tabla de la izquierda** va en el\n",
    "`WHERE`.\n",
    "\n",
    "Y hay un caso donde el `WHERE` sobre la derecha sí es correcto:\n",
    "cuando es `IS NULL`, o sea cuando lo que buscas es justamente lo que\n",
    "no hizo pareja. Eso es lo que hicimos dos secciones más arriba."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS clientes_sin_pedidos_grandes\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id AND p.monto > 800\n",
    "WHERE p.id IS NULL;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Veinte clientes de 120 nunca han hecho un pedido de más de S/800. Esa es una\n",
    "lista para el equipo comercial, no un número para un dashboard 📞"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## La suma que se multiplica"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y ahora el error más caro de todo el capítulo, porque el número que sale es\n",
    "grande y creíble."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS filas, ROUND(SUM(p.monto), 2) AS suma_inflada\n",
    "FROM pedidos p\n",
    "JOIN detalle d ON d.id_pedido = p.id;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT ROUND(SUM(monto), 2) AS suma_correcta FROM pedidos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un millón y medio contra medio millón. La venta de la tienda se triplicó sola\n",
    "🫠\n",
    "\n",
    "El motivo es puramente aritmético: cada pedido tiene unas 3 líneas de\n",
    "detalle, así que el JOIN repite la fila del pedido una vez por línea, y con ella\n",
    "repite el monto. 2.682 filas donde había 900.\n",
    "\n",
    "Esto es lo que se llama **fan-out**, y es el error que más veces\n",
    "he encontrado en reportes de empresa. Nadie lo nota porque no hay error y porque\n",
    "el número inflado igual \"parece\" plausible.\n",
    "\n",
    "Dos formas de no caer:\n",
    "\n",
    "- 🧮 Suma en el nivel donde el dato vive una sola vez. El monto vive en\n",
    "`pedidos`; las cantidades viven en `detalle`.\n",
    "\n",
    "- 🔍 Cuenta filas antes y después de cada JOIN. Si el número crece y tú no\n",
    "esperabas que creciera, ahí está el problema.\n",
    "\n",
    "Cuando lo que quieres es el detalle, súmalo del detalle y no del pedido."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad, ROUND(SUM(d.cantidad * d.precio_unit), 2) AS soles\n",
    "FROM clientes c\n",
    "JOIN pedidos p ON p.id_cliente = c.id\n",
    "JOIN detalle d ON d.id_pedido = p.id\n",
    "GROUP BY c.ciudad\n",
    "ORDER BY soles DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ahí sí está bien, porque `cantidad * precio_unit` es un dato de la\n",
    "línea y cada línea aparece una sola vez.\n",
    "\n",
    "Y un aviso honesto sobre esta base: el `monto` del pedido y la\n",
    "suma de sus líneas no cuadran entre sí, porque `tienda.db` está hecha\n",
    "para practicar y las dos cosas se generaron por separado. En una base de verdad\n",
    "sí deberían cuadrar, y comprobar que cuadran es exactamente el tipo de consulta\n",
    "que vas a escribir tu primera semana en un trabajo 🐣"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tres tablas, o cuatro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Los JOIN se encadenan. No hay límite y no hay sintaxis nueva: se pega uno\n",
    "detrás de otro."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT p.id, p.fecha, c.nombre, pr.nombre AS producto, d.cantidad, d.precio_unit\n",
    "FROM pedidos p\n",
    "JOIN clientes c ON c.id = p.id_cliente\n",
    "JOIN detalle d ON d.id_pedido = p.id\n",
    "JOIN productos pr ON pr.id = d.id_producto\n",
    "ORDER BY p.id, pr.id\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cuatro tablas en una consulta, y ahí está el pedido 1 completo: quién lo\n",
    "hizo, qué compró y a qué precio. Eso es lo que en el Excel del cliente son\n",
    "cuatro pestañas y un BUSCARV que se rompe.\n",
    "\n",
    "Fíjate en el papel de `detalle`, que es distinto al de las otras.\n",
    "Un pedido tiene muchos productos y un producto está en muchos pedidos, y eso no\n",
    "se puede guardar con una sola clave foránea: hace falta una tabla en medio con\n",
    "las dos. Eso se llama **tabla puente**, y la vas a reconocer\n",
    "enseguida porque casi no tiene datos propios, solo dos identificadores y un par\n",
    "de columnas más 🌉\n",
    "\n",
    "Fíjate que el nombre del cliente sale con espacios de más: es uno de los\n",
    "dieciocho sucios del capítulo 5, y el JOIN no lo limpia. Los problemas de datos\n",
    "no se arreglan solos al juntarlos, se propagan 🧽"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cuando la columna se llama igual en las dos tablas\n",
    "\n",
    "| PostgreSQL | `JOIN clientes USING (id_cliente)` |\n",
    "|---|---|\n",
    "| MySQL | `JOIN clientes USING (id_cliente)` |\n",
    "| SQL Server | `no existe USING: siempre ON a.id_cliente = b.id_cliente` |\n",
    "| SQLite | `JOIN clientes USING (id_cliente)` |\n",
    "\n",
    "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."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El CROSS JOIN, que casi nunca quieres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS cruz\n",
    "FROM clientes CROSS JOIN productos;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "120 clientes por 40 productos igual a 4.800 filas: cada cliente contra cada\n",
    "producto, sin condición ninguna. Se usa para armar rejillas completas, tipo\n",
    "\"todos los meses contra todas las sucursales aunque no hubiera ventas\".\n",
    "\n",
    "Lo importante es reconocerlo cuando aparece *sin querer*. Si escribes\n",
    "un JOIN y te olvidas del `ON`, o pones las tablas separadas por comas\n",
    "sin condición, sale esto: un número enorme y una consulta que tarda una\n",
    "eternidad."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "-- La forma antigua, con comas. Corre en los cuatro y no la escribas.\n",
    "SELECT COUNT(*) FROM pedidos p, clientes c WHERE c.id = p.id_cliente;\n",
    "\n",
    "-- La misma con JOIN, que es la que se lee y la que no se puede olvidar a medias.\n",
    "SELECT COUNT(*) FROM pedidos p JOIN clientes c ON c.id = p.id_cliente;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Las dos dan 876. La diferencia es que en la primera, si te olvidas el\n",
    "`WHERE`, tienes un producto cartesiano silencioso; en la segunda, si\n",
    "te olvidas el `ON`, se te nota."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Una tabla consigo misma"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT a.id, a.direccion, b.id, b.direccion\n",
    "FROM direcciones a\n",
    "JOIN direcciones b ON b.id_cliente = a.id_cliente AND b.id > a.id\n",
    "ORDER BY a.id\n",
    "LIMIT 3;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La misma tabla dos veces, con dos alias distintos. Sirve para comparar filas\n",
    "entre sí: aquí saca las parejas de direcciones que pertenecen al mismo cliente,\n",
    "que es como se buscan duplicados.\n",
    "\n",
    "El `b.id > a.id` del `ON` es el truco: sin él, cada\n",
    "pareja saldría dos veces y además cada fila se emparejaría consigo misma."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ejercicios"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Siete sobre `tienda.db`. Intenta antes de abrir 💛"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Los cinco clientes que más compran\n",
    "\n",
    "Nombre, ciudad, soles y número de pedidos, de mayor a\n",
    "menor."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.nombre, c.ciudad, ROUND(SUM(p.monto), 2) AS soles, COUNT(p.id) AS pedidos\n",
    "FROM clientes c\n",
    "JOIN pedidos p ON p.id_cliente = c.id\n",
    "GROUP BY c.id, c.nombre, c.ciudad\n",
    "ORDER BY soles DESC\n",
    "LIMIT 5;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "nombre                      ciudad    soles     pedidos\n",
    "--------------------------  --------  --------  -------\n",
    "Distribuidora Paz 058       Lima      10131.72  17\n",
    "Almacenes Vega 067          Piura     9516.06   12\n",
    "Market Central 087          Chiclayo  8148.56   12\n",
    "Restaurante Miraflores 093  Trujillo  8119.95   12\n",
    "  MARKET CENTRAL 078        Chiclayo  7977.9    14\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ojo con el `GROUP BY c.id, c.nombre, c.ciudad`: hay que poner las\n",
    "tres aunque el `id` ya sea único, porque la regla del capítulo 6 pide\n",
    "que todo lo del SELECT esté en el GROUP BY. Y va el `id` primero,\n",
    "porque dos clientes podrían llamarse igual."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Ventas por categoría de producto\n",
    "\n",
    "Unidades y soles por categoría, juntando\n",
    "`detalle` con `productos`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT pr.categoria, SUM(d.cantidad) AS unidades, ROUND(SUM(d.cantidad * d.precio_unit), 2) AS soles\n",
    "FROM detalle d\n",
    "JOIN productos pr ON pr.id = d.id_producto\n",
    "GROUP BY pr.categoria\n",
    "ORDER BY soles DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "categoria         unidades  soles\n",
    "----------------  --------  ---------\n",
    "Limpieza          10076     361951.33\n",
    "Snacks            6548      338311.89\n",
    "Bebidas           5488      316973.5\n",
    "Abarrotes         7454      310302.68\n",
    "Cuidado personal  3601      218079.75\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Limpieza vende más unidades y más soles, y en el capítulo 6 vimos que es la\n",
    "categoría más barata del catálogo. Vende por volumen. Cuidado personal es lo\n",
    "contrario: la más cara y la que menos mueve."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Ticket por segmento\n",
    "\n",
    "El ticket promedio y el número de pedidos de cada segmento\n",
    "de cliente."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.segmento, ROUND(AVG(p.monto), 2) AS ticket, COUNT(p.id) AS pedidos\n",
    "FROM clientes c\n",
    "JOIN pedidos p ON p.id_cliente = c.id\n",
    "GROUP BY c.segmento\n",
    "ORDER BY ticket DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "segmento    ticket  pedidos\n",
    "----------  ------  -------\n",
    "Minimarket  630.43  174\n",
    "Horeca      610.05  257\n",
    "Mayorista   607.59  194\n",
    "Bodega      596.05  251\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Entre 596 y 630 los cuatro. Otra vez el mismo hallazgo del capítulo 6: el\n",
    "tipo de cliente casi no cambia cuánto gasta por pedido. Lo que cambia es cuántos\n",
    "pedidos hace."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Ciudades completas, incluso las flojas\n",
    "\n",
    "Por ciudad: cuántos clientes, cuántos pedidos y cuántos\n",
    "soles, sin perder a nadie por el camino."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT c.ciudad, COUNT(DISTINCT c.id) AS clientes, COUNT(p.id) AS pedidos,\n",
    "       ROUND(SUM(p.monto), 2) AS soles\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "GROUP BY c.ciudad\n",
    "ORDER BY soles DESC;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "ciudad    clientes  pedidos  soles\n",
    "--------  --------  -------  ---------\n",
    "Chiclayo  25        212      115979.43\n",
    "Piura     24        167      107165.17\n",
    "Arequipa  22        168      101432.51\n",
    "Cusco     17        112      64966.23\n",
    "Trujillo  17        113      64150.66\n",
    "Lima      15        104      63748.53\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tres detalles que hacen que esto esté bien: `LEFT JOIN` para no\n",
    "perder a los clientes sin pedidos, `COUNT(DISTINCT c.id)` porque el\n",
    "JOIN repite al cliente una vez por pedido, y `COUNT(p.id)` en vez de\n",
    "`COUNT(*)`.\n",
    "\n",
    "Si hubiera puesto `COUNT(c.id)` sin `DISTINCT`, Chiclayo\n",
    "tendría 212 clientes en vez de 25 🫠"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. El producto que más se vende\n",
    "\n",
    "Los tres productos con más unidades, con su nombre y su\n",
    "categoría."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT pr.nombre, pr.categoria, SUM(d.cantidad) AS unidades\n",
    "FROM productos pr\n",
    "JOIN detalle d ON d.id_producto = pr.id\n",
    "GROUP BY pr.id, pr.nombre, pr.categoria\n",
    "ORDER BY unidades DESC\n",
    "LIMIT 3;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "nombre       categoria         unidades\n",
    "-----------  ----------------  --------\n",
    "Producto 22  Cuidado personal  1075\n",
    "Producto 23  Bebidas           1016\n",
    "Producto 38  Abarrotes         1013\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y compáralo con el ranking por soles del capítulo 6: los tres de aquí no son\n",
    "los tres de allá. Unidades y soles son dos preguntas distintas, y la que te\n",
    "pidan casi nunca es la que estás contestando."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. ¿Hay pedidos sin líneas?\n",
    "\n",
    "Comprueba si algún pedido se quedó sin detalle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "q(\"\"\"\n",
    "SELECT COUNT(*) AS pedidos_sin_detalle\n",
    "FROM pedidos p\n",
    "LEFT JOIN detalle d ON d.id_pedido = p.id\n",
    "WHERE d.id IS NULL;\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "pedidos_sin_detalle\n",
    "-------------------\n",
    "0\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Cero, o sea que la base está sana por ese lado. Estas consultas se corren\n",
    "aunque den cero: un cero comprobado vale muchísimo más que un \"debería estar\n",
    "bien\" 🌟"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Escríbelo para los cuatro\n",
    "\n",
    "Sin ejecutar: todos los clientes y todos los pedidos,\n",
    "cuadren o no, en una sola tabla."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```\n",
    "-- PostgreSQL, SQL Server y SQLite (3.39 o más nuevo)\n",
    "SELECT c.nombre, p.id, p.monto\n",
    "FROM clientes c\n",
    "FULL OUTER JOIN pedidos p ON p.id_cliente = c.id;\n",
    "\n",
    "-- MySQL, que no tiene FULL OUTER JOIN\n",
    "SELECT c.nombre, p.id, p.monto\n",
    "FROM clientes c LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "UNION\n",
    "SELECT c.nombre, p.id, p.monto\n",
    "FROM clientes c RIGHT JOIN pedidos p ON p.id_cliente = c.id;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ese `UNION` sin `ALL` es a propósito: quita los\n",
    "duplicados, que son justo las filas que ya salieron por los dos lados. Con\n",
    "`UNION ALL` tendrías las 876 con pareja contadas dos veces.\n",
    "\n",
    "El `UNION` lo vemos entero en el capítulo 8, junto con las\n",
    "subconsultas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## El LEFT JOIN que dejó de serlo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y ahora la trampa de los JOIN, que es la que más veces me han traído. Fíjate en dónde está puesta la condición, que ahí está todo 🫥"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### La trampa\n",
    "\n",
    "Quieres todos los clientes con lo que compraron por Web, incluidos los que no compraron nada. Usas LEFT JOIN, que es exactamente para eso, y filtras el canal.\n",
    "\n",
    "```\n",
    "SELECT COUNT(DISTINCT c.id)\n",
    "FROM clientes c\n",
    "LEFT JOIN pedidos p ON p.id_cliente = c.id\n",
    "WHERE p.canal = 'Web';\n",
    "-- 105\n",
    "```\n",
    "\n",
    "**Qué está mal**\n",
    "\n",
    "105 de 120. Se perdieron quince clientes justo los que el LEFT JOIN estaba ahí para conservar 🫥\n",
    "\n",
    "El LEFT JOIN hace su trabajo: trae los clientes sin pedidos, con las columnas de `pedidos` en NULL. Pero el `WHERE` corre **después**, y `NULL = 'Web'` no es verdadero, así que los borra a todos. Un WHERE sobre la tabla de la derecha convierte tu LEFT JOIN en un INNER JOIN y no te lo dice.\n",
    "\n",
    "La condición sobre la tabla de la derecha va en el `ON`, no en el `WHERE`:\n",
    "`LEFT JOIN pedidos p ON p.id_cliente = c.id AND p.canal = 'Web'`\n",
    "Así salen los 120.\n",
    "\n",
    "La regla corta: en el ON va lo que decide **qué se junta**, en el WHERE lo que decide **qué se queda**. Y con LEFT JOIN esa diferencia deja de ser sutil."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comprueba que lo tienes\n",
    "\n",
    "Haces un LEFT JOIN de clientes con ventas para encontrar quién no ha comprado nunca, y añades WHERE ventas.fecha > \"2026-01-01\". Salen cero clientes sin compras.\n",
    "\n",
    "a) El WHERE sobre la tabla de la derecha convierte el LEFT en INNER\n",
    "\n",
    "b) El LEFT JOIN está al revés, tenía que ser RIGHT\n",
    "\n",
    "c) Faltan clientes en la tabla, por eso no aparecen\n",
    "\n",
    "d) Hay que usar COUNT en vez de mirar las filas\n",
    "\n",
    "---\n",
    "\n",
    "**La correcta es la a.**\n",
    "\n",
    "*b)* Con las tablas en ese orden, el LEFT es el correcto. El problema llegó después del JOIN.\n",
    "\n",
    "*c)* Aparecían antes de añadir esa línea. Algo de lo que añadiste los quitó.\n",
    "\n",
    "*d)* Contar te daría el mismo cero. La consulta ya perdió esas filas.\n",
    "\n",
    "Un cliente sin compras trae la fecha en NULL, y NULL no es mayor que nada. El filtro va en el ON, no en el WHERE."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lo que te llevas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 🔗 `JOIN ... ON` junta dos tablas por una columna común. Ponle\n",
    "alias a las tablas y prefijo a todas las columnas desde el primer día.\n",
    "\n",
    "- ⭕ `INNER` son las que hacen pareja (876), `LEFT` suma\n",
    "las sueltas de la izquierda (900), `RIGHT` las de la derecha (877) y\n",
    "`FULL OUTER` las dos (901).\n",
    "\n",
    "- 🇲 MySQL no tiene `FULL OUTER JOIN`; se arma con\n",
    "`LEFT UNION RIGHT`.\n",
    "\n",
    "- 🔍 `LEFT JOIN` + `WHERE ... IS NULL` es la forma de\n",
    "encontrar lo que falta. Es la primera consulta que corro en una base nueva.\n",
    "\n",
    "- 💥 Un `WHERE` sobre la tabla derecha convierte tu\n",
    "`LEFT JOIN` en `INNER`. Esa condición va en el\n",
    "`ON`.\n",
    "\n",
    "- 🧮 Con `LEFT JOIN` cuenta `COUNT(columna_derecha)`,\n",
    "nunca `COUNT(*)`.\n",
    "\n",
    "- 🎈 El fan-out: unir pedidos con detalle triplicó la venta, de S/532.653 a\n",
    "S/1.591.227. Suma en el nivel donde el dato vive una sola vez.\n",
    "\n",
    "Y si de todo el capítulo te llevas una sola frase, que sea esta:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "En el `ON` va lo que decide qué se junta. En el `WHERE`,\n",
    "lo que decide qué se queda."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Y si el BUSCARV te sirvió para entender el JOIN, funciona también al\n",
    "revés: entender el JOIN te arregla la mitad de los BUSCARV que se rompen.\n",
    "Está en la [guía de Excel](https://missyera.com/guias/excel-desde-cero/) 🔗\n",
    "\n",
    "En el capítulo 8 vienen las subconsultas y los CTE, que es lo que te deja\n",
    "escribir una consulta larga en pedacitos que se entienden. Y ahí `WITH`\n",
    "se escribe igual en los cuatro, para variar 🌸\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preguntas frecuentes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "¿Qué es un JOIN en SQL?La instrucción para juntar dos tablas por una columna que comparten, como el id del cliente.\n",
    "\n",
    "¿Cuál es la diferencia entre INNER JOIN y LEFT JOIN?INNER se queda solo con las filas que encuentran pareja. LEFT se queda con todas las de la izquierda y rellena con nulos las que no la encuentran. Elegir mal es cómo desaparece plata de un reporte sin que nada falle.\n",
    "\n",
    "¿Por qué mi JOIN devuelve más filas de las que tenía?Porque una fila encontró varias parejas y se multiplicó. Es el error que más infla sumas, y se detecta contando filas antes y después.\n",
    "\n",
    "¿Cuántos tipos de JOIN hay?Los que se usan son cuatro: INNER, LEFT, RIGHT y FULL. Y CROSS JOIN, que cruza todo con todo y casi siempre aparece por accidente."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "Ese era el capítulo 7 de **SQL desde cero**. El texto completo, con las salidas de cada bloque, está en https://missyera.com/guias/sql-desde-cero/joins/\n",
    "\n",
    "Que tengas lindo día! 🌸"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}