Introducción

Al trabajar con PostgreSQL en proyectos backend reales, rara vez consultarás una sola tabla. Los datos suelen normalizarse en varias tablas relacionadas entre sí mediante claves foráneas, y necesitas combinarlas para devolver información significativa. Esta lección se centra en tres técnicas importantes que te ayudarán a escribir consultas complejas de forma clara y eficiente: JOIN para conectar tablas, subquery para consultas anidadas y CTE para estructurar la lógica de consulta.

Usaremos un esquema de ejemplo que simula un sistema de ventas con cuatro tablas principales: customers, orders, products y order_items. La tabla customers almacena la información de los clientes, orders registra los pedidos de los clientes, products contiene el catálogo de productos y order_items es la tabla intermedia que guarda el detalle de cada producto en cada pedido. Este es el tipo de relación uno-a-muchos anidados que encontrarás con frecuencia.

JOINs entre múltiples tablas

JOIN es la forma más común de combinar datos de varias tablas en una misma consulta. PostgreSQL soporta INNER JOIN (solo obtiene los registros que coinciden en ambas tablas), LEFT JOIN (obtiene todos los registros de la tabla izquierda incluso cuando no hay coincidencia en la derecha) y otras variantes como RIGHT JOIN, FULL OUTER JOIN. En la práctica backend, usarás INNER JOIN y LEFT JOIN con mayor frecuencia.

El siguiente ejemplo obtiene la lista de pedidos con el nombre del cliente y el monto total:

SELECT o.id AS order_id, c.name AS customer_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 10;

Cuando necesites unir varias tablas a la vez, solo añade más cláusulas JOIN. Por ejemplo, para obtener el detalle de cada producto en un pedido incluyendo el nombre del producto y la cantidad:

SELECT o.id AS order_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 42;

Un error común es unir tablas olvidando la condición ON, lo que produce un producto cartesiano y hace que la consulta sea muy lenta. Verifica siempre que cada JOIN tenga una condición clara de coincidencia de claves.

Subconsultas y subconsultas correlacionadas

Una subconsulta es una consulta SELECT anidada dentro de otra consulta. Puedes usar una subconsulta en la cláusula WHERE para filtrar, en SELECT para calcular valores o en FROM para crear una tabla temporal. Una forma especial es la subconsulta correlacionada, en la que la consulta interna hace referencia a columnas de la consulta externa, lo que significa que se ejecuta de nuevo para cada fila del resultado.

Ejemplo: encontrar clientes que tengan al menos un pedido usando una subconsulta simple:

SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_amount > 500);

Ejemplo de subconsulta correlacionada que cuenta el número de pedidos para cada cliente mostrado en la lista:

SELECT c.name, c.email,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c
WHERE c.created_at > '2024-01-01';

La consulta interna hace referencia a c.id de la consulta externa, por lo que PostgreSQL debe ejecutarla de nuevo para cada cliente. Con tablas grandes, las subconsultas correlacionadas pueden ser lentas, así que considera convertirlas en JOIN o CTE si el rendimiento es un problema.

Common Table Expressions con WITH

CTE (Common Table Expression) te permite definir consultas temporales con nombre directamente en la sentencia, lo que hace que la lógica compleja sea más fácil de leer. Una CTE comienza con la palabra clave WITH, seguida de una lista de consultas nombradas y, finalmente, la consulta principal que las utiliza.

Ejemplo: calcular los ingresos totales por cada cliente en el año 2024 y luego filtrar aquellos con ingresos superiores a 1000:

WITH customer_revenue AS (
  SELECT o.customer_id, SUM(o.total_amount) AS revenue
  FROM orders o
  WHERE o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'
  GROUP BY o.customer_id
)
SELECT c.name, cr.revenue
FROM customer_revenue cr
INNER JOIN customers c ON c.id = cr.customer_id
WHERE cr.revenue > 1000
ORDER BY cr.revenue DESC;

Las CTE también soportan la forma recursiva (recursive CTE) con la palabra clave WITH RECURSIVE, muy útil para recorrer jerarquías como la estructura organizativa de una empresa o categorías de productos multinivel. Además, PostgreSQL 12 y posteriores permiten usar materialización con WITH ... AS MATERIALIZED para acelerar la consulta en ciertos casos.

Errores comunes y consejos prácticos

Un error frecuente es confundir JOIN con subconsulta en la cláusula WHERE usando IN o EXISTS. Ambos dan resultados equivalentes en muchas situaciones, pero EXISTS suele ser más rápido cuando solo necesitas comprobar la existencia en lugar de obtener valores. Ejemplo: encontrar clientes que hayan realizado pedidos:

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

Al escribir CTE, usa nombres significativos como customer_revenue o top_products para que el lector entienda fácilmente el propósito de cada paso. Evita abusar de las CTE cuando un JOIN simple sea suficiente, ya que las CTE pueden añadir coste de optimización extra en algunas versiones de PostgreSQL. Por último, revisa siempre el plan de ejecución con EXPLAIN ANALYZE cuando una consulta compleja vaya lenta, porque a veces una subconsulta correlacionada puede sustituirse por un JOIN para acelerar significativamente.

Resumen

En esta lección aprendiste a combinar múltiples tablas con JOIN, a escribir subconsultas y subconsultas correlacionadas para filtrar y calcular, y a usar CTE con WITH para estructurar consultas complejas en bloques legibles. Estas tres técnicas son la base para manejar la mayoría de los requisitos de consulta en aplicaciones backend reales, desde obtener listas de pedidos con sus detalles hasta calcular informes agregados. En la próxima lección, aprenderás más sobre indexación y cómo optimizar el rendimiento de las consultas complejas que acabas de escribir.