Dados · SQL

Por que o WHERE anula o LEFT JOIN?

Publicado em

Quatro pedidos de R$ 40, R$ 60, R$ 20 e R$ 50: quantos passam em amount > 30?

De um filtro sobre valores às duas versões da mesma consulta, em cinco passos:

  1. Filtrar as linhas de um JOIN com WHERE
  2. Seguir a linha NULL até o WHERE
  3. Manter cada cliente com a condição no ON
  4. Contar as linhas das duas versões
  5. Praticar

Quando o SQL aplica o WHERE de um JOIN?

O WHERE vem depois do JOIN e lê cada linha do resultado dele. Um pedido de R$ 12 sai do JOIN, e depois WHERE amount > 30 descarta a linha dele.

Vamos pegar três clientes e os pedidos deles.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
customers
idname
1Ana
2Pedro
3Júlia
orders
idcustomer_idamount
1011R$ 40
1021R$ 70
1032R$ 25
1042R$ 60
1053R$ 50
LEFT JOIN, antes do WHERE
nameamount
AnaR$ 40
AnaR$ 70
PedroR$ 25
PedroR$ 60
JúliaR$ 50
resultado
nameamount
AnaR$ 40
AnaR$ 70
PedroR$ 60
JúliaR$ 50
  1. Cada cliente aparece uma vez por pedido, como em qualquer JOIN.

    linhas

  2. Quais linhas passam em amount > 30? Toque nelas na tabela do LEFT JOIN.

    Você lê o valor de cada linha e compara com 30.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Ana
    2Pedro
    3Júlia
    orders
    idcustomer_idamount
    1011R$ 35
    1021R$ 65
    1032R$ 20
    1042R$ 45
    1053R$ 70

Por que o WHERE faz a linha NULL do LEFT JOIN sumir?

O cliente com id 7 não tem nenhum pedido: a linha dele traz amount NULL. NULL > 0 não é verdadeiro, então WHERE amount > 0 descarta a linha dele.

Vamos pegar três clientes, um deles sem pedido.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
customers
idname
1Ana
2Pedro
3Júlia
orders
idcustomer_idamount
1011R$ 40
1023R$ 60
LEFT JOIN, antes do WHERE
nameamount
AnaR$ 40
PedroNULL
JúliaR$ 60
resultado
nameamount
AnaR$ 40
JúliaR$ 60
  1. O LEFT JOIN mantém cada cliente, mesmo sem pedido.

    linhas

  2. Quais linhas passam em amount > 30?

    Você compara cada valor com 30. Na linha do Pedro, você compara NULL com 30.

  3. Sem WHERE, qual JOIN dá essas mesmas linhas?

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Ana
    2Pedro
    3Júlia
    4Lucas
    orders
    idcustomer_idamount
    1011R$ 45
    1023R$ 20
    1033R$ 65
    1044R$ 35

Como filtrar um LEFT JOIN sem perder clientes?

Escrita no ON, a condição escolhe os pedidos que vão ser ligados, e depois o LEFT JOIN mantém cada cliente. O cliente com id 7 só tem um pedido de R$ 12: AND amount > 30 recusa esse pedido, e o cliente aparece mesmo assim, com amount NULL.

Vamos pegar três clientes, com a condição no ON.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Ana
2Pedro
3Júlia
orders
idcustomer_idamount
1011R$ 40
1021R$ 60
1031R$ 25
1043R$ 20
resultado
nameamount
AnaR$ 40
AnaR$ 60
PedroNULL
JúliaNULL
  1. O que a coluna amount mostra na linha da Júlia?

    Primeiro você vê quais pedidos da Júlia passam em amount > 30.

  2. Cada cliente aparece uma vez por pedido ligado, e uma vez se não tiver nenhum.

    linhas

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
      AND orders.amount > 40
    customers
    idname
    1Ana
    2Pedro
    3Júlia
    4Lucas
    orders
    idcustomer_idamount
    1011R$ 50
    1021R$ 65
    1032R$ 15
    1042R$ 45
    1053R$ 20
    1064R$ 35

Qual a diferença entre a condição no ON e no WHERE?

Com 7 clientes, a condição escrita no ON dá pelo menos 7 linhas, pelo menos uma por cliente. Escrita no WHERE, ela só mantém as linhas em que é verdadeira, e pode não sobrar nenhuma.

Vamos pegar quatro clientes e as duas versões da consulta.

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Ana
2Pedro
3Júlia
4Lucas
orders
idcustomer_idamount
1011R$ 70
1021R$ 45
1031R$ 20
1042R$ 35
1054R$ 15
resultado com WHERE
nameamount
AnaR$ 70
AnaR$ 45
PedroR$ 35
resultado com ON
nameamount
AnaR$ 70
AnaR$ 45
PedroR$ 35
JúliaNULL
LucasNULL
  1. O WHERE relê cada linha do LEFT JOIN. A linha da Júlia traz amount NULL.

    linhas

  2. Cada cliente aparece uma vez por pedido que passa em amount > 30, e uma vez se não tiver nenhum.

    linhas

SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
WHERE orders.amount > 30
SELECT name, amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id
  AND orders.amount > 30
customers
idname
1Ana
2Pedro
3Júlia
4Lucas
orders
idcustomer_idamount
1011R$ 25
1021R$ 55
1033R$ 40
1043R$ 65
1053R$ 50
1064R$ 20

Como ler rápido um LEFT JOIN com filtro?

Primeiro você olha onde a condição está escrita. No WHERE, você conta os pedidos que passam nela. No ON, cada cliente aparece uma vez por pedido que passa nela, e uma vez se não tiver nenhum.

  1. SELECT name, amount
    FROM customers
    LEFT JOIN orders
      ON orders.customer_id = customers.id
    WHERE orders.amount > 40
    customers
    idname
    1Ana
    2Pedro
    3Júlia
    orders
    idcustomer_idamount
    1011R$ 35
    1023R$ 60
    1033R$ 15