Condições extras no JOIN: ON ... AND ...

Você já sabe como juntar tabelas usando JOIN. Mas na vida real, só bater as chaves pode não ser suficiente. Muitas vezes rola a necessidade de juntar dados só se eles baterem em algum critério extra — tipo, só registros ativos, só dados do ano atual ou só pedidos finalizados.

É aí que entra a extensão do ON usando AND.

Condições extras no JOIN ... ON te deixam controlar exatamente quais linhas vão entrar na junção, antes mesmo do SQL começar a montar o resultado. Isso faz sua query ficar:

• Mais rápida (menos linhas passam pelo JOIN),
• Mais precisa (o filtro já rola na hora da junção),
• Mais previsível quando você usa LEFT JOIN (diferente do filtro no WHERE).

Exemplo: Só registros ativos em cursos

Imagina que a tabela enrollments tem o status da participação do estudante: active, dropped, pending.

Tabela students:

Atualizando a tabela enrollments:

Tabela courses:

Agora a gente quer pegar só os estudantes que têm cursos ativos:

SELECT 
    students.name AS student_name,
    courses.name AS course_name
FROM students 
INNER JOIN enrollments 
    ON students.id = enrollments.student_id
    AND enrollments.status = 'active'
INNER JOIN courses 
    ON enrollments.course_id = courses.id;

Resultado:

Aqui a gente colocou AND enrollments.status = 'active' dentro do ON, pra junção rolar só com os registros ativos, e não filtrar depois de juntar tudo.

Por que não WHERE?

Dava pra escrever assim também:

...
WHERE enrollments.status = 'active'

Mas isso tem outro comportamento com LEFT JOIN. O filtro no WHERE remove linhas que não têm correspondência (NULL), e aí o LEFT JOIN vira um INNER JOIN.

Já a condição AND enrollments.status = 'active' dentro do ON já limita as linhas que vão ser juntadas — controla o que entra na junção, não só filtra depois.

Esse jeito é importante principalmente quando você quer manter linhas de uma tabela mesmo se não tiver valor correspondente na outra (bem comum em relatórios e análises).

Mais exemplos de ON ... AND ...

Tabela students:

Tabela enrollments:

Tabela courses:

Exemplo: só cursos do ano atual

SELECT
    students.name,
    courses.name,
    enrollments.enrolled_at
FROM students
JOIN enrollments
    ON students.id = enrollments.student_id
    AND EXTRACT(YEAR FROM enrollments.enrolled_at) = EXTRACT(YEAR FROM CURRENT_DATE)
JOIN courses
    ON enrollments.course_id = courses.id;

Aqui a gente só junta os registros que são do ano atual.

Exemplo: excluindo por valor

JOIN enrollments
    ON students.id = enrollments.student_id
    AND enrollments.status != 'dropped'

Aqui a gente exclui estudantes que saíram do curso na hora da junção, não depois.

Quando a condição tá dentro do ON, o PostgreSQL pode otimizar o plano de junção e processar menos linhas. Isso faz muita diferença quando tem muito dado. Filtrar dentro é mais eficiente do que "cortar" depois do JOIN.

JOIN ON — não é só chave, não!

Muita gente acha que ON é só id = id. Mas na real, dá pra colocar:

• Operadores lógicos: AND, OR, NOT
• Comparações: >, <, <>, BETWEEN, IN
• Expressões: EXTRACT, DATE_TRUNC, COALESCE, NULLIF

Juntando tudo

Tabela students:

Tabela faculties:

Tabela courses:

Tabela enrollments:

SELECT
    s.name AS student_name,
    c.name AS course_name,
    f.name AS faculty_name
FROM students s
JOIN enrollments e
    ON s.id = e.student_id
    AND e.status = 'active'
JOIN courses c
    ON e.course_id = c.id
    AND c.name != 'PE'
JOIN faculties f
    ON c.faculty_id = f.id
    AND f.name IS NOT NULL;

Aqui a gente filtra ao mesmo tempo por:

• Registros ativos,
• Cursos, menos "PE",
• Faculdades que têm nome.

Resultado da query:

Espero que você tenha curtido essa aula. Você vai usar vários JOINs com filtros nas suas queries o tempo todo. Quase sempre :)