Como um JOIN funciona

JOIN junta duas tabelas usando uma coluna em comum, e o ON diz qual é esse par.

O que o banco faz é pegar as linhas de uma tabela, procurar na outra a linha que

bate e devolver as duas lado a lado. Sem correspondência, o comportamento depende

do tipo de join.

A forma geral

SELECT <colunas>
FROM <tabela_esquerda> <alias>
[INNER|LEFT|RIGHT] JOIN <tabela_direita> <alias> ON <chave> = <chave>;

O INNER JOIN é o padrão: as duas tabelas listadas depois do FROM contam como

um INNER JOIN escrito por extenso. O alias é o nome curto usado a partir

dali — AS é opcional, FROM matriculas m e FROM matriculas AS m são

idênticos.

INNER JOIN esconde o que não tem par

INNER JOIN devolve só as linhas que têm correspondência dos dois lados. Um

curso que ninguém matriculou não aparece, e um aluno sem matrícula também não.

Quando a consulta pedida é "todos os cursos", e um deles está vazio, ele

simplesmente sumiu do resultado — sem aviso, sem erro. É a causa mais comum de

relatório com linha faltando e ninguém sabe por quê.

Onde a linha some

SELECT <colunas> FROM <tabela> INNER JOIN <outra> ON <chave> = <chave>;

LEFT JOIN devolve a linha sem par

LEFT JOIN (ou LEFT OUTER JOIN) começa pela tabela do FROM e sempre

devolve todas as linhas dela. Quando não há correspondência, as colunas da tabela

do JOIN vêm preenchidas com NULL. Por isso LEFT JOIN é a resposta padrão

para "todos, e quero ver quem ficou de fora" — contagem de matrícula, lista de

cursos vazios, relatório com linha zerada.

O par que mostra a diferença

SELECT <colunas>, COUNT(<coluna_do_join>) FROM <tabela> LEFT JOIN <outra> ON <chave> = <chave> GROUP BY <coluna>;

Contar COUNT(*) depois do LEFT JOIN dá número errado, porque a linha sem par

entra na contagem como se fosse uma matrícula. O que se conta é a coluna da

tabela do JOIN, que vem NULL justamente na linha que não teve par — e por isso

a contagem sai certa.

RIGHT JOIN é o LEFT JOIN com as tabelas trocadas de lugar, e escrever assim

costuma deixar a consulta mais legível. Já CROSS JOIN sem WHERE multiplica as

linhas: duas tabelas de 3 e 4 linhas devolvem 12.

Exemplo

<?php
declare(strict_types=1);

// INNER JOIN e LEFT JOIN sobre a mesma tabela de juncao.
// A diferenca que importa: o curso sem matricula some no INNER e aparece
// com zero no LEFT.

$sql = <<<'SQL'
-- INNER JOIN: devolve so quem tem matricula dos dois lados
SELECT c.nome AS curso, m.data_matricula
FROM cursos c
INNER JOIN matriculas m ON m.curso_id = c.id
WHERE m.situacao = 'ativa'
ORDER BY c.nome;

-- LEFT JOIN: todos os cursos entram, mesmo os que ninguem matriculou.
-- Onde nao ha par, as colunas da tabela da direita vem preenchidas com NULL.
SELECT c.nome AS curso, m.data_matricula
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.id
ORDER BY c.nome, m.data_matricula;

-- contagem que revela a diferenca: no LEFT o curso vazio vem com 0,
-- e por isso conta-se m.id e nao COUNT(*)
SELECT c.nome AS curso, COUNT(m.id) AS matriculados
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.id
GROUP BY c.nome
ORDER BY matriculados DESC;

-- a mesma contagem em INNER: o curso sem matricula simplesmente nao aparece
SELECT c.nome AS curso, COUNT(*) AS matriculados
FROM cursos c
INNER JOIN matriculas m ON m.curso_id = c.id
GROUP BY c.nome
HAVING COUNT(*) > 0;

-- LEFT JOIN do aluno: sem matricula, o curso vem preenchido com NULL
SELECT a.nome AS aluno, c.nome AS curso
FROM alunos a
LEFT JOIN matriculas m ON m.aluno_id = a.id
LEFT JOIN cursos c ON c.id = m.curso_id
WHERE a.ativo = 1
ORDER BY a.nome;
SQL;

echo $sql . "\n";

Saída real

-- INNER JOIN: devolve so quem tem matricula dos dois lados
SELECT c.nome AS curso, m.data_matricula
FROM cursos c
INNER JOIN matriculas m ON m.curso_id = c.id
WHERE m.situacao = 'ativa'
ORDER BY c.nome;

-- LEFT JOIN: todos os cursos entram, mesmo os que ninguem matriculou.
-- Onde nao ha par, as colunas da tabela da direita vem preenchidas com NULL.
SELECT c.nome AS curso, m.data_matricula
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.id
ORDER BY c.nome, m.data_matricula;

-- contagem que revela a diferenca: no LEFT o curso vazio vem com 0,
-- e por isso conta-se m.id e nao COUNT(*)
SELECT c.nome AS curso, COUNT(m.id) AS matriculados
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.id
GROUP BY c.nome
ORDER BY matriculados DESC;

-- a mesma contagem em INNER: o curso sem matricula simplesmente nao aparece
SELECT c.nome AS curso, COUNT(*) AS matriculados
FROM cursos c
INNER JOIN matriculas m ON m.curso_id = c.id
GROUP BY c.nome
HAVING COUNT(*) > 0;

-- LEFT JOIN do aluno: sem matricula, o curso vem preenchido com NULL
SELECT a.nome AS aluno, c.nome AS curso
FROM alunos a
LEFT JOIN matriculas m ON m.aluno_id = a.id
LEFT JOIN cursos c ON c.id = m.curso_id
WHERE a.ativo = 1
ORDER BY a.nome;

Mais de dois JOIN

Nada impede encadear quantos JOIN forem necessários: a consulta é uma cadeia em

que cada ON liga a próxima tabela a tudo o que já está montado antes. Com três

ou mais tabelas os nomes de coluna se repetem, e é por isso que entra o apelido

de tabela.

A cadeia, com apelido em cada uma

SELECT <colunas>
FROM <tabela> <alias>
INNER JOIN <tabela> <alias> ON <chave> = <chave>
INNER JOIN <tabela> <alias> ON <chave> = <chave>
[INNER|LEFT] JOIN <tabela> <alias> ON <chave> = <chave> [AND <extra>]
WHERE <condicao>;

Depois do FROM tabela alias, o nome original só existe como apelido. Com quatro

tabelas, COUNT(*) passa a contar a combinação inteira: um aluno com três

matrículas vira três linhas, e é por isso que COUNT(DISTINCT aluno_id) é a

forma correta de contar pessoas.

O ON na posição errada muda o resultado

O ON entra no FROM, e não no WHERE. Escrever LEFT JOIN com o filtro no

WHERE transforma a consulta em INNER JOIN, porque o WHERE descarta

justamente as linhas com NULL que o LEFT JOIN tinha preservado. Com ON, o

filtro age antes do emparelhamento e a linha sem par sobrevive.

Onde o filtro vai, linha por linha

Filtro no ON ainda aceita a própria coluna do JOIN, e é assim que a mesma

consulta mostra só as matrículas ativas sem apagar o curso que não tem nenhuma.

Exemplo

<?php
declare(strict_types=1);

// JOIN encadeado com tres e quatro tabelas, com apelido em cada uma.
// O detalhe que quebra: filtro no ON preserva o LEFT JOIN, filtro no WHERE nao.

$sql = <<<'SQL'
-- tres tabelas: a cadeia vai montando o resultado passo a passo
SELECT a.nome AS aluno, c.nome AS curso, m.data_matricula
FROM matriculas m
INNER JOIN alunos a ON a.id = m.aluno_id
INNER JOIN cursos c ON c.id = m.curso_id
WHERE m.situacao = 'ativa'
ORDER BY a.nome, c.nome;

-- quatro tabelas, todas com apelido curto
SELECT a.nome AS aluno,
       c.nome AS curso,
       p.nome AS professor,
       m.data_matricula
FROM matriculas m
INNER JOIN alunos a     ON a.id = m.aluno_id
INNER JOIN cursos c     ON c.id = m.curso_id
INNER JOIN professores p ON p.area = 'Banco de dados'
ORDER BY c.nome, a.nome;

-- ON preserva o LEFT JOIN: o curso sem matricula continua na lista
SELECT c.nome AS curso, COUNT(m.id) AS ativas
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.id AND m.situacao = 'ativa'
GROUP BY c.nome
ORDER BY c.nome;

-- o mesmo filtro no WHERE descarta a linha sem par: virou INNER JOIN
SELECT c.nome AS curso, COUNT(m.id) AS ativas
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.id
WHERE m.situacao = 'ativa'
GROUP BY c.nome
ORDER BY c.nome;

-- contagem sem repeticao: um aluno com tres matriculas conta uma vez so
SELECT c.nome AS curso, COUNT(DISTINCT m.aluno_id) AS alunos
FROM cursos c
INNER JOIN matriculas m ON m.curso_id = c.id
GROUP BY c.nome
HAVING COUNT(DISTINCT m.aluno_id) >= 2
ORDER BY alunos DESC;
SQL;

echo $sql . "\n";

Saída real

-- tres tabelas: a cadeia vai montando o resultado passo a passo
SELECT a.nome AS aluno, c.nome AS curso, m.data_matricula
FROM matriculas m
INNER JOIN alunos a ON a.id = m.aluno_id
INNER JOIN cursos c ON c.id = m.curso_id
WHERE m.situacao = 'ativa'
ORDER BY a.nome, c.nome;

-- quatro tabelas, todas com apelido curto
SELECT a.nome AS aluno,
       c.nome AS curso,
       p.nome AS professor,
       m.data_matricula
FROM matriculas m
INNER JOIN alunos a     ON a.id = m.aluno_id
INNER JOIN cursos c     ON c.id = m.curso_id
INNER JOIN professores p ON p.area = 'Banco de dados'
ORDER BY c.nome, a.nome;

-- ON preserva o LEFT JOIN: o curso sem matricula continua na lista
SELECT c.nome AS curso, COUNT(m.id) AS ativas
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.id AND m.situacao = 'ativa'
GROUP BY c.nome
ORDER BY c.nome;

-- o mesmo filtro no WHERE descarta a linha sem par: virou INNER JOIN
SELECT c.nome AS curso, COUNT(m.id) AS ativas
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.id
WHERE m.situacao = 'ativa'
GROUP BY c.nome
ORDER BY c.nome;

-- contagem sem repeticao: um aluno com tres matriculas conta uma vez so
SELECT c.nome AS curso, COUNT(DISTINCT m.aluno_id) AS alunos
FROM cursos c
INNER JOIN matriculas m ON m.curso_id = c.id
GROUP BY c.nome
HAVING COUNT(DISTINCT m.aluno_id) >= 2
ORDER BY alunos DESC;

Subquery no IN

Subquery é uma consulta dentro de outra, no lugar de um valor ou de uma lista. No

IN, ela devolve a lista que a consulta externa vai comparar, e resolve o

problema de não poder escrever WHERE id = (SELECT id FROM ... LIMIT 1), que dá

erro quando a subconsulta devolve mais de uma linha.

A forma padrão

SELECT <colunas> FROM <tabela>
WHERE <coluna> [NOT] IN (SELECT <coluna> FROM <outra> WHERE <condicao>);

IN compara valores e compara a lista inteira. NOT IN faz o contrário, e traz

uma armadilha: se a subquery devolver um único NULL, o NOT IN não devolve

linha nenhuma, porque NULL não pode ser comparado com !=.

EXISTS e NOT EXISTS

EXISTS pergunta se a subquery devolve alguma linha, e o conteúdo dela não

importa. O WHERE continua na consulta externa, e a subconsulta é correlacionada:

as duas se referem à mesma linha da tabela de fora.

Existe, e não existe

SELECT <colunas> FROM <tabela> <alias>
WHERE [NOT] EXISTS (SELECT 1 FROM <outra> <alias> WHERE <chave> = <alias>.<chave>);

O SELECT 1 dentro do EXISTS é convenção: importa é que a subquery devolve

linha, e 1 é o menor texto que expressa isso. NOT EXISTS é mais seguro que

NOT IN justamente porque NULL não o engana.

Qual dos dois escolher

IN compara valores e EXISTS responde sim ou não. Na prática o EXISTS

correlacionado costuma ser mais rápido, porque o banco pode parar na primeira

linha que encontra, enquanto o IN monta a lista antes de responder.

As formas de comparação com subquery

Exemplo

<?php
declare(strict_types=1);

// Subquery e EXISTS: tres jeitos de perguntar "o que existe em outra tabela".
// EXISTS responde sim ou nao; IN compara uma lista inteira.

$sql = <<<'SQL'
-- 1) IN: a subquery monta a lista e a consulta externa compara com ela
SELECT id, nome, carga_horaria FROM cursos
WHERE id IN (SELECT curso_id FROM matriculas WHERE situacao = 'ativa')
ORDER BY nome;

-- 2) NOT IN e o oposto -- cuidado: um unico NULL na subquery zera o resultado
SELECT id, nome FROM cursos
WHERE id NOT IN (SELECT curso_id FROM matriculas);

-- 3) EXISTS: pergunta se existe ao menos uma linha, e para na primeira
SELECT c.id, c.nome FROM cursos c
WHERE EXISTS (
    SELECT 1 FROM matriculas m
    WHERE m.curso_id = c.id AND m.situacao = 'ativa'
)
ORDER BY c.nome;

-- 4) NOT EXISTS: os cursos que ninguem matriculou, sem risco com NULL
SELECT c.id, c.nome, c.valor FROM cursos c
WHERE NOT EXISTS (
    SELECT 1 FROM matriculas m WHERE m.curso_id = c.id
)
ORDER BY c.nome;

-- 5) subquery no WHERE com igualdade, para quando o resultado e um valor so
SELECT nome FROM cursos
WHERE id = (SELECT curso_id FROM matriculas ORDER BY id LIMIT 1);

-- 6) ANY e ALL comparam com cada valor devolvido
SELECT nome FROM cursos
WHERE valor > ALL (SELECT valor FROM cursos WHERE carga_horaria = 40);
SQL;

echo $sql . "\n";

Saída real

-- 1) IN: a subquery monta a lista e a consulta externa compara com ela
SELECT id, nome, carga_horaria FROM cursos
WHERE id IN (SELECT curso_id FROM matriculas WHERE situacao = 'ativa')
ORDER BY nome;

-- 2) NOT IN e o oposto -- cuidado: um unico NULL na subquery zera o resultado
SELECT id, nome FROM cursos
WHERE id NOT IN (SELECT curso_id FROM matriculas);

-- 3) EXISTS: pergunta se existe ao menos uma linha, e para na primeira
SELECT c.id, c.nome FROM cursos c
WHERE EXISTS (
    SELECT 1 FROM matriculas m
    WHERE m.curso_id = c.id AND m.situacao = 'ativa'
)
ORDER BY c.nome;

-- 4) NOT EXISTS: os cursos que ninguem matriculou, sem risco com NULL
SELECT c.id, c.nome, c.valor FROM cursos c
WHERE NOT EXISTS (
    SELECT 1 FROM matriculas m WHERE m.curso_id = c.id
)
ORDER BY c.nome;

-- 5) subquery no WHERE com igualdade, para quando o resultado e um valor so
SELECT nome FROM cursos
WHERE id = (SELECT curso_id FROM matriculas ORDER BY id LIMIT 1);

-- 6) ANY e ALL comparam com cada valor devolvido
SELECT nome FROM cursos
WHERE valor > ALL (SELECT valor FROM cursos WHERE carga_horaria = 40);

UNION

UNION empilha o resultado de duas consultas em uma lista só, como se fossem uma

tabela vertical. As duas precisam ter o mesmo número de colunas, e as colunas

se combinam pela posição, não pelo nome: o nome que aparece no resultado é o da

primeira consulta.

Empilhar dois resultados

SELECT <colunas> FROM <tabela_1>
UNION [ALL]
SELECT <colunas> FROM <tabela_2>
ORDER BY <coluna>;

UNION remove as linhas repetidas; UNION ALL guarda todas. A diferença

importa: UNION é a escolha certa quando as duas listas são o mesmo conjunto em

fontes diferentes, e UNION ALL quando elas são de fato diferentes — e é bem mais

rápido, porque não precisa comparar as linhas entre si.

CTE

WITH cria uma consulta intermediária nomeada, que vale só para a instrução

seguinte. Serve para quebrar uma consulta grande em partes legíveis, e para

filtrar antes do GROUP BY sem precisar repetir o filtro. A partir do MySQL 8.0

a CTE pode ser referenciada por outra, e a recursividade (WITH RECURSIVE)

percorre uma árvore de resultados a partir de uma âncora.

A consulta nomeada

WITH <nome> AS (SELECT <colunas> FROM <tabela> WHERE <condicao> GROUP BY <coluna>)
SELECT <colunas> FROM <tabela> <alias> LEFT JOIN <nome> ON <chave> = <chave>;

View

CREATE VIEW salva uma consulta com nome no banco, como se fosse uma tabela. A

view não guarda dados: ela guarda o SQL, e devolve o resultado da consulta sempre

que é lida.

Criar e usar

CREATE [OR REPLACE] VIEW <nome> AS SELECT <colunas> FROM <tabela> WHERE <condicao>;
SELECT * FROM <nome> [ORDER BY <coluna>] [LIMIT <n>];
DROP VIEW [IF EXISTS] <nome>;

View com SELECT * quebra quando a tabela muda de forma; escrever as colunas uma

a uma evita o problema. E view que faz agregação sem GROUP BY continua válida

depois de INSERT na tabela base, porque a consulta é reavaliada a cada leitura.

Exemplo

<?php
declare(strict_types=1);

// UNION empilha resultados; WITH nomeia uma consulta; VIEW salva a consulta
// no banco como se fosse tabela.

$sql = <<<'SQL'
-- 1) UNION: as duas consultas precisam do mesmo numero de colunas,
--    e a ordem define qual nome aparece no resultado
SELECT id, nome, carga_horaria AS referencia, 'curso' AS origem FROM cursos
UNION
SELECT id, nome, 0 AS referencia, 'aluno' AS origem FROM alunos
ORDER BY origem, nome;

-- 2) UNION ALL guarda as repetidas; UNION as remove
SELECT cidade AS texto, COUNT(*) AS qtd FROM alunos GROUP BY cidade
UNION ALL
SELECT estado AS texto, COUNT(*) AS qtd FROM cidades GROUP BY estado;

-- 3) WITH: a CTE vale so para a consulta seguinte e da um nome ao trecho
WITH ativas AS (
    SELECT curso_id, COUNT(*) AS qtd
    FROM matriculas
    WHERE situacao = 'ativa'
    GROUP BY curso_id
)
SELECT c.nome AS curso, COALESCE(a.qtd, 0) AS ativas
FROM cursos c
LEFT JOIN ativas a ON a.curso_id = c.id
ORDER BY ativas DESC, c.nome;

-- 4) WITH RECURSIVE percorre uma arvore a partir de uma ancora
WITH RECURSIVE arvore AS (
    SELECT id, nome, 0 AS nivel FROM cursos WHERE id = 1
    UNION ALL
    SELECT c.id, c.nome, a.nivel + 1
    FROM cursos c JOIN arvore a ON c.id = a.id
)
SELECT id, nome, nivel FROM arvore ORDER BY nivel, nome;

-- 5) CREATE VIEW: guarda o SQL, nao os dados
CREATE OR REPLACE VIEW vw_matriculas_ativas AS
SELECT m.id, a.nome AS aluno, c.nome AS curso, m.data_matricula
FROM matriculas m
INNER JOIN alunos a ON a.id = m.aluno_id
INNER JOIN cursos c ON c.id = m.curso_id
WHERE m.situacao = 'ativa';

-- 6) ler a view como se fosse tabela
SELECT * FROM vw_matriculas_ativas ORDER BY aluno LIMIT 10;
SHOW FULL TABLES WHERE Table_type = 'VIEW';
DROP VIEW IF EXISTS vw_matriculas_ativas;
SQL;

echo $sql . "\n";

Saída real

-- 1) UNION: as duas consultas precisam do mesmo numero de colunas,
--    e a ordem define qual nome aparece no resultado
SELECT id, nome, carga_horaria AS referencia, 'curso' AS origem FROM cursos
UNION
SELECT id, nome, 0 AS referencia, 'aluno' AS origem FROM alunos
ORDER BY origem, nome;

-- 2) UNION ALL guarda as repetidas; UNION as remove
SELECT cidade AS texto, COUNT(*) AS qtd FROM alunos GROUP BY cidade
UNION ALL
SELECT estado AS texto, COUNT(*) AS qtd FROM cidades GROUP BY estado;

-- 3) WITH: a CTE vale so para a consulta seguinte e da um nome ao trecho
WITH ativas AS (
    SELECT curso_id, COUNT(*) AS qtd
    FROM matriculas
    WHERE situacao = 'ativa'
    GROUP BY curso_id
)
SELECT c.nome AS curso, COALESCE(a.qtd, 0) AS ativas
FROM cursos c
LEFT JOIN ativas a ON a.curso_id = c.id
ORDER BY ativas DESC, c.nome;

-- 4) WITH RECURSIVE percorre uma arvore a partir de uma ancora
WITH RECURSIVE arvore AS (
    SELECT id, nome, 0 AS nivel FROM cursos WHERE id = 1
    UNION ALL
    SELECT c.id, c.nome, a.nivel + 1
    FROM cursos c JOIN arvore a ON c.id = a.id
)
SELECT id, nome, nivel FROM arvore ORDER BY nivel, nome;

-- 5) CREATE VIEW: guarda o SQL, nao os dados
CREATE OR REPLACE VIEW vw_matriculas_ativas AS
SELECT m.id, a.nome AS aluno, c.nome AS curso, m.data_matricula
FROM matriculas m
INNER JOIN alunos a ON a.id = m.aluno_id
INNER JOIN cursos c ON c.id = m.curso_id
WHERE m.situacao = 'ativa';

-- 6) ler a view como se fosse tabela
SELECT * FROM vw_matriculas_ativas ORDER BY aluno LIMIT 10;
SHOW FULL TABLES WHERE Table_type = 'VIEW';
DROP VIEW IF EXISTS vw_matriculas_ativas;