En SQL, los operadores CROSS APPLY y OUTER APPLY se utilizan para invocar funciones con valores de tabla o expresiones de tabla a la derecha del operador. Funcionan de manera similar a las uniones, pero con algunas diferencias clave, especialmente en cómo manejan las filas que no producen resultados en la tabla derecha.

Ambos operadores implican dos pasos conceptuales:

1. Aplicación de la Expresión Derecha (A1): La expresión de la tabla derecha se evalúa para cada fila de la tabla o expresión de la tabla izquierda.
2. Inclusión de Filas Externas (A2): Este paso es específico de OUTER APPLY. Permite incluir filas de la tabla izquierda incluso si la evaluación de la expresión derecha no devuelve ninguna fila.

La diferencia principal radica en cómo manejan los resultados vacíos de la expresión derecha:

• CROSS APPLY: Si la evaluación de la expresión derecha para una fila de la tabla izquierda no produce resultados, esa fila de la tabla izquierda se descarta del resultado final. Es similar a una INNER JOIN.
• OUTER APPLY: Si la evaluación de la expresión derecha para una fila de la tabla izquierda no produce resultados, esa fila de la tabla izquierda se incluye en el resultado final. Las columnas correspondientes a la expresión derecha se rellenarán con NULL. Es similar a una LEFT JOIN.

Veamos un ejemplo práctico para ilustrar estas diferencias. Primero, crearemos dos tablas:

Tabla 1: Clientes

Customers (CustomerID, City)

CREATE TABLE Customers (
    CustomerID CHAR(5) NOT NULL PRIMARY KEY,
    City VARCHAR(10) NOT NULL
);

INSERT INTO Customers (CustomerID, City) VALUES
('FISSA', 'Madrid'),
('FRNDO', 'Madrid'),
('KRLOS', 'Madrid'),
('MRPHS', 'Zion');

Tabla 2: Pedidos

Orders (OrderID, CustomerID)

CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    CustomerID CHAR(5) NULL
);

INSERT INTO Orders (OrderID, CustomerID) VALUES
(1, 'FRNDO'),
(2, 'FRNDO'),
(3, 'KRLOS'),
(4, 'KRLOS'),
(5, 'KRLOS'),
(6, 'MRPHS'),
(7, NULL); -- Pedido sin cliente asignado

Ejemplo: Obtener los dos pedidos más recientes por cliente

Usando CROSS APPLY:

SELECT C.*, CA.*
FROM Customers AS C
CROSS APPLY (
    SELECT TOP 2 O.OrderID
    FROM Orders AS O
    WHERE C.CustomerID = O.CustomerID
    ORDER BY O.OrderID DESC
) AS CA;

Análisis del proceso con CROSS APPLY:

Para cada fila en Customers, se ejecuta la subconsulta (la expresión derecha). Si la subconsulta devuelve filas (es decir, si el cliente tiene pedidos), esas filas se combinan con la fila del cliente. Si la subconsulta no devuelve filas para un cliente específico (como si un cliente no tuviera pedidos), ese cliente no aparecerá en el resultado final.

Usando OUTER APPLY:

SELECT C.*, CA.*
FROM Customers AS C
OUTER APPLY (
    SELECT TOP 2 O.OrderID
    FROM Orders AS O
    WHERE C.CustomerID = O.CustomerID
    ORDER BY O.OrderID DESC
) AS CA;

Análisis del proceso con OUTER APPLY:

Al igual que CROSS APPLY, la subconsulta se ejecuta para cada fila de Customers. Sin embargo, si un cliente no tiene pedidos (o si la subconsulta no devuelve resultados por alguna otra razón), el cliente *aún se incluirá* en el resultado. Las columnas de la subconsulta (OrderID en este caso) se mostrarán como NULL para ese cliente. En nuestro ejemplo, esto significa que veremos los clientes que no tienen pedidos en la tabla Orders, con OrderID como NULL.

Consideremos también el pedido con CustomerID = NULL (OrderID 7). Este pedido no será asociado a ningún cliente mediante CROSS APPLY o OUTER APPLY porque la condición C.CustomerID = O.CustomerID no se cumplirá para ningún cliente válido (NULL no es igual a ningún valor, ni siquiera a otro NULL en una comparación directa).

Resumen de APPLY

CROSS APPLY es útil cuando desea combinar filas de la tabla izquierda con resultados de una función o subconsulta solo cuando esa función o subconsulta produce resultados. OUTER APPLY es más flexible, permitiendo ver todas las filas de la tabla izquierda, incluso si no hay datos coincidentes en la tabla derecha.

Explorando WITH CUBE, WITH ROLLUP y GROUPING

Estas cláusulas se utilizan con la sentencia GROUP BY para generar subtotalizaciones y totalizaciones de manera más avanzada.

• WITH CUBE: Genera todas las posiblse combinaciones de agregación para las columnas especificadas en la cláusula GROUP BY. Esto incluye totales por cada columna individual y combinaciones de todas ellas.
• WITH ROLLUP: Genera una jerarquía de agregación para las columnas especificadas. Crea subtotalse basados en el orden de las columnas en la cláusula GROUP BY.
• GROUPING(): Es una función que indica si una columna en la salida de WITH CUBE o WITH ROLLUP es un subtotal (1) o un valor real de datos (0).

Creemos una tabla de ejemplo:

Tabla: Información de Personas

PeopleInfo (ID, Name, Number, Phone, Score)

CREATE TABLE PeopleInfo (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(10),
    Number NVARCHAR(10) NOT NULL,
    Phone NVARCHAR(10),
    Score INT
);

INSERT INTO PeopleInfo (Name, Number, Phone, Score) VALUES
('Li Huan', '3223', '1365255', 80),
('Li Huan', '322123', '1', 90),
('Li Ming', '3213112352', '13152', 56),
('Li Ming', '32132312', '13342563', 60),
('Wang Hua', '3223', '1365255', 80);

Comparación: GROUP BY, WITH CUBE

Consideremos la suma de Score agrupada por Name y Number.

-- 1. Sin CUBE/ROLLUP
SELECT Name, Number, SUM(Score) AS TotalScore
FROM PeopleInfo
GROUP BY Name, Number;

-- 2. Con WITH CUBE
SELECT Name, Number, SUM(Score) AS TotalScore
FROM PeopleInfo
GROUP BY Name, Number WITH CUBE;

Análisis:

GROUP BY Name, Number solo mostrará los totales para combinaciones únicas de Name y Number. WITH CUBE, al agregar Name y Number, generará filas adicionales para totales por Name (NULL en la columna Number) y totales por Number (NULL en la columna Name), además de un gran total general (NULL en ambas columnas).

Comparación: WITH CUBE vs WITH ROLLUP

-- 3. Con WITH ROLLUP
SELECT Name, Number, SUM(Score) AS TotalScore
FROM PeopleInfo
GROUP BY Name, Number WITH ROLLUP;

Análisis:

WITH CUBE genera todas las combinaciones posibles. WITH ROLLUP genera una jerarquía. Si agrupamos por Name, Number WITH ROLLUP, obtendremos totales para cada Name (con Number como NULL) y luego un gran total general. No obtendremos totales para cada Number individualmente si no están asociados a un Name específico en la estructura jerárquica.

La posición de las columnas en GROUP BY es crucial para WITH ROLLUP:

-- ROLLUP con orden Name, Number
SELECT Name, Number, SUM(Score) AS TotalScore
FROM PeopleInfo
GROUP BY Name, Number WITH ROLLUP;

-- ROLLUP con orden Number, Name
SELECT Name, Number, SUM(Score) AS TotalScore
FROM PeopleInfo
GROUP BY Number, Name WITH ROLLUP;

-- ROLLUP con una columna adicional
SELECT Name, Number, Phone, SUM(Score) AS TotalScore
FROM PeopleInfo
GROUP BY Name, Number, Phone WITH ROLLUP;

El orden de las columnas en la cláusula GROUP BY define la jerarquía para WITH ROLLUP. La última columna especificada es la que se desglosa primero, y así sucesivamente hacia la izquierda.

Uso de la función GROUPING()

La función GROUPING() nos ayuda a identificar qué columnas representan totales o subtotales.

SELECT
    Name,
    Number,
    GROUPING(Name) AS NameGrouping,
    GROUPING(Number) AS NumberGrouping,
    SUM(Score) AS TotalScore
FROM PeopleInfo
GROUP BY Name, Number WITH CUBE; -- o WITH ROLLUP

Aálisis:

• Si GROUPING(ColumnName) devuelve 1, significa que esa columna es un subtotal (su valor es NULL en el resultado de la consulta).
• Si GROUPING(ColumnName) devuelve 0, significa que el valor de esa columna es un dato real de una fila.

Esto es muy útil para interpretar las filas de totales generadas por WITH CUBE y WITH ROLLUP.