Subconsultas y SELECT Avanzado

Las subconsultas permiten usar el resultado de una consulta dentro de otra.

Subconsulta Escalar (valor único)

-- Clientes que gastaron más que el promedio
SELECT nombre, apellido
FROM clientes
WHERE id IN (
    SELECT cliente_id
    FROM pedidos
    WHERE total > (SELECT AVG(total) FROM pedidos)
);

-- Productos más caros que el precio promedio
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos)
ORDER BY precio DESC;

Subconsulta en FROM (tabla derivada)

-- Ingresos mes a mes con crecimiento
SELECT
    mes,
    ingresos,
    LAG(ingresos) OVER (ORDER BY mes) AS mes_anterior,
    ingresos - LAG(ingresos) OVER (ORDER BY mes) AS crecimiento
FROM (
    SELECT
        DATE_FORMAT(creado_en, '%Y-%m') AS mes,
        SUM(total) AS ingresos
    FROM pedidos
    GROUP BY DATE_FORMAT(creado_en, '%Y-%m')
) AS ingresos_mensuales
ORDER BY mes;

EXISTS / NOT EXISTS

-- Clientes que han hecho al menos un pedido
SELECT nombre, apellido
FROM clientes c
WHERE EXISTS (
    SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id
);

-- Productos que nunca fueron pedidos
SELECT nombre, precio
FROM productos p
WHERE NOT EXISTS (
    SELECT 1 FROM items_pedido ip WHERE ip.producto_id = p.id
);

Expresiones de Tabla Comunes (CTE)

Los CTEs hacen las consultas complejas más legibles:

-- Mejores clientes con estadísticas de pedidos
WITH estadisticas_cliente AS (
    SELECT
        cliente_id,
        COUNT(*) AS cantidad_pedidos,
        SUM(total) AS valor_vida
    FROM pedidos
    GROUP BY cliente_id
),
niveles_cliente AS (
    SELECT
        c.id,
        c.nombre,
        c.apellido,
        ec.valor_vida,
        CASE
            WHEN ec.valor_vida >= 5000 THEN 'Oro'
            WHEN ec.valor_vida >= 1000 THEN 'Plata'
            ELSE 'Bronce'
        END AS nivel
    FROM clientes c
    JOIN estadisticas_cliente ec ON c.id = ec.cliente_id
)
SELECT * FROM niveles_cliente WHERE nivel = 'Oro' ORDER BY valor_vida DESC;

CTE Recursivo

-- Recorrer jerarquía organizacional
WITH RECURSIVE organigrama AS (
    -- Caso base: directivos de nivel superior
    SELECT id, nombre, gerente_id, 0 AS nivel, nombre AS ruta
    FROM empleados
    WHERE gerente_id IS NULL

    UNION ALL

    -- Caso recursivo: empleados con un gerente
    SELECT e.id, e.nombre, e.gerente_id, o.nivel + 1,
           CONCAT(o.ruta, ' > ', e.nombre)
    FROM empleados e
    JOIN organigrama o ON e.gerente_id = o.id
)
SELECT nivel, ruta FROM organigrama ORDER BY ruta;

Funciones de Ventana

Las funciones de ventana calculan un valor sobre un conjunto de filas relacionadas con la fila actual:

-- Número de fila por cliente (último pedido primero)
SELECT
    id,
    cliente_id,
    total,
    ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY creado_en DESC) AS num_fila
FROM pedidos;

-- Total acumulado de ingresos
SELECT
    DATE(creado_en) AS dia,
    SUM(total) AS ingreso_diario,
    SUM(SUM(total)) OVER (ORDER BY DATE(creado_en)) AS total_acumulado
FROM pedidos
GROUP BY DATE(creado_en);

-- Clasificar productos por precio dentro de cada categoría
SELECT
    nombre,
    categoria,
    precio,
    RANK() OVER (PARTITION BY categoria ORDER BY precio DESC) AS rango_precio
FROM productos;

Expresión CASE

-- Lógica condicional en consultas
SELECT
    nombre,
    precio,
    CASE
        WHEN precio < 50  THEN 'Económico'
        WHEN precio < 200 THEN 'Rango Medio'
        WHEN precio < 500 THEN 'Premium'
        ELSE 'Lujo'
    END AS nivel_precio,
    CASE WHEN stock = 0 THEN 'Sin Stock' ELSE 'En Stock' END AS disponibilidad
FROM productos;

Funciones de Cadena

SELECT
    CONCAT(nombre, ' ', apellido) AS nombre_completo,
    UPPER(email)              AS email_mayusculas,
    LENGTH(telefono)          AS longitud_telefono,
    TRIM('  hola  ')          AS recortado,
    REPLACE(telefono, '-', '') AS telefono_limpio
FROM clientes;

Funciones de Fecha

SELECT
    creado_en,
    DATE(creado_en)                       AS fecha_pedido,
    DATE_FORMAT(creado_en, '%Y-%m')       AS año_mes,
    DATEDIFF(NOW(), creado_en)            AS dias_atras,
    DATE_ADD(creado_en, INTERVAL 30 DAY)  AS fecha_vencimiento,
    YEAR(creado_en)                       AS año,
    MONTH(creado_en)                      AS mes,
    DAYOFWEEK(creado_en)                  AS dia_semana
FROM pedidos;