Clientes que nunca fizeram pedidos
A missão
Problema
Duas tabelas: customer(id, name) e orders(id, customer_id), em que orders.customer_id aponta para customer.id. Pedidos feitos sem cadastro são guardados com customer_id NULL. Escreva uma consulta (query) que retorne os nomes dos clientes que nunca fizeram um pedido, em uma única coluna name, ordenados por nome.
Exemplos
Input: customer = [(1, "Ana"), (2, "Ben"), (3, "Cy")]
orders = [(10, 1), (11, 1), (12, 3)]
Output: [("Ben",)]
Por quê: Ana tem dois pedidos e Cy tem um
Input: customer = [(1, "Ana"), (2, "Ben")]
orders = [(10, 1), (11, None)]
Output: [("Ben",)]
Por quê: o pedido 11 foi feito sem cadastro e não pertence a ninguém
Input: customer = [(1, "Ana"), (2, "Ben")]
orders = []
Output: [("Ana",), ("Ben",)]
Por quê: edge case: sem nenhum pedido, todo cliente se qualifica
Dicas
0 / 3
Um inner join só mantém os clientes que têm um pedido correspondente, que é o contrário do que se pede. Qual join mantém um cliente mesmo quando nada corresponde?
Um LEFT JOIN mantém todos os clientes e preenche as colunas do pedido com NULL quando não há correspondência. Esses NULLs são o sinal.
Faça LEFT JOIN orders ON orders.customer_id = customer.id e mantenha as linhas WHERE orders.id IS NULL. Cuidado com NOT IN sobre uma coluna que contém NULL: ele não retorna linha nenhuma.
Restrições
- guest orders have a NULL customer_id
Solução
Um LEFT JOIN mantém todos os clientes e combina cada um com os pedidos dele; um cliente sem pedido é combinado com uma única linha de NULLs. Testar uma coluna que nunca pode ser NULL em um pedido real, como o.id, separa exatamente esses clientes. Esse formato se chama anti-join, e NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) expressa a mesma coisa. O tentador WHERE id NOT IN (SELECT customer_id FROM orders) está errado aqui: o pedido sem cadastro coloca um NULL nessa lista, x NOT IN (..., NULL) nunca é verdadeiro, e a consulta não retorna nada. Com um índice em orders.customer_id, cada cliente custa uma busca no índice, O(n log m) para n clientes e m pedidos.
import sqlite3
QUERY = """
SELECT c.name
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name
"""
def run(customers, orders):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT)")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER)")
db.executemany("INSERT INTO customer VALUES (?, ?)", customers)
db.executemany("INSERT INTO orders VALUES (?, ?)", orders)
return db.execute(QUERY).fetchall()
print(run([(1, "Ana"), (2, "Ben"), (3, "Cy")], [(10, 1), (11, 1), (12, 3)])) # -> [('Ben',)]
print(run([(1, "Ana"), (2, "Ben")], [(10, 1), (11, None)])) # -> [('Ben',)]
print(run([(1, "Ana"), (2, "Ben")], [])) # -> [('Ana',), ('Ben',)]Travou na ideia, e não no código? JOINs INNER e OUTER explica isso.