SQL avanzado para developers: la guía que te falta

El salto que separa a quien consulta de quien resuelve

La mayoría de los developers que dicen “no sé SQL” sí saben SQL: escriben SELECT, JOIN, GROUP BY y un WHERE. El salto real no es la sintaxis, es el modelo mental. La sintaxis se lookup; el modelo mental se practica.

Un GROUP BY no agrupa filas: agrupa filas y colapsa cada grupo a una sola. En cuanto eso hace clic, todo lo demás se deduce. Por eso esta guía arranca ahí y no por los SELECT.

GROUP BY: por qué “SELECT column” sin agrupar falla

Este query es el error más común de quien empieza:

SELECT department, name, salary
FROM employees;

El motor devuelve todas las filas, una por empleado. Si ahora quieres “un registro por departamento”, el motor te va a exigir que declares la intención:

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

La regla es mecánica: todo lo que NO está en GROUP BY ni dentro de una función de agregación (COUNT, SUM, AVG, MIN, MAX) debe aparecer en el GROUP BY. PostgreSQL hace cumplir esa regla:

SELECT department, name FROM employees GROUP BY department;
ERROR:  column "employees.name" must appear in the GROUP BY clause
        or be used in an aggregate function

Ese error es tu amigo. Otros motores de bases de datos devuelven un valor arbitrario del grupo sin avisar, y ahí sí producís datos incorrectos en silencio. PostgreSQL prefiere fallar.

La solución cuando quieres el detalle y el agregado juntos no es aflojar la regla: es una window function, que veremos más abajo.

Funciones de agregación: los cinco que usas todos los días

-- contar
SELECT COUNT(*) FROM orders;
SELECT COUNT(shipping_address) FROM orders;  -- ignora NULLs
SELECT COUNT(DISTINCT customer_id) FROM orders;
-- sumar y promediar
SELECT SUM(total) AS revenue, AVG(total) AS ticket_promedio
FROM orders
WHERE created_at >= '2026-01-01';
-- extremos
SELECT MIN(created_at) AS primer_pedido, MAX(created_at) AS ultimo_pedido
FROM orders;

La diferencia entre COUNT(*) y COUNT(columna) es la que más confunde: COUNT(*) cuenta filas, COUNT(columna) cuenta solo las no-NULL. En una tabla de 1000 pedidos con 40 sin dirección de envío, COUNT(*) da 1000 y COUNT(shipping_address) da 960.

HAVING: filtrar grupos, no filas

Este es el punto donde la mayoría se queda atascada. WHERE filtra filas antes de agrupar; HAVING filtra grupos después. No son intercambiables.

-- MAL: no funciona en PostgreSQL
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 50000
GROUP BY department;
-- BIEN:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

El orden de ejecución de un SELECT es fijo en PostgreSQL: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. WHERE ve filas; en ese punto la columna AVG(salary) todavía no existe. HAVING ve grupos ya formados.

Regla práctica: si el filtro menciona una columna sin agregar, va en WHERE. Si menciona el resultado de un AVG o COUNT, va en HAVING.

JOIN: por qué INNER JOIN pierde filas

Un INNER JOIN devuelve solo las filas que coinciden en ambas tablas. Si un pedido tiene customer_id NULL, ese pedido desaparece del resultado. Nadie ve el error: el query corre, devuelve datos, y simplemente están menos.

-- INNER: 480 pedidos de 500 (20 tienen customer_id NULL)
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- LEFT JOIN: los 500 pedidos, con name NULL en los huérfanos
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;

La diferencia entre 480 y 500 filas es exactamente el tipo de bug que sobrevive meses en producción porque el resultado “parece razonable”. Si alguna columna del lado derecho puede ser NULL, casi siempre quieres LEFT JOIN.

Window functions: el grupo que no colapsa

Acá está la herramienta que separa a alguien que consulta de alguien que resuelve. Un GROUP BY agrupa y colapsa: si quieres ver el detalle de cada empleado más el promedio de su departamento, el GROUP BY te obliga a elegir uno de los dos.

Una window function te da ambos sin colapsar nada:

SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_dept,
    salary - AVG(salary) OVER (PARTITION BY department) AS delta_vs_promedio,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS puesto_en_depto,
    LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS salario_anterior
FROM employees
ORDER BY department, puesto_en_depto;

La diferencia con GROUP BY es una línea: OVER (PARTITION BY ...). Sin ella, AVG(salary) colapsa a una fila por departamento. Con ella, calculas el promedio pero conservas una fila por empleado. Por eso se llaman “ventana”: calculan sobre un conjunto sin eliminarlo.

LAG y LEAD comparan cada fila con la anterior o la siguiente dentro del mismo grupo. Con eso puedes calcular variación mes a mes sin un self-join:

SELECT
    mes,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY mes) AS variacion,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY mes))
        / NULLIF(LAG(revenue) OVER (ORDER BY mes), 0),
        1
    ) AS variacion_pct
FROM monthly_revenue;

El NULLIF(..., 0) no es opcional si vas a dividir: en el primer mes LAG devuelve NULL, y si además el revenue anterior fuera 0 tendrías una división por cero. NULLIF convierte ese 0 en NULL y el resultado es NULL en vez de un error.

CTE: cuando el query deja de caber en la pantalla

Los subqueries anidados son el síntoma de un query que necesita partes con nombre. Una CTE (WITH) te permite definirlos aparte y referenciarlos por nombre:

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
hired_recent AS (
    SELECT id, name, department, salary, created_at
    FROM employees
    WHERE created_at >= '2026-01-01'
)
SELECT
    h.name,
    h.department,
    h.salary,
    d.avg_salary
FROM hired_recent h
JOIN dept_avg d ON h.department = d.department
WHERE h.salary > d.avg_salary;

La diferencia práctica: el subquery anidado se reescribe mentalmente cada vez que lo lees; la CTE se lee de arriba hacia abajo como una receta. En PostgreSQL 12+ las CTEs no materializan por defecto (el planner las integra como subqueries), así que usarlas no penaliza el rendimiento — solo la claridad.

El índice que casi siempre falta

Un WHERE sobre columna sin índice es un Seq Scan: lee la tabla entera. Verificalo siempre antes de asumir:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Si ves Seq Scan en orders con filtro por customer_id, ese es el índice que falta:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Vuelve a correr el EXPLAIN y deberías ver Index Scan o Bitmap Heap Scan. En tablas de menos de unos miles de filas PostgreSQL va a seguir eligiendo Seq Scan aunque el índice exista, porque leer la tabla entera es más barato que mantener el índice al día. Eso no es un bug: es el optimizador haciendo su trabajo.

Errores que PostgreSQL te avisa y que no puedes ignorar

-- division por cero
SELECT 1 / 0;
ERROR:  division by zero
-- division por cero que SÍ puedes evitar
SELECT 1 / NULLIF(0, 0);
        → NULL
-- not null violada
INSERT INTO employees (name) VALUES (NULL);
ERROR:  null value in column "name" violates not-null constraint

PostgreSQL aborta la transacción completa en un error. Eso significa que en un script con varios INSERT, un error a mitad de camino deja la base en un estado parcial. Si necesitas continuar, envuelve cada bloque crítico en su propia transacción.

Checklist antes de dar por terminado un query

Antes de pasar a la siguiente tarea, revisa estas seis cosas. Casi todos los bugs de SQL que se descubren en producción son alguno de estos seis:

• ¿Estou filtrando filas o grupos? Si el filtro menciona un AVG o COUNT, va en HAVING, no en WHERE.
• ¿Estou perdiendo filas en un JOIN? Si alguna columna del lado derecho puede ser NULL, probablemente quieres LEFT JOIN.
• ¿Las filas son las que creo? Corre el COUNT(*) del query con y sin el filtro y compara. Si el número baja más de lo esperado, algo se está perdiendo.
• ¿Estoy dividiendo por algo que puede ser cero? Todo / necesita un NULLIF en el denominador.
• ¿Estoy usando una columna sin agregar fuera del GROUP BY? PostgreSQL lo rechaza con error. Si el query corre en otro motor y aquí falla, ese motor te está devolviendo basura silenciosamente.
• ¿El plan usa los índices que creo? EXPLAIN ANALYZE es la única fuente de verdad sobre lo que el optimizador realmente hizo.

Preguntas frecuentes

Lee también

• Cómo instalar PostgreSQL 17 en Ubuntu 24.04: guía paso a paso
• Crecimiento sostenido en PostgreSQL: qué miden los datos de Vondra
• PostgreSQL 19: Reflexiones sobre las características retiradas