{
 "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 práctica 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",
    "Los ejercicios están al final y traen una celda vacía debajo de cada uno. Las\n",
    "respuestas viven en el cuaderno de soluciones, y merece la pena pelearse un\n",
    "rato antes de abrirlo 💛"
   ]
  },
  {
   "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": [
    "## Antes de empezar\n",
    "\n",
    "Esta celda baja el ayudante que corrige tus ejercicios. Después, en cada\n",
    "ejercicio que se pueda corregir solo, vas a ver `%%revisa` arriba de la celda:\n",
    "escribe tu respuesta debajo, ejecuta, y te digo si te salió 💛"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "\n",
    "# El ayudante de los cuadernos. Trae la corrección de los ejercicios y, en los\n",
    "# capítulos de consola, la celda mágica que ejecuta los comandos. Se baja en\n",
    "# vez de venir pegado aquí para que siempre sea el último.\n",
    "urllib.request.urlretrieve(\n",
    "    \"https://missyera.com/static/cuadernos/revisa.py\", \"revisa.py\")\n",
    "import revisa\n",
    "revisa.carga({\n",
    "    1: \"bm9tYnJlICAgICAgICAgICAgICAgICAgICAgIGNpdWRhZCAgICBzb2xlcyAgICAgcGVkaWRvcwotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSAgLS0tLS0tLS0gIC0tLS0tLS0tICAtLS0tLS0tCkRpc3RyaWJ1aWRvcmEgUGF6IDA1OCAgICAgICBMaW1hICAgICAgMTAxMzEuNzIgIDE3CkFsbWFjZW5lcyBWZWdhIDA2NyAgICAgICAgICBQaXVyYSAgICAgOTUxNi4wNiAgIDEyCk1hcmtldCBDZW50cmFsIDA4NyAgICAgICAgICBDaGljbGF5byAgODE0OC41NiAgIDEyClJlc3RhdXJhbnRlIE1pcmFmbG9yZXMgMDkzICBUcnVqaWxsbyAgODExOS45NSAgIDEyCiAgTUFSS0VUIENFTlRSQUwgMDc4ICAgICAgICBDaGljbGF5byAgNzk3Ny45ICAgIDE0\",\n",
    "    2: \"Y2F0ZWdvcmlhICAgICAgICAgdW5pZGFkZXMgIHNvbGVzCi0tLS0tLS0tLS0tLS0tLS0gIC0tLS0tLS0tICAtLS0tLS0tLS0KTGltcGllemEgICAgICAgICAgMTAwNzYgICAgIDM2MTk1MS4zMwpTbmFja3MgICAgICAgICAgICA2NTQ4ICAgICAgMzM4MzExLjg5CkJlYmlkYXMgICAgICAgICAgIDU0ODggICAgICAzMTY5NzMuNQpBYmFycm90ZXMgICAgICAgICA3NDU0ICAgICAgMzEwMzAyLjY4CkN1aWRhZG8gcGVyc29uYWwgIDM2MDEgICAgICAyMTgwNzkuNzU=\",\n",
    "    3: \"c2VnbWVudG8gICAgdGlja2V0ICBwZWRpZG9zCi0tLS0tLS0tLS0gIC0tLS0tLSAgLS0tLS0tLQpNaW5pbWFya2V0ICA2MzAuNDMgIDE3NApIb3JlY2EgICAgICA2MTAuMDUgIDI1NwpNYXlvcmlzdGEgICA2MDcuNTkgIDE5NApCb2RlZ2EgICAgICA1OTYuMDUgIDI1MQ==\",\n",
    "    4: \"Y2l1ZGFkICAgIGNsaWVudGVzICBwZWRpZG9zICBzb2xlcwotLS0tLS0tLSAgLS0tLS0tLS0gIC0tLS0tLS0gIC0tLS0tLS0tLQpDaGljbGF5byAgMjUgICAgICAgIDIxMiAgICAgIDExNTk3OS40MwpQaXVyYSAgICAgMjQgICAgICAgIDE2NyAgICAgIDEwNzE2NS4xNwpBcmVxdWlwYSAgMjIgICAgICAgIDE2OCAgICAgIDEwMTQzMi41MQpDdXNjbyAgICAgMTcgICAgICAgIDExMiAgICAgIDY0OTY2LjIzClRydWppbGxvICAxNyAgICAgICAgMTEzICAgICAgNjQxNTAuNjYKTGltYSAgICAgIDE1ICAgICAgICAxMDQgICAgICA2Mzc0OC41Mw==\",\n",
    "    5: \"bm9tYnJlICAgICAgIGNhdGVnb3JpYSAgICAgICAgIHVuaWRhZGVzCi0tLS0tLS0tLS0tICAtLS0tLS0tLS0tLS0tLS0tICAtLS0tLS0tLQpQcm9kdWN0byAyMiAgQ3VpZGFkbyBwZXJzb25hbCAgMTA3NQpQcm9kdWN0byAyMyAgQmViaWRhcyAgICAgICAgICAgMTAxNgpQcm9kdWN0byAzOCAgQWJhcnJvdGVzICAgICAgICAgMTAxMw==\",\n",
    "    6: \"cGVkaWRvc19zaW5fZGV0YWxsZQotLS0tLS0tLS0tLS0tLS0tLS0tCjA=\",\n",
    "}, lenguaje=\"sql\")"
   ]
  },
  {
   "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": [
    "%%revisa 1\n",
    "-- tu turno"
   ]
  },
  {
   "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": [
    "%%revisa 2\n",
    "-- tu turno"
   ]
  },
  {
   "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": [
    "%%revisa 3\n",
    "-- tu turno"
   ]
  },
  {
   "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": [
    "%%revisa 4\n",
    "-- tu turno"
   ]
  },
  {
   "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": [
    "%%revisa 5\n",
    "-- tu turno"
   ]
  },
  {
   "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": [
    "%%revisa 6\n",
    "-- tu turno"
   ]
  },
  {
   "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": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tu turno"
   ]
  },
  {
   "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?** La respuesta está en el cuaderno de soluciones. Míralo tú primero."
   ]
  },
  {
   "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"
   ]
  },
  {
   "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
}