Subqueries no SQLite: SELECT aninhado em WHERE e FROM

Uma subquery é um SELECT dentro de outro SELECT

Uma subquery no SQLite é exatamente o que o nome sugere: um SELECT colocado dentro de outro comando, entre parênteses. O SQLite executa a consulta interna primeiro, pega o resultado e passa para a consulta externa.

Vamos montar um exemplo simples que vai servir de base pro resto:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders;

Cinco pedidos, quatro clientes — sendo que dois deles ainda não fizeram compra alguma. Vamos usar essa base nos próximos exemplos.

Subquery no WHERE: filtrando por uma lista

Esse é o formato mais comum de subquery no SQLite: a consulta interna devolve uma lista de ids e a externa filtra com base nela.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

A query interna devolve todo customer_id que aparece em orders. Já a query externa fica só com os clientes cujo id está nessa lista. Cleo, Boris e Ada aparecem; Dmitri (que não tem pedidos) fica de fora.

O padrão IN (SELECT ...) é o cavalo de batalha quando você quer "linhas de A que têm correspondência em B". Leia mentalmente como: "onde o valor dessa coluna é um dos valores retornados pela subquery".

NOT IN no SQLite: cuidado com os NULLs

A pergunta inversa — "quais clientes não fizeram pedidos?" — está a uma linha de distância:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Funciona bem aqui. Mas o NOT IN tem uma pegadinha clássica: se a subquery retornar um NULL que seja, o NOT IN inteiro vira NULL (que não é TRUE), e o resultado vem vazio. Surpresa silenciosa, daquelas chatas de debugar.

O hábito seguro ao usar NOT IN contra uma coluna que pode ter NULL:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Ou então use NOT EXISTS, que simplesmente não sofre desse problema. Já chegamos lá.

Subquery escalar: uma linha, uma coluna

Uma subquery escalar retorna um único valor — uma linha, uma coluna — e pode ser usada em qualquer lugar onde se espera um valor.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name, total
FROM orders
JOIN customers ON customers.id = orders.customer_id
WHERE total = (SELECT MAX(total) FROM orders);

O SELECT MAX(total) FROM orders interno retorna 200. Em seguida, a consulta externa filtra os pedidos que batem com esse valor. Esse padrão é útil sempre que você precisa comparar com um agregado.

Você também pode usar uma subquery escalar na lista do SELECT para anexar um valor calculado a cada linha:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT
    name,
    (SELECT COUNT(*) FROM orders WHERE customer_id = customers.id) AS order_count
FROM customers;

Cada linha de customers dispara a subquery interna uma vez, passando o customers.id como parâmetro. Isso é uma subquery correlacionada — já já falo mais sobre isso. Para casos do tipo "um número por linha" como esse, um LEFT JOIN com GROUP BY costuma ter desempenho melhor, mas a forma escalar fica linda de ler.

EXISTS no SQLite: só checar se existe alguma correspondência

O EXISTS é o primo mais discreto do IN. Ele não liga para os valores — só verifica se a subquery retorna qualquer linha. Por isso, é comum escrever SELECT 1 lá dentro, já que a coluna em si não importa.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.total > 100
);

Esse exemplo encontra clientes que fizeram pelo menos um pedido acima de 100. A query interna referencia c.id da query externa — é justamente isso que caracteriza uma subquery correlacionada. O SQLite para de varrer a tabela interna assim que encontra a primeira correspondência, e é por isso que o EXISTS costuma ter desempenho melhor que o IN quando a pergunta é "essa linha tem alguma linha relacionada?".

A versão negada, NOT EXISTS, é a forma segura contra NULL de perguntar "não existe linha relacionada":

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Subquery no FROM: a tabela derivada

Uma subquery pode aparecer em qualquer lugar onde uma tabela apareceria — inclusive na cláusula FROM. Nesse caso, o select aninhado vira uma "tabela derivada" temporária e nomeada, que você pode usar em JOIN, filtrar ou agregar como qualquer outra tabela.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT country, AVG(customer_total) AS avg_per_customer
FROM (
    SELECT c.country, c.id, SUM(o.total) AS customer_total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
) AS per_customer
GROUP BY country;

A consulta interna calcula o total por cliente. A externa tira a média desses totais por país. Agregações em dois estágios como essa são justamente o caso de uso das tabelas derivadas — quando não dá pra resolver tudo em um único GROUP BY.

O alias AS per_customer é obrigatório: toda tabela derivada precisa de um nome.

Subqueries correlacionadas: executadas a cada linha externa

Uma subquery é correlacionada quando faz referência a uma coluna da consulta externa. O SQLite precisa reavaliar a consulta interna para cada linha da externa, o que dá flexibilidade, mas pode sair caro.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT name,
       (SELECT MAX(total)
        FROM orders
        WHERE customer_id = customers.id) AS biggest_order
FROM customers;

Para cada cliente, queremos descobrir o maior pedido. Como a consulta interna depende de customers.id, ela é executada uma vez para cada cliente. Quem nunca fez pedido aparece com NULL — exatamente o comportamento esperado.

Subqueries correlacionadas caem como uma luva no padrão "para cada linha de A, calcule algo a partir de B". Se a tabela for pequena ou a busca estiver indexada, beleza. Mas em tabelas grandes sem índices que ajudem, faça profile antes de mandar pra produção — um JOIN com GROUP BY costuma ser mais rápido.

Subquery ou JOIN no SQLite: qual escolher?

As duas consultas a seguir respondem à mesma pergunta:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Subconsulta
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 100);

-- JOIN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.total > 100;

As duas formas devolvem as mesmas linhas. O otimizador do SQLite, inclusive, costuma reescrever uma na outra internamente. Escolha com base na legibilidade:

• Use uma subquery quando você só precisa filtrar e não quer que as colunas da tabela interna apareçam no resultado.
• Use um JOIN quando o resultado precisa de colunas das duas tabelas.
• Use EXISTS quando a pergunta é "existe pelo menos uma linha relacionada?" — fica mais claro e evita as armadilhas de NULL que aparecem com IN/NOT IN.

Na dúvida, escreva a versão que se explica sozinha quando você lê em voz alta.

Uma cilada comum: subquery retornando várias linhas

Uma subquery usada com = precisa retornar no máximo uma linha. Se ela devolver mais de uma, o SQLite escolhe uma qualquer (basicamente ao acaso) e você fica com um resultado silenciosamente errado — sem nenhum erro.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

-- Arriscado: e se houver mais de um cliente do Reino Unido?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

Use IN quando a subquery interna puder retornar várias linhas:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name, country) VALUES
    (1, 'Ada',   'UK'),
    (2, 'Boris', 'FR'),
    (3, 'Cleo',  'UK'),
    (4, 'Dmitri','DE');

INSERT INTO orders (customer_id, total) VALUES
    (1, 120), (1, 80), (2, 50), (3, 200), (3, 30);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'UK');

Se você espera que volte exatamente uma linha e quer garantir isso, adicione LIMIT 1 junto com um ORDER BY — assim a escolha pelo menos fica determinística. Melhor ainda: escreva a query de um jeito que os próprios dados garantam uma única linha (filtrando por uma coluna única, por exemplo).

Próximo passo: Common Table Expressions

Subqueries dentro do FROM viram uma bagunça rapidinho — principalmente quando você precisa da mesma tabela derivada duas vezes, ou quando o aninhamento começa a ter três níveis de profundidade. As Common Table Expressions (WITH ... AS (...)) permitem dar um nome à subquery logo no início e reaproveitá-la pelo nome no restante do comando. É esse o assunto da próxima página.