Docs ejecutables de SQLite: LEFT JOIN

Q: ¿Qué hace un LEFT JOIN en SQLite?

LEFT JOIN devuelve todas las filas de la tabla de la izquierda y, además, las filas de la derecha que coincidan. Si no hay coincidencia en la tabla derecha, la fila de la izquierda sigue apareciendo y las columnas de la derecha llegan como NULL. LEFT OUTER JOIN es exactamente lo mismo: en SQLite el OUTER es opcional.

Q: ¿En qué se diferencian LEFT JOIN e INNER JOIN en SQLite?

INNER JOIN solo devuelve las filas en las que la condición de unión se cumple en ambas tablas. LEFT JOIN, en cambio, te trae todas las filas de la izquierda sí o sí, y rellena con NULL las columnas de la derecha cuando no hay match. Úsalo cuando 'no hay coincidencia' es en sí una respuesta útil, por ejemplo, para listar usuarios sin pedidos.

Q: ¿Por qué mi LEFT JOIN se comporta como un INNER JOIN?

Casi siempre es por una cláusula WHERE que filtra una columna de la tabla derecha sin contemplar los NULL. Las condiciones sobre la tabla derecha van en el ON, no en el WHERE; o bien escribe WHERE right.col IS NULL si lo que quieres es justo encontrar las filas sin coincidencia. Un WHERE right.col = 'x' se carga en silencio todas las filas no coincidentes.

LEFT JOIN: conserva todo lo de la izquierda

INNER JOIN solo devuelve filas cuando hay coincidencia en ambos lados. Eso suele ser lo que buscas… pero no siempre. A veces la respuesta que necesitas es justo la "no coincidencia": usuarios que aún no han hecho ningún pedido, productos que nunca se han vendido, publicaciones sin un solo comentario. Para esos casos entra en juego el LEFT JOIN de SQLite.

Un LEFT JOIN devuelve todas las filas de la tabla de origen (la "izquierda"). Si la otra tabla tiene una fila que coincide, obtienes las columnas emparejadas; si no la hay, la fila se conserva igualmente y las columnas del otro lado vienen como NULL.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

Cleo no tiene pedidos, pero aun así aparece en el resultado: con NULL en la columna total. Cambia LEFT JOIN por INNER JOIN y Cleo desaparece sin dejar rastro.

El modelo mental

Lee la consulta de arriba abajo y piensa en la tabla de la izquierda como el ancla. Cada fila de users va a aparecer en el resultado, pase lo que pase. Lo que hace LEFT JOIN es preguntarse, para cada usuario: "¿existe alguna fila que coincida en orders?"

Si hay coincidencia → pega las columnas coincidentes a la fila del usuario.
Si hay varias coincidencias → genera una fila de salida por cada coincidencia (Ada tiene dos pedidos, así que aparece dos veces).
Si no hay coincidencia → genera una fila con NULL en todas las columnas de la tabla de la derecha.

Ese último caso es justo la razón de ser del LEFT JOIN. Aquí NULL no significa "no lo sabemos", sino "no hay nada en la derecha que pegar".

LEFT OUTER JOIN es exactamente la misma operación. La palabra OUTER es opcional en SQLite y la mayoría de la gente la omite.

Buscar filas sin coincidencia en SQLite

El caso de uso clásico de LEFT JOIN: encontrar filas de la tabla de la izquierda que no tienen coincidencia en la derecha. El truco está en filtrar por una columna de la tabla derecha que en los datos reales nunca sea NULL (normalmente su clave primaria) y comprobar si vale NULL después del join:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

Solo aparece Cleo. El join engancha los datos del pedido cuando existen, y luego el WHERE o.id IS NULL se queda únicamente con las filas en las que el enganche falló. A esto a veces se le llama "anti-join."

ON vs WHERE: la trampa sutil

Este es el bug más común al trabajar con LEFT JOIN, así que merece la pena detenerse aquí. Las condiciones pueden ir en la cláusula ON o en la cláusula WHERE, pero su comportamiento cambia bastante en los outer joins.

ON se evalúa mientras se ejecuta el join. Las condiciones que pongas ahí deciden qué filas de la tabla derecha cuentan como coincidencia.
WHERE se evalúa después de que el join ya haya generado sus filas. Filtra el resultado combinado.

Mira lo que pasa cuando metes una condición sobre la tabla derecha en el WHERE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- Incorrecto: convierte silenciosamente el LEFT JOIN en un INNER JOIN
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

Cleo no tiene pedidos, así que en su fila o.status vale NULL, y NULL = 'shipped' no se cumple, por lo que queda fuera del resultado. El estado de Boris es 'pending', así que también desaparece. En la práctica, el LEFT JOIN terminó comportándose como un INNER JOIN sin avisar.

La solución: mover la condición al ON para que filtre las coincidencias y no las filas del resultado:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

Ahora aparecen todos los usuarios. Ada muestra su pedido enviado; Boris aparece con NULL (su pedido pendiente no califica como coincidencia); Cleo también queda en NULL (no tiene pedidos). Esa es la respuesta correcta cuando la pregunta es "muéstrame todos los usuarios, junto con sus pedidos enviados si los hay".

Regla práctica: las condiciones sobre la tabla izquierda pueden ir en el WHERE. Las condiciones sobre la tabla derecha casi siempre van en el ON — salvo que quieras precisamente encontrar filas sin coincidencia usando IS NULL.

Contar filas con LEFT JOIN en SQLite

Una tarea típica: contar cuántas filas relacionadas tiene cada padre, incluyendo a los padres con cero. Un INNER JOIN descartaría esos ceros. En cambio, un LEFT JOIN combinado con COUNT sobre una columna de la tabla derecha sí devuelve el resultado esperado:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

Dos detalles que vale la pena notar:

COUNT(o.id) cuenta las filas no nulas del lado derecho. Cleo obtiene 0, no 1, porque COUNT ignora los NULL. Si hubieras puesto COUNT(*), Cleo saldría con 1 (la fila existe, simplemente tiene NULL adentro). En la práctica, COUNT(right.id) casi siempre es lo que buscas.
COALESCE(SUM(o.total), 0) convierte el NULL de la suma de Cleo en 0. Sin esto, mostraría NULL como ingresos: técnicamente correcto, pero feo de presentar.

LEFT JOIN con varias tablas en SQLite

Los LEFT JOIN se encadenan. Cada join toma el resultado acumulado y le suma otra tabla. Una vez que una columna pasó a ser nullable por culpa de un LEFT JOIN, sigue usando LEFT JOIN para todas las tablas que cuelguen de ella; de lo contrario, el siguiente INNER JOIN te va a descartar silenciosamente las filas que querías conservar.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

Vuelven los tres usuarios. Ada tiene un pedido y un envío. Boris tiene un pedido, pero no envío (el campo carrier está en NULL). Cleo no tiene pedido, así que tanto o.total como s.carrier aparecen como NULL. La cadena de LEFT JOIN conserva a cada usuario, sin importar en qué punto de la relación se queden sin datos.

Cuándo usar LEFT JOIN en SQLite

Recurre a LEFT JOIN cuando la pregunta gira en torno a la tabla de la izquierda y la tabla de la derecha aporta información complementaria. Frases como "todos los usuarios, con sus pedidos si los hay" o "todos los productos y su última reseña" se traducen de forma directa a un LEFT JOIN.

Usa INNER JOIN cuando ambos lados sean igual de obligatorios. "Pedidos con los datos de su usuario" no tiene sentido para un pedido sin usuario, así que ahí lo que quieres es justamente ese filtrado del inner join.

Si te ves escribiendo LEFT JOIN ... WHERE right.col IS NOT NULL, en realidad querías un INNER JOIN. Si escribes LEFT JOIN ... WHERE right.col IS NULL, lo que buscas es un anti-join (filas sin coincidencia), y ahí sí lo estás haciendo bien.

Siguiente paso: self-joins

A veces la tabla con la que quieres hacer join es la misma que ya estás consultando: empleados y sus jefes, categorías y sus padres, pares de usuarios de la misma ciudad. Eso se llama self-join, y es el tema de la siguiente página.

Preguntas frecuentes

¿Qué hace un LEFT JOIN en SQLite?

LEFT JOIN devuelve todas las filas de la tabla de la izquierda y, además, las filas de la derecha que coincidan. Si no hay coincidencia en la tabla derecha, la fila de la izquierda sigue apareciendo y las columnas de la derecha llegan como NULL. LEFT OUTER JOIN es exactamente lo mismo: en SQLite el OUTER es opcional.

¿En qué se diferencian LEFT JOIN e INNER JOIN en SQLite?

INNER JOIN solo devuelve las filas en las que la condición de unión se cumple en ambas tablas. LEFT JOIN, en cambio, te trae todas las filas de la izquierda sí o sí, y rellena con NULL las columnas de la derecha cuando no hay match. Úsalo cuando 'no hay coincidencia' es en sí una respuesta útil, por ejemplo, para listar usuarios sin pedidos.

¿Por qué mi LEFT JOIN se comporta como un INNER JOIN?

Casi siempre es por una cláusula WHERE que filtra una columna de la tabla derecha sin contemplar los NULL. Las condiciones sobre la tabla derecha van en el ON, no en el WHERE; o bien escribe WHERE right.col IS NULL si lo que quieres es justo encontrar las filas sin coincidencia. Un WHERE right.col = 'x' se carga en silencio todas las filas no coincidentes.

Conceptos relacionados

LEFT JOIN en SQLite: conserva todas las filas