Subconsultas en SQLite: SELECT anidados con WHERE y FROM

Una subconsulta es un SELECT dentro de otro SELECT

Una subconsulta en SQLite es justo lo que parece: una instrucción SELECT metida dentro de otra, encerrada entre paréntesis. SQLite ejecuta primero la consulta interna, toma su resultado y se lo pasa a la externa.

Vamos a montar un ejemplo pequeño que podamos reutilizar:

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, cuatro clientes, dos de los cuales no han pedido nada. Lo iremos usando durante todo el artículo.

Subconsulta en WHERE: filtrar contra una lista

La forma más típica de las subconsultas en SQLite: obtener una lista de ids en una consulta interna y luego filtrar la consulta externa contra esa lista.

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);

La consulta interna devuelve todos los customer_id que aparecen en orders. La consulta externa se queda solo con los clientes cuyo id está en esa lista. Aparecen Cleo, Boris y Ada; Dmitri (que no tiene pedidos) queda fuera.

IN (SELECT ...) es el patrón estrella para resolver "filas de A que tienen coincidencia en B". Léelo mentalmente como "donde el valor de esta columna es uno de los valores que devuelve la subconsulta".

NOT IN en SQLite: cuidado con los NULL

La pregunta opuesta — "¿qué clientes no han hecho pedidos?" — está a una línea de distancia:

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);

Esto funciona en este caso. Pero NOT IN tiene una trampa peligrosa: si la subconsulta llega a devolver un NULL, todo el NOT IN se evalúa como NULL (que no es TRUE) y te quedas con cero filas. Sorprendente y silencioso.

La costumbre segura al usar NOT IN contra una columna que pueda contener 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
);

O usa NOT EXISTS, que ni siquiera tiene este problema. Ya llegaremos a eso.

Subconsultas escalares en SQLite: una fila, una columna

Una subconsulta escalar devuelve un único valor (una fila y una columna), y la puedes usar en cualquier sitio donde se espere un 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);

El SELECT MAX(total) FROM orders interno devuelve 200. Después, la consulta externa filtra los pedidos que coinciden con ese valor. Resulta muy útil cuando necesitas comparar contra un valor agregado.

También puedes usar una subconsulta escalar dentro del SELECT para añadir un valor calculado a cada fila:

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 fila de customers ejecuta la consulta interna una vez, sustituyendo customers.id. Eso es lo que se conoce como subconsulta correlacionada — ya entraremos en detalle más abajo. Para casos del tipo "un número por fila" como este, un LEFT JOIN con GROUP BY suele rendir mejor, pero la forma escalar se lee de maravilla.

EXISTS: comprobar si hay alguna coincidencia

EXISTS es el primo discreto de IN. No le importan los valores: solo mira si la subconsulta devuelve alguna fila. Lo habitual es escribir SELECT 1 dentro, porque la columna da igual.

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
);

Esto encuentra los clientes que hayan hecho al menos un pedido superior a 100. La consulta interna hace referencia a c.id de la consulta externa: eso es justo lo que la convierte en una subconsulta correlacionada. SQLite deja de recorrer la tabla interna en cuanto encuentra una coincidencia, y por eso EXISTS suele rendir mejor que IN cuando lo que preguntas es "¿esta fila tiene una fila relacionada?".

Su negación, NOT EXISTS, es la forma segura frente a NULL de preguntar "ninguna fila 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
);

Subconsulta en FROM: la tabla derivada

Una subconsulta puede aparecer en cualquier lugar donde encaje una tabla, y eso incluye la cláusula FROM. La consulta interna se convierte en una "tabla derivada" temporal con nombre, sobre la que luego puedes hacer joins, filtrar o agregar.

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;

La consulta interna calcula el total por cliente. La consulta externa promedia esos totales por país. Este tipo de agregaciones en dos etapas es justamente para lo que sirven las tablas derivadas: cuando no puedes resolverlo todo con un único GROUP BY.

El alias AS per_customer es obligatorio: toda tabla derivada necesita un nombre.

Subconsultas correlacionadas: una ejecución por cada fila externa

Una subconsulta es correlacionada cuando hace referencia a una columna de la consulta externa. SQLite tiene que volver a evaluar la consulta interna para cada fila de la consulta externa, lo cual da mucha flexibilidad, pero puede salir caro en rendimiento.

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;

Por cada cliente, obtenemos el monto de su pedido más alto. La consulta interna depende de customers.id, así que se ejecuta una vez por cada cliente. Los clientes sin pedidos quedan con NULL, que es justo lo que queremos.

Las subconsultas correlacionadas en SQLite encajan de forma natural cuando la lógica es "para cada fila de A, calcula algo a partir de B". Si la tabla es pequeña o la búsqueda usa un índice, no hay problema. En tablas grandes sin índices que las apoyen, mide antes de pasar a producción: muchas veces un JOIN con GROUP BY resulta más rápido.

Subconsulta vs JOIN en SQLite: ¿cuál conviene?

Estas dos consultas responden a la misma pregunta:

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;

Ambas devuelven las mismas filas. De hecho, el optimizador de SQLite suele reescribir una forma como la otra de manera interna. La elección depende de la legibilidad:

• Usa una subconsulta cuando solo necesites filtrar y no quieras que las columnas de la tabla interna ensucien el resultado.
• Usa un JOIN cuando el resultado tenga que incluir columnas de ambas tablas.
• Usa EXISTS cuando la pregunta sea "¿existe al menos una fila relacionada?" — queda más claro y evita las trampas con NULL típicas de IN/NOT IN.

Ante la duda, escribe la versión que se explica sola al leerla en voz alta.

Un error frecuente: subconsultas que devuelven varias filas

Una subconsulta usada con = debe devolver como máximo una fila. Si devuelve más, SQLite elige una (prácticamente al azar) y obtienes resultados incorrectos sin que se lance ningún error.

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);

-- Riesgoso: ¿qué pasa si hay más de un cliente del Reino Unido?
SELECT * FROM orders
WHERE customer_id = (SELECT id FROM customers WHERE country = 'UK');

Usa IN cuando la subconsulta pueda devolver varias filas:

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');

Si esperas exactamente una fila y quieres forzarlo, añade LIMIT 1 junto con un ORDER BY para que, al menos, la elección sea determinista. Mejor todavía: escribe la consulta de modo que los propios datos garanticen una única fila (filtrando por una columna única).

Siguiente paso: Common Table Expressions

Las subconsultas en FROM se vuelven incómodas enseguida, sobre todo cuando necesitas la misma tabla derivada dos veces o cuando el anidamiento llega a tres niveles. Las Common Table Expressions (WITH ... AS (...)) te permiten ponerle nombre a una subconsulta al principio y referenciarla por ese nombre en el resto de la sentencia. De eso va la próxima página.