RIGHT JOIN, FULL OUTER JOIN e CROSS JOIN

RIGHT JOIN: manter a tabela da direita

O LEFT JOIN mantém todas as linhas da esquerda. Seu simétrico, o RIGHT JOIN (ou RIGHT OUTER JOIN), mantém todas as linhas da direita (a que vai depois do JOIN), tenham ou não par na esquerda. Quando não há correspondência, as colunas da tabela esquerda ficam como NULL.

SELECT produtos.nome, categorias.nome
FROM produtos
RIGHT JOIN categorias ON produtos.categoria_id = categorias.id;

Aqui a direita é categorias, então aparecem todas as categorias, inclusive as que não têm nenhum produto (com NULL na coluna do produto). Repare que A RIGHT JOIN B dá o mesmo resultado que B LEFT JOIN A: são duas formas de escrever a mesma coisa. Por isso, na prática, costuma-se preferir o LEFT JOIN, reordenando as tabelas.

FULL OUTER JOIN: manter ambos os lados

O FULL OUTER JOIN combina os dois: mantém todas as linhas de ambas as tabelas. As que têm par são emparelhadas; as que não têm aparecem mesmo assim com NULL no lado que falta.

SELECT produtos.nome, categorias.nome
FROM produtos
FULL OUTER JOIN categorias ON produtos.categoria_id = categorias.id;

No resultado aparecem tanto os produtos sem categoria (com NULL no nome da categoria) quanto as categorias sem produtos (com NULL no nome do produto). É a união do que retornariam um LEFT JOIN e um RIGHT JOIN.

Nota de compatibilidade: o SQLite só suporta RIGHT JOIN e FULL OUTER JOIN a partir da versão 3.39 (2022). Em versões mais antigas você teria que simulá-los reordenando tabelas (LEFT JOIN) ou com UNION.

CROSS JOIN: o produto cartesiano

O CROSS JOIN não leva ON: combina cada linha da esquerda com cada linha da direita. É o produto cartesiano. Se A tem 3 linhas e B tem 4, o resultado tem 3 × 4 = 12 linhas.

-- Todas as combinações de tamanho e cor.
SELECT tamanhos.valor, cores.valor
FROM tamanhos
CROSS JOIN cores;

Serve para gerar todas as combinações possíveis (tamanhos × cores, dias × horas...). É preciso usá-lo com cuidado: o número de linhas cresce muito rápido. De fato, um INNER JOIN nada mais é do que um CROSS JOIN ao qual depois se aplica a condição do ON como filtro.

Exemplos

FULL OUTER JOIN: produtos e categorias, falte o que faltar

SELECT produtos.nome AS produto, categorias.nome AS categoria
FROM produtos
FULL OUTER JOIN categorias ON produtos.categoria_id = categorias.id
ORDER BY categoria, produto;

CROSS JOIN: todas as combinações de tamanho e cor

SELECT t.valor AS tamanho, c.valor AS cor
FROM tamanhos AS t
CROSS JOIN cores AS c
ORDER BY t.valor, c.valor;