LEFT JOIN y NULL: encontrar lo que falta

Conserva todas las filas de una tabla con LEFT JOIN, detecta las ausencias con IS NULL y reemplázalas con COALESCE.

8 min · 3 preguntas

El INNER JOIN de la lección anterior oculta a los clientes sin pedidos. Sin embargo, la pregunta de negocio más frecuente es precisamente: ¿quién falta? Clientes inactivos, productos nunca vendidos, facturas sin pago... Ejecuta este script en sqliteonline.com o sqlite3: añade un cuarto cliente sin pedidos.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Alice', 'Lyon'),
  (2, 'Karim', 'Paris'),
  (3, 'Mina', 'Nantes'),
  (4, 'Paulo', 'Lille');

INSERT INTO orders (id, customer_id, amount)
VALUES
  (1, 1, 49.90),
  (2, 1, 15.00),
  (3, 2, 120.50);

LEFT JOIN conserva todas las filas de la tabla de la izquierda (la que se nombra antes de la palabra clave), incluso sin coincidencia a la derecha. Cuando falta la coincidencia, las columnas de la tabla de la derecha se rellenan con NULL.

SELECT c.name, o.id AS order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id;

Para aislar lo que falta, filtra por estos valores NULL: es el patrón anti-join, un reflejo esencial en el análisis de datos. La columna evaluada debe provenir de la tabla de la derecha y no debe ser NULL de forma natural, de ahí la elección de su clave primaria o.id.

SELECT c.name, c.city
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
WHERE o.id IS NULL;

Una última herramienta: COALESCE(a, b) devuelve a si no es NULL, y en caso contrario b. Combinada con un LEFT JOIN y una agregación, convierte las ausencias en ceros utilizables en un informe o una factura.

SELECT c.name,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id
ORDER BY total_spent DESC;

Prueba de conocimientos

Comprueba que has retenido los puntos clave de esta lección.

1. ¿Qué combinación lista los clientes que nunca han pedido?
   • INNER JOIN y luego WHERE o.id IS NULL
   • LEFT JOIN y luego WHERE o.id IS NULL
   • LEFT JOIN y luego WHERE o.id = NULL
2. ¿Qué devuelve la condición amount = NULL?
   • Verdadero para las filas donde amount es NULL
   • Nunca verdadero: la comparación queda indefinida
   • Un error de sintaxis
3. ¿Qué devuelve COALESCE(SUM(o.amount), 0) para un cliente sin pedidos?
   • 0
   • NULL
   • El cliente queda excluido del resultado
   • Un error de agregación