Vários JOINs, aliases e self-join

Aliases de tabela

Escrever produtos.nome e categorias.nome repetidamente fica cansativo. Um alias é um nome curto que você atribui a uma tabela com AS (ou sem ele):

SELECT p.nome, c.nome AS categoria
FROM produtos AS p
INNER JOIN categorias AS c ON p.categoria_id = c.id;

p e c são os aliases. A partir daí você se refere às colunas como p.nome e c.nome. É mais curto e mais legível.

Vários JOINs encadeados

Você pode combinar mais de duas tabelas: cada JOIN adiciona uma tabela nova com seu próprio ON. Eles são avaliados de cima para baixo, encadeando os emparelhamentos:

SELECT p.nome, c.nome, f.nome
FROM produtos AS p
INNER JOIN categorias AS c ON p.categoria_id = c.id
INNER JOIN funcionarios AS f ON c.responsavel_id = f.id;

Self-join: uma tabela consigo mesma

Às vezes uma tabela se relaciona consigo mesma. Pensemos em funcionarios, onde cada um tem um chefe_id que aponta para outro funcionário da mesma tabela:

Para mostrar cada funcionário junto ao nome de seu chefe, unimos funcionarios com funcionarios. Como não podemos ter a mesma tabela duas vezes com o mesmo nome, os aliases são obrigatórios: um para o funcionário, outro para o chefe.

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
INNER JOIN funcionarios AS c ON f.chefe_id = c.id;

Aqui f é "o funcionário" e c é "seu chefe": ambos são a mesma tabela, mas o ON os relaciona como dois papéis distintos.

Com INNER JOIN, Ana (que não tem chefe) não aparece, porque seu chefe_id é NULL e não emparelha com ninguém. Se você quisesse incluí-la com seu chefe em NULL, usaria LEFT JOIN.

Exemplos

Self-join: cada funcionário e o nome de seu chefe

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
INNER JOIN funcionarios AS c ON f.chefe_id = c.id
ORDER BY f.nome;