LEFT JOIN no SQLite: como manter todas as linhas

LEFT JOIN preserva tudo da tabela da esquerda

O INNER JOIN só devolve linhas quando há correspondência dos dois lados. Na maioria das vezes é exatamente isso que você quer — mas nem sempre. Em algumas situações, a ausência de correspondência é justamente a resposta que você procura: usuários que ainda não fizeram nenhum pedido, produtos que nunca venderam, posts sem comentários. Para esses casos, entra o LEFT JOIN.

O LEFT JOIN no SQLite retorna todas as linhas da tabela da esquerda. Se houver uma linha correspondente na tabela da direita, você recebe as colunas combinadas. Se não houver, a linha da esquerda continua aparecendo no resultado e as colunas da direita vêm 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 não tem pedidos, mas mesmo assim aparece — com NULL na coluna total. Troque LEFT JOIN por INNER JOIN e a Cleo some de vez.

O modelo mental

Leia a query de cima para baixo pensando na tabela da esquerda como uma âncora. Toda linha de users vai aparecer no resultado, aconteça o que acontecer. Aí o LEFT JOIN pergunta, para cada usuário: "existe alguma linha correspondente em orders?"

• Achou correspondência → gruda as colunas casadas na linha do usuário.
• Várias correspondências → gera uma linha de saída para cada match (a Ada tem dois pedidos, então aparece duas vezes).
• Nenhuma correspondência → gera uma linha com NULL em todas as colunas vindas da tabela da direita.

Esse último caso é justamente a razão de existir o LEFT JOIN. O NULL aqui não significa "não sabemos" — significa "não há nada do outro lado para grudar".

LEFT OUTER JOIN é exatamente a mesma operação. A palavra OUTER é opcional no SQLite, e quase ninguém escreve.

Encontrando linhas sem correspondência

O caso de uso clássico do LEFT JOIN: achar as linhas da tabela da esquerda que não têm match do outro lado. O macete é filtrar por uma coluna da tabela da direita que normalmente é NOT NULL nos dados reais — geralmente a chave primária — e checar se ela ficou NULL depois do 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;

Só a Cleo aparece. O join tenta anexar os dados de pedidos quando existem, e o WHERE o.id IS NULL mantém apenas as linhas em que o anexo falhou. Esse padrão também é conhecido como "anti-join".

ON vs WHERE: a pegadinha sutil

Essa é a confusão mais comum quando o assunto é LEFT JOIN, e vale a pena parar pra entender. As condições podem ir tanto na cláusula ON quanto na WHERE, só que o comportamento muda completamente em joins externos.

• O ON é avaliado durante o join. As condições ali decidem quais linhas da tabela à direita contam como correspondência.
• O WHERE roda depois que o join já produziu suas linhas. Ele filtra o resultado final.

Veja o que acontece quando você coloca uma condição sobre a tabela à direita no 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');

-- Errado: transforma silenciosamente o LEFT JOIN em 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';

A Cléo não tem nenhum pedido, então o o.status fica NULL na linha dela, e NULL = 'shipped' não é verdadeiro — ela acaba sendo descartada. O status do Bóris é 'pending', então também sai do resultado. Na prática, o LEFT JOIN virou um INNER JOIN disfarçado.

A solução é jogar a condição para dentro do ON, fazendo com que ela filtre as correspondências em vez das linhas finais:

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';

Agora todos os usuários aparecem. Ada vem com seu pedido enviado; Boris vem com NULL (o pedido pendente dele não bateu com o filtro); Cleo vem com NULL (não tem pedido nenhum). É exatamente essa a resposta certa quando a pergunta é: "me mostre todos os usuários e, se tiverem, os pedidos enviados".

Regra de bolso: condições sobre a tabela da esquerda podem ir no WHERE. Já condições sobre a tabela da direita quase sempre pertencem ao ON — a menos que você queira justamente achar linhas sem correspondência usando IS NULL. É aqui que mora a diferença entre left join where vs on que confunde tanta gente.

Contando com LEFT JOIN

Uma tarefa clássica: contar quantas linhas relacionadas cada "pai" tem, incluindo os que têm zero. Com INNER JOIN os zeros somem. Já um LEFT JOIN combinado com COUNT em uma coluna da tabela da direita devolve o resultado certinho:

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;

Dois detalhes que merecem atenção:

• COUNT(o.id) conta apenas as linhas não nulas do lado direito. A Cleo recebe 0, e não 1 — porque COUNT ignora NULL. Se você escrevesse COUNT(*), a Cleo apareceria com 1 (a linha existe, só está cheia de NULLs). Na prática, COUNT(right.id) é quase sempre o que você quer.
• COALESCE(SUM(o.total), 0) transforma o NULL da soma da Cleo em 0. Sem isso, ela apareceria com receita NULL, o que até é tecnicamente correto, mas fica feio na exibição.

LEFT JOIN com várias tabelas no SQLite

O LEFT JOIN é encadeável. Cada join pega o resultado parcial e junta mais uma tabela em cima dele. Mas atenção: depois que uma coluna virou anulável por causa de um LEFT JOIN, continue usando LEFT JOIN para qualquer tabela que dependa dela — caso contrário, o próximo INNER JOIN vai descartar silenciosamente justamente as linhas que você queria manter.

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;

Voltam três usuários. Ada tem pedido e envio. Boris tem pedido, mas sem envio (a transportadora vem como NULL). Cleo não tem pedido nenhum, então tanto o.total quanto s.carrier ficam NULL. A sequência de LEFT JOINs mantém todos os usuários, não importa em que ponto da cadeia de relacionamentos os dados deixam de existir.

Quando usar LEFT JOIN no SQLite

Use LEFT JOIN quando a pergunta gira em torno da tabela à esquerda, e a tabela à direita serve apenas como informação complementar. Frases como "todos os usuários, com seus pedidos se houver" ou "todos os produtos e a avaliação mais recente de cada um" se traduzem diretamente em LEFT JOIN.

Já o INNER JOIN faz sentido quando os dois lados são igualmente obrigatórios — "pedidos com os dados do usuário" não tem sentido para um pedido sem usuário, então o filtro automático do inner join é justamente o que você quer.

Se você se pegou escrevendo LEFT JOIN ... WHERE right.col IS NOT NULL, na verdade queria um INNER JOIN. Se escreveu LEFT JOIN ... WHERE right.col IS NULL, então queria um anti-join — e acertou em cheio.

A seguir: self-joins

Às vezes a tabela com a qual você quer fazer o join é a mesma que você já está consultando — funcionários e seus gerentes, categorias e suas categorias-pai, pares de usuários da mesma cidade. Isso é um self-join, e é o assunto da próxima página.