Combinación de Conjuntos - Usando Subconsultas, Expresiones de Tabla y el Operador APPLY

SQL proporciona un número de formas diferentes de combinar datos de varias tablas. Este post cubre las subconsultas, las expresiones de tabla y el operador APPLY.

Antes de ejecutar las consultas en este post, añada un nuevo proveedor a la tabla Production.Suppliers ejecutando el siguiente código (si es que no lo agregado en un post anterior).

USE TSQL2012;

INSERT INTO Production.Suppliers

(companyname,contactname,contacttitle,address,city,postalcode,country,phone)

VALUES(N'Supplier XYZ',N'Jiru',N'Head of Security',N'42 Sekimai Musashino-shi', N'Tokyo', N'01759', N'Japan', N'(02) 4311-2609');

Este proveedor no tiene ningún producto relacionado en la tabla Production.Products y es utilizado en los ejemplos demostrando las no coincidencias.

T-SQL soporta anidación de consultas. Esta es una parte conveniente del lenguaje que puede utilizar para referirse a un resultado de otra consulta. No necesita almacenar el resultado de una consulta en una variable con el fin de poder referirse a ese resultado desde otra consulta. Este post cubre los diferentes tipos de subconsultas. Este post también cubre el uso de las expresiones de tabla, las cuales son consultas llamadas. Por último, este post también cubre el operador de tabla APPLY.

Subconsultas

Las subconsultas pueden ser autónomas, es decir, independientes de la consulta externa; o pueden ser correlacionadas, es decir, tener una referencia a una columna de la tabla en la consulta externa. En términos del resultado de la subconsulta, puede ser escalar, de varios valores, o de valores de tabla.

Esta post comienza cubriendo las subconsultas autónomas más simples y luego continúa con subconsultas correlacionadas.

Subconsultas Autónomas

Las subconsultas autónomas son subconsultas que no tienen dependencia en la consulta externa. Si lo desea, puede resaltar la consulta interna y ejecutarla independientemente. Esto hace la resolución de problemas con subconsultas autónomas más fáciles en comparación con las subconsultas correlacionadas.

Como se ha mencionado, una subconsulta puede retornar diferentes formas de resultados. Puede retornar un solo valor, varios valores, o incluso un resultado de tabla completo. Las subconsultas de valores de tabla, o expresiones de tabla, son discutidas más adelante en este post.

Las subconsultas que retornan un solo valor, o subconsultas escalares, pueden ser utilizadas donde una expresión de un solo valor es esperado, como en un lado de una comparación. Por ejemplo, la siguiente consulta utiliza una subconsulta autónoma para retornar los productos con el precio unitario mínimo.

SELECT productid, productname, unitprice

FROM Production.Products

WHERE unitprice =

       (SELECT MIN(unitprice)

        FROM Production.Products);

Aquí está la salida de esta consulta.

productid  productname    unitprice

---------- -------------- ----------

33          Product ASTMN  2.50

Como puede ver, la subconsulta retorna el precio unitario mínimo de la tabla Production.Products. La consulta externa entonces retorna información sobre productos con el precio unitario mínimo. Trate de resaltar sólo la consulta interna y ejecútela, y encontrará que esto es posible.

Note que si ha supuesto que una subconsulta escalar retorna en la práctica más de un valor, el código falla en tiempo de ejecución. Si la subconsulta escalar retorna un conjunto vacío, es convertido a un NULL.

Una subconsulta también puede retornar varios valores en la forma de una sola columna. Tal como una subconsulta puede ser utilizada donde un resultado de varios valores es esperado; por ejemplo, cuando utiliza el predicado IN. A modo de ejemplo, la siguiente consulta utiliza una subconsulta de varios valores para retornar los productos suministrados por proveedores de Japón.

SELECT productid, productname, unitprice

FROM Production.Products

WHERE supplierid IN

       (SELECT supplierid 

        FROM Production.Suppliers

        WHERE country = N'Japan');

Esta consulta genera el siguiente resultado.

productid  productname    unitprice

---------- -------------- ----------

9           Product AOZBW  97.00

10          Product YHXGE  31.00

74          Product BKAZJ  10.00

13          Product POXFU  6.00

14          Product PWCJB  23.25

15          Product KSZOI  15.50

La consulta interna retorna supplierid de proveedores de Japón. La consulta externa entonces retorna información acerca de productos cuyo supplierid está en el conjunto retornado por la subconsulta. Al igual que con predicados en general, puede negar un predicado IN, así que si quiere retornar productos suministrados por proveedores que no son de Japón, simplemente cambie el IN a NOT IN.

Subconsultas Correlacionadas

Las subconsultas correlacionadas son subconsultas donde la consulta interna tiene una referencia a una columna de la tabla en la consulta externa. Son más difíciles de trabajar en comparación a subconsultas autónomas porque no pueden simplemente resaltar la porción interna y ejecutarla independientemente.

A modo de ejemplo, suponga que necesita retornar productos con el precio unitario mínimo por categoría. Puede utilizar una subconsulta correlacionada para retornar el precio unitario mínimo de los productos donde la categoryid es igual a una en la fila externa (la correlación), de la siguiente manera.

SELECT categoryid, productid, productname, unitprice

FROM Production.Products AS P1

WHERE unitprice = 

       (SELECT MIN(unitprice)  

        FROM Production.Products AS P2

        WHERE P2.categoryid = P1.categoryid);

Esta consulta genera el siguiente resultado.

categoryid  productid  productname    unitprice

----------- ---------- -------------- ----------

1            24         Product QOGNU   4.50

2            3          Product IMEHJ   10.00

3            19         Product XKXDO   9.20

4            33         Product ASTMN   2.50

5            52         Product QSRXF   7.00

6            54         Product QAQRL   7.45

7            74         Product BKAZJ   10.00

8            13         Product POXFU   6.00

Note que la consulta externa y la consulta interna se refieren a diferentes instancias de la misma tabla, Production.Products. Para que la subconsulta pueda distinguir entre las dos, debe asignar diferentes alias a las diferentes instancias. La consulta asigna el alias P1 a la instancia externa y P2 a la instancia interna, y por utilizar el alias de la tabla como un prefijo, puede referirse a las columnas de una manera inequívoca. La subconsulta utiliza una correlación en el predicado P2.categoryid = P1.categoryid, lo que significa que filtra sólo los productos donde la categoryid es igual a una en la fila externa. Así que cuando la fila externa tiene categoryid de 1, la consulta interna retorna el precio unitario mínimo de todos los productos donde la categoryid es 1; cuando la fila exterior tiene categoryid de 2, la consulta interna retorna el precio unitario mínimo de todos los productos donde la categoryid es 2; y así sucesivamente.

Como otro ejemplo de una subconsulta correlacionada, la siguiente consulta retorna los clientes que han realizado pedidos el 12 de febrero del 2007.

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE EXISTS

  (SELECT *

   FROM Sales.Orders AS O

   WHERE O.custid = C.custid

    AND O.orderdate = '20070212');

Esta consulta genera el siguiente resultado.

custid   companyname

------- ---------------

5        Customer HGVLZ

66       Customer LHANT

El predicado EXISTS acepta una subconsulta como entrada y retorna Verdadero cuando la subconsulta retorna al menos una fila y Falso en caso contrario. En este caso, la subconsulta retorna los pedidos realizados por el cliente cuyo ID es igual al custid en la fila externa (la correlación) y donde la fecha de pedido es 12 de febrero del 2007. Por lo tanto la consulta externa retorna un cliente sólo si hay al menos un pedido realizado por ese cliente en la fecha en cuestión.

Como un predicado, EXISTS no necesita retornar el conjunto de resultados de la subconsulta; más bien, retorna sólo Verdadero o Falso, dependiendo de si la subconsulta retorna alguna fila. Por esta razón, el SQL Server Query Optimizer ignora la lista SELECT de la subconsulta, y por lo tanto, lo que sea que especifique no afectará las opciones de optimización como la selección de índice.

Así con otros predicados, puede negar el predicado EXISTS también. La siguiente consulta niega el predicado de la consulta anterior, retornando los clientes que no colocan pedidos el 12 de febrero del 2007.

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE NOT EXISTS

  (SELECT *

   FROM Sales.Orders AS O

   WHERE O.custid = C.custid

     AND O.orderdate = '20070212');

Esta consulta genera el siguiente resultado, mostrado aquí en forma abreviada.

custid  companyname

------- ---------------

72       Customer AHPOP

58       Customer AHXHT

25       Customer AZJED

18       Customer BSVAR

91       Customer CCFIZ

...

Expresiones de Tabla

Las expresiones de tabla son llamadas consultas. Escribe una consulta interna que retorna un conjunto de resultado relacional, nómbrelo, y consúltelo desde una consulta externa. T-SQL soporta cuatro formas de expresiones de tabla:

·         Tablas derivadas

·         Expresiones de tabla común (CTEs)

·         Vistas

·         Funciones con valores de tabla InLine

Las dos primeras son visibles sólo a la sentencia que los define. En cuanto a las dos últimas, se preserva la definición de la expresión de tabla en la base de datos como un objeto; por lo tanto, es reusable, y también puede controlar el acceso al objeto con permisos.

Note que debido a que una expresión de tabla se supone que representa una relación, la consulta interna que la define necesita ser relacional. Esto significa que todas las columnas retornadas por la consulta interna deben tener nombres (utilizar alias si la columna es el resultado de una expresión), y todos los nombres de columna deben ser únicos. Además, a la consulta interna no le es permitida tener una cláusula ORDER BY. (Recuerde, un conjunto no tiene orden). Hay una excepción a la última regla: si utiliza la opción TOP u OFFSET-FETCH en la consulta interna, el ORDER BY tiene un significado que no está relacionado al orden de presentación; más bien, es parte de la especificación del filtro. Así que si la consulta interna utiliza la opción TOP u OFFSET-FETCH, es permitido tener una cláusula ORDER BY también. Pero entonces la consulta externa no tiene ningún orden de presentación garantizado si no tiene su propia cláusula ORDER BY.

Optimización de Expresiones de Tabla

Es importante notar que, desde el punto de vista de rendimiento, cuando SQL Server optimiza las consultas que involucran expresiones de tabla, primero desanida la lógica de expresión de la tabla, y por lo tanto interactúa con las tablas originarias directamente. De alguna manera no persiste el resultado de la expresión de tabla en una tabla de trabajo interna y luego interactúa con esta tabla de trabajo. Esto significa que las expresiones de tabla no tienen un rendimiento asociado, ni bueno ni malo, solo no tiene lado.

Ahora que entiende los requisitos de la consulta interna, está listo para aprender acerca de las diferentes formas de expresiones de tabla que soporta T-SQL.

Tablas Derivadas

Una tabla derivada es probablemente la forma de expresión de tabla que más se parece a una subconsulta, es sólo una subconsulta que retorna un resultado de tabla completa. Define la consulta interna de la tabla derivada en paréntesis en la cláusula FROM de la consulta externa, y especifica el nombre de la tabla derivada después del paréntesis.

Antes de demostrar el uso de las tablas derivadas, esta sección describe una consulta que retorna un cierto resultado deseado. Luego se explica una necesidad que no puede ser abordada directamente en la consulta, y muestra cómo se puede hacer frente a esa necesidad utilizando una tabla derivada (o cualquier otro tipo de expresión de tabla para ese caso).

Considere la siguiente consulta, la cual calcula números de fila para productos, particionados por categoryid, y ordenados por unitprice y productid.

SELECT

  ROW_NUMBER() OVER(PARTITION BY categoryid

                   ORDER BY unitprice, productid) AS rownum,

  categoryid, productid, productname, unitprice

FROM Production.Products;

Esta consulta genera el siguiente resultado, que se muestra aquí en forma abreviada.

rownum  categoryid  productid  productname    unitprice

------- ----------- ---------- -------------- ----------

1        1           24         Product QOGNU  4.50

2        1           75         Product BWRLG  7.75

3        1           34         Product SWNJY  14.00

4        1           67         Product XLXQF  14.00

5        1           70         Product TOONT  15.00

...

1        2            3          Product IMEHJ  10.00

2        2           77         Product LUNZZ  13.00

3        2           15         Product KSZOI  15.50

4        2           66         Product LQMGN  17.00

5        2           44         Product VJIEO  19.45

...

Por ahora, basta decir que la función ROW_NUMBER calcula enteros secuenciales únicos, desde 1 y basados en el ordenamiento indicado, posiblemente con particiones de filas. Como puede ver en el resultado de la consulta, la función ROW_NUMBER genera enteros secuenciales únicos desde 1 y basados en el ordenamiento sobre unitprice y productid, dentro de cada partición definida por categoryid.

El asunto con la función ROW NUMBER, y funciones de ventana en general, es que sólo son permitidas en las cláusulas  SELECT y ORDER BY de una consulta. De esta manera, ¿Si desea filtrar filas basadas en tales resultados de la función? Por ejemplo, suponga que desea retornar sólo las filas donde el número de fila es menor o igual a 2; es decir, en cada categoría, desea retornar los dos productos con los menores precios unitarios, con el productid utilizado como desempate. No se permite referirse a la función ROW_NUMBER en la cláusula WHERE de la consulta. Recuerde también que de acuerdo al procesamiento de consulta lógico, no se permite referirse a un alias de columna que fue asignado en la lista SELECT en la cláusula WHERE, porque la cláusula WHERE es conceptualmente evaluada antes que la cláusula SELECT.

Puede eludir la restricción utilizando una expresión de tabla. Escriba una consulta tal como la consulta previa que calcula la función de ventana en la cláusula SELECT, y asigna un alias de columna a la columna de resultado. Entonces defina una expresión de tabla basado en esa consulta, y refiérase al alias de columna en la cláusula WHERE de la consulta externa, de la siguiente manera.

SELECT categoryid, productid, productname, unitprice

FROM (SELECT

       ROW_NUMBER() OVER(PARTITION BY categoryid

                    ORDER BY unitprice, productid) AS rownum,

       categoryid, productid, productname, unitprice

     FROM Production.Products) AS D

WHERE rownum <= 2;

Esta consulta genera el siguiente resultado, que se muestra aquí en forma abreviada.

categoryid  productid  productname    unitprice

----------- ---------- -------------- ----------

1            24         Product QOGNU   4.50

1            75         Product BWRLG   7.75

2            3          Product IMEHJ   10.00

2            77         Product LUNZZ   13.00

3            19         Product XKXDO   9.20

3            47         Product EZZPR   9.50

...

Como puede ver, la tabla derivada es definida en la cláusula FROM de la consulta externa entre paréntesis, seguido por el nombre de la tabla derivada. Entonces la consulta externa es permitida para referirse a los alias de columna que fueron asignados por la consulta interna. Este es un uso clásico de las expresiones de tabla.

Dos opciones de alias de columna son disponibles cuando trabaja con tablas derivadas: InLine y Externa. Con la forma InLine, especifica el alias de columna como parte de la expresión, como en <expresión> AS alias. La última consulta utilizó la forma inline para asignar el alias rownum a la expresión con la función ROW_NUMBER. Con la forma de alias externo, no especifica el resultado de alias de columna como parte de las expresiones de columna; en cambio, nombra todas las columnas de destino justo después del nombre de la tabla derivada, como en FROM (...) AS D (rownum, categoryid, productid, productname, unitprice). Con la forma externa, debe especificar todos los nombres de columnas destino y no sólo los que son resultados de los cálculos.

Hay un par de aspectos problemáticos para trabajar con tablas derivadas que parten del hecho de que una tabla derivada es definida en la cláusula FROM de la consulta externa. Un problema que tiene que ver con los casos donde desea referirse a una tabla derivada de otra. En tal caso, termina anidando tablas derivadas, y el anidamiento a menudo complica la lógica, haciéndola difícil de seguir y aumentando la probabilidad de errores. Considere la siguiente forma general de anidamiento de tablas derivadas.

SELECT ...

FROM (SELECT

      FROM (SELECT ...

FROM T1

WHERE ...) AS D1

WHERE ...) AS D2

WHERE ...;

El otro problema con las tablas derivadas tiene que ver con la propiedad "todo-en-una vez" del lenguaje. Recuerde que todas las expresiones que aparecen en la misma fase de procesamiento de consulta lógico son conceptualmente evaluadas en el mismo punto en el tiempo. Esto es cierto incluso para las expresiones de tabla. Como resultado, el nombre asignado a una tabla derivada no es visible para otros elementos que aparecen en la misma fase de procesamiento de consulta lógica en donde el nombre de la tabla derivada fue definido. Esto significa que si quiere unir varias instancias de la misma tabla derivada, no puede. No tiene otra opción más que duplicar el código, definiendo múltiples tablas derivadas basadas en la misma consulta. La forma general de una consulta se parece a la siguiente.

SELECT ...

FROM (SELECT ...

      FROM T1) AS D1

  INNER JOIN

     (SELECT ...

      FROM T1) AS D2

   ON ...;

Las tablas derivadas D1 y D2 son basadas en la misma consulta. Esta repetición de código aumenta la probabilidad de errores cuando necesita hacer revisiones a las consultas internas.

CTEs

Una expresión de tabla común (CTE) es un concepto similar a una tabla derivada en el sentido de que es una expresión de tabla nombrada que es visible sólo a la sentencia que la define. Al igual que una consulta sobre una tabla derivada, una consulta sobre una CTE involucra tres partes principales:

·         La consulta interna

·         El nombre que asigna a la consulta y a sus columnas

·         La consulta externa

Sin embargo, con los CTEs, la disposición de las tres partes es diferente. Recordemos que con tablas derivadas la consulta interna aparece en la cláusula FROM de la consulta externa, en el medio de todo. Con las CTEs, primero nombra la CTE, luego, especifica la consulta interna, y luego la consulta externa, es un enfoque mucho más modular.

WITH <nombre_CTE>

AS

(

  <consulta_interna>

)

<consulta_externa>;

Recordemos el ejemplo de la sección sobre tablas derivadas donde retornó para cada categoría de producto los dos productos con los precios unitarios más bajos. He aquí cómo puede implementar la misma tarea con un CTE.

WITH C AS

(

  SELECT

    ROW_NUMBER() OVER(PARTITION BY categoryid

                     ORDER BY unitprice, productid) AS rownum,

    categoryid, productid, productname, unitprice

  FROM Production.Products

)

SELECT categoryid, productid, productname, unitprice

FROM C

WHERE rownum <= 2;

Como puede ver, es un concepto similar a las tablas derivadas, excepto la consulta interna no está definida en el medio de la consulta externa; en cambio, primero define la consulta interna, de principio a fin, luego la consulta externa, de principio a fin. Este diseño conduce a código mucho más claro que es más fácil de entender.

No anida los CTEs como lo hace en las tablas derivadas. Si necesita definir varios CTEs, simplemente los separa con comas. Cada uno puede referirse a los CTEs previamente definidos, y la consulta externa puede referirse a todos ellos. Después de que la consulta externa termina, todos los CTEs definidos en esa sentencia WITH se han ido. El hecho de que no anida los CTEs hace que sea más fácil seguir la lógica y por lo tanto reduce las posibilidades de error. Por ejemplo, si desea referirse a un CTE desde otro, puede utilizar la siguiente forma general.

WITH C1 AS

(

  SELECT ...

  FROM T1

  WHERE ...

),

C2 AS

(

  SELECT

  FROM C1

  WHERE ...

)

SELECT ...

FROM C2

WHERE ...;

Debido a que el nombre del CTE es asignado antes del inicio de la consulta externa, puede referirse a varias instancias del mismo nombre de CTE, a diferencia de las tablas derivadas. La forma general se parece a la siguiente.

WITH C AS

(

  SELECT ...

  FROM T1

)

SELECT ...

FROM C AS C1

  INNER JOIN C AS C2

    ON ...;

Los CTEs también tienen una forma recursiva. El cuerpo de la consulta recursiva tiene dos o más consultas, usualmente separadas por un operador UNION ALL. Al menos una de las consultas en el cuerpo CTE, conocida como el miembro anclado, es una consulta que retorna un resultado relacional válido. La consulta de anclaje es invocada sólo una vez. Además, al menos una de las consultas en el cuerpo CTE, conocido como el miembro recursivo, tiene una referencia al nombre del CTE. Esta consulta es invocada repetidamente hasta que se retorna un conjunto de resultado vacío. En cada iteración, la referencia al nombre del CTE del miembro recursivo representa el conjunto de resultado anterior. Entonces la referencia al nombre de CTE de la consulta externa representa los resultados unificados de la invocación del miembro anclado y todas las invocaciones del miembro recursivo.

A modo de ejemplo, el código siguiente utiliza una CTE recursiva para retornar la cadena de gestión conduciendo todo el camino hasta el CEO para un empleado especificado.

WITH EmpsCTE AS

(

  SELECT empid, mgrid, firstname, lastname, 0 AS distance

  FROM HR.Employees

  WHERE empid = 9

  UNION ALL

  SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance

  FROM EmpsCTE AS S

    JOIN HR.Employees AS M

      ON S.mgrid = M.empid

)

SELECT empid, mgrid, firstname, lastname, distance

FROM EmpsCTE;

Este código retorna la siguiente salida.

empid       mgrid       firstname  lastname             distance

----------- ----------- ---------- -------------------- -----------

9            5           Zoya       Dolgopyatova            0

5            2           Sven       Buck                    1

2            1           Don        Funk                    2

1            NULL        Sara       Davis                   3

Como puede ver, el miembro anclado retorna la fila para el empleado 9. Entonces, el miembro recursivo es invocado repetidamente, y en cada ronda combina el conjunto resultado anterior con la tabla HR.Employees para retornar el jefe directo del empleado de la ronda previa. La consulta recursiva se detiene tan pronto como retorna un conjunto vacío, en este caso, después de no encontrar un jefe del CEO. Entonces la consulta externa retorna los resultados unificados de la invocación del miembro anclado (la fila para el empleado 9) y todas las invocaciones del miembro recursivo (todos los jefes superiores al empleado 9).

Vistas y Funciones de Valores de Tabla InLine

Como aprendió en las secciones previas, las tablas derivadas y las CTEs son expresiones de tabla que son visibles sólo en el ámbito de la sentencia que las define. Después que esa sentencia termina, la expresión de tabla se ha ido. Por lo tanto, las tablas derivadas y las CTEs no son reutilizables. Para la reutilización, necesita almacenar la definición de la expresión de tabla como un objeto en la base de datos, y para esto puede utilizar vistas o funciones con valores de tabla InLine. Debido a que estos son objetos en la base de datos, puede controlar el acceso utilizando permisos.

La principal diferencia entre las vistas y las funciones con valores de tabla InLine es que el primero no acepta parámetros de entrada y el segundo si lo hace. A modo de ejemplo, suponga que necesita persistir la definición de la consulta con el cálculo del número de fila de los ejemplos en las secciones previas. Para lograr esto, se crea la siguiente vista.

IF OBJECT_ID('Sales.RankedProducts','V') IS NOT NULL DROP VIEW Sales.RankedProducts;

GO

CREATE VIEW Sales.RankedProducts

AS

SELECT

  ROW_NUMBER() OVER(PARTITION BY categoryid

                    ORDER BY unitprice, productid) AS rownum,

  categoryid, productid, productname, unitprice

FROM Production.Products;

GO

Note que no es el conjunto resultado de la vista la que es almacenada en la base de datos; más bien, sólo su definición es almacenada. Ahora que la definición es almacenada, el objeto es reutilizable. Siempre que necesite consultar la vista, está disponible, suponiendo que tiene los permisos para consultarla.

SELECT categoryid, productid, productname, unitprice

FROM Sales.RankedProducts

WHERE rownum <= 2;

Así para las funciones con valores de tabla InLine, son muy similares a las vistas en concepto; sin embargo, como se mencionó, ellos soportan parámetros de entrada. Así que si quiere definir algo como una vista con parámetros, lo más cercano que tiene es una función con valores de tabla InLine. Como ejemplo, considere el CTE recursivo de la sección sobre los CTEs que retornó la cadena de gestión que conduce al empleado 9. Suponga que quería encapsular la lógica en una expresión de tabla para reutilizarla, pero también quería parametrizar la entrada empleado en lugar de utilizar la constante 9. Puede lograr esto utilizando una función con valores de tabla InLine con la siguiente definición.

IF OBJECT_ID('HR.GetManagers','IF') IS NOT NULL DROP FUNCTION HR.GetManagers;

GO

CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE

AS

RETURN

  WITH EmpsCTE AS

  (

    SELECT empid, mgrid, firstname, lastname, 0 AS distance

    FROM HR.Employees

    WHERE empid = @empid

    UNION ALL

    SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 As distance

    FROM EmpsCTE AS S

      JOIN HR.Employees AS M

        ON S.mgrid = M.empid

  )

  SELECT empid, mgrid, firstname, lastname, distance

  FROM EmpsCTE;

GO

Observe que la cabecera asigna la función con el nombre (HR.GetManagers), define el parámetro de entrada (@empid AS INT), e indica que la función retorna un resultado de tabla (definido por la consulta retornada). Entonces, la función tiene una cláusula RETURN retornando el resultado de la consulta recursiva, y el miembro anclado del CTE recursivo filtra el empleado cuyo ID es igual al empid de entrada. Cuando consulta la función, pasa un empid de entrada específico como muestra el siguiente ejemplo.

SELECT *

FROM HR.GetManagers(9) AS M;

APPLY

El operador APPLY es un operador poderoso que puede utilizar para aplicar una expresión de tabla dada, a una entrada derecha para cada fila de una expresión de tabla dada, así como la entrada izquierda. Lo que es interesante acerca del operador APPLY, en comparación a un join es que la expresión de tabla derecha puede ser correlacionada a la tabla izquierda; en otras palabras, la consulta interna en la expresión de la tabla derecha puede tener una referencia a un elemento de la tabla izquierda. Así conceptualmente, la expresión de tabla derecha es evaluada por separado para cada fila izquierda. Esto significa que puede reemplazar el uso de cursores en algunos casos con el operador APPLY.

Por ejemplo, suponga que tiene una consulta que realiza alguna lógica para un cliente particular. Suponga que necesita aplicar esta lógica de consulta a cada cliente de la tabla Sales.Customers. Puede usar un cursor para iterar a través de los clientes, y en cada iteración invocar a la consulta para el cliente actual. En lugar de ello, puede utilizar el operador APPLY, proporcionando la tabla Sales.Customers como la entrada izquierda, y una expresión de tabla basada en su consulta como la entrada derecha. Puede correlacionar el ID Cliente en la consulta interna de la expresión de tabla derecha al ID Cliente de la tabla izquierda.

Las dos formas del operador APPLY; CROSS y OUTER, son descritos en las siguientes secciones.

CROSS APPLY

El operador CROSS APPLY opera sobre las expresiones de tabla izquierda y derecha como entradas. La expresión de tabla derecha puede tener una correlación a elementos de la tabla izquierda. La expresión de tabla derecha es aplicada a cada fila de la entrada izquierda. Lo que es especial acerca del operador CROSS APPLY comparado al OUTER APPLY es que si la expresión de tabla derecha retorna un conjunto vacío para una fila izquierda, la fila izquierda no es retornada. La figura muestra una ilustración del operador CROSS APPLY. 

Las letras X, Y y Z representan los valores clave de la tabla izquierda. F representa la expresión de tabla proporcionada como la entrada derecha, y entre paréntesis, se puede ver el valor clave de la fila izquierda pasada como el elemento correlacionado. En el lado derecho de la ilustración, puede ver el resultado retornado de la expresión de tabla derecha para cada fila izquierda. Luego en la parte inferior, puede ver el resultado del operador de tabla CROSS APPLY, donde las filas izquierdas corresponden con las respectivas filas derechas que fueron retornadas por ellos. Observe que una fila izquierda que obtiene un conjunto vacío de regreso de la expresión de tabla derecha no es retornada. Tal es el caso con la fila con el valor clave Z.

Como un ejemplo más práctico, suponga que escribe una consulta que retorna los dos productos con los precios unitarios más bajos para un proveedor especificado, digamos, el proveedor 1.

SELECT productid, productname, unitprice

FROM Production.Products

WHERE supplierid = 1

ORDER BY unitprice, productid

OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY;

Esta consulta genera la siguiente salida.

productid  productname    unitprice

---------- -------------- ----------

3           Product IMEHJ 10.00

1           Product HHYDP 18.00

Luego, suponga que necesita aplicar esta lógica a cada uno de los proveedores de Japón que tiene en la tabla Production.Suppliers. No desea utilizar un cursor para iterar a través de los proveedores uno a la vez e invocar una consulta separada para cada uno. En lugar de ello, puede utilizar el operador CROSS APPLY como en la siguiente.

SELECT S.supplierid, S.companyname AS supplier, A.*

FROM Production.Suppliers AS S

  CROSS APPLY (SELECT productid, productname, unitprice

  FROM Production.Products AS P

  WHERE P.supplierid = S.supplierid

  ORDER BY unitprice, productid

  OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS A

WHERE S.country = N'Japan';

Esta consulta genera el siguiente resultado.

supplierid  supplier        productid  productname    unitprice

----------- --------------- ---------- -------------- ----------

4            Supplier QOVFD  74          Product BKAZJ       10.00

4            Supplier QOVFD  10          Product YHXGE       31.00

6            Supplier QWUSF  13          Product POXFU       6.00

6            Supplier QWUSF  15          Product KSZOI       15.50

Como puede ver en la consulta, la entrada izquierda al operador APPLY es la tabla Production.Suppliers, con sólo proveedores de Japón filtrados. La expresión de tabla derecha es una tabla derivada correlacionada retornando los dos productos con los precios más bajos para el proveedor de la izquierda. Debido a que el operador APPLY aplica la expresión de tabla derecha a cada proveedor de la izquierda, se obtiene los dos productos con los precios más bajos por cada proveedor de Japón. Debido a que el operador CROSS APPLY no retorna las filas izquierdas para el cual la expresión de tabla derecha retorna un conjunto vacío, los proveedores de Japón que no tienen productos relacionados no son retornados.

OUTER APPLY

El operador OUTER APPLY hace lo que el operador CROSS APPLY hace, pero también incluye en las filas de resultados del lado izquierdo, obtener un conjunto vacío de retorno del lado derecho. Los NULLs son utilizados como marcadores de posición para las columnas de resultado desde el lado derecho. En otras palabras, el operador OUTER APPLY preserva el lado izquierdo. En cierto sentido, la diferencia entre OUTER APPLY y CROSS APPLY es similar a la diferencia entre un LEFT OUTER JOIN y un INNER JOIN. La figura muestra una ilustración del operador OUTER APPLY: 

Observe que esta vez la fila izquierda con el valor clave Z es preservada.

Regrese al ejemplo retornando los dos productos con los precios más bajos por cada proveedor de Japón: Si utiliza el operador OUTER APPLY en lugar del CROSS APPLY, preservará el lado izquierdo. Aquí está la consulta revisada.

SELECT S.supplierid, S.companyname AS supplier, A.*

FROM Production.Suppliers AS S

  OUTER APPLY (SELECT productid, productname, unitprice

  FROM Production.Products AS P

  WHERE P.supplierid = S.supplierid

  ORDER BY unitprice, productid

  OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS A

WHERE S.country = N'Japan';

Aquí está la salida de esta consulta.

supplierid  supplier          productid  productname    unitprice

----------- --------------- ---------- -------------- ----------

4            Supplier QOVFD  74           Product BKAZJ      10.00

4            Supplier QOVFD  10           Product YHXGE      31.00

6            Supplier QWUSF  13           Product POXFU      6.00

6            Supplier QWUSF  15           Product KSZOI      15.50

30           Supplier XYZ    NULL         NULL               NULL

Observe que el proveedor 30 fue preservado esta vez a pesar de que no tiene productos relacionados.

Ejercicio 1: Retornar Productos con el Precio Unitario Mínimo por Categoría

En este ejercicio, escribe una solución que utilice un CTE para retornar los productos con el precio unitario más bajo por categoría.

1.    Abra el SSMS y conéctese a la base de datos de muestra TSQL2012.

2.    Como primer paso en su solución, escriba una consulta sobre la tabla Production.Products que agrupa los productos por categoryid y retorna para cada categoría el precio unitario mínimo. Aquí está una consulta que logra este paso.

SELECT categoryid, MIN(unitprice) AS mn

FROM Production.Products

GROUP BY categoryid;

Esta consulta genera el siguiente resultado.

categoryid   mn

----------- ---------------------

1            4.50

2            10.00

3            9.20

4            2.50

5            7.00

6            7.45

7            10.00

8            6.00

3.    El siguiente paso en la solución es definir un CTE basado en la consulta anterior y, luego unir la CTE a la tabla Production.Products para retornar por cada categoría los productos con el precio unitario mínimo. Este paso se puede lograr con el código siguiente.

WITH CatMin AS

(

  SELECT categoryid, MIN(unitprice) AS mn

  FROM Production.Products

  GROUP BY categoryid

)

SELECT P.categoryid, P.productid, P.productname, P.unitprice

FROM Production.Products AS P

  INNER JOIN CatMin AS M

    ON P.categoryid = M.categoryid

    AND P.unitprice = M.mn;

Este código representa la solución completa devolviendo el resultado deseado.

categoryid  productid  productname    unitprice

----------- ---------- -------------- ----------

1            24         Product QOGNU      4.50

2            3          Product IMEHJ      10.00

3            19         Product XKXDO      9.20

4            33         Product ASTMN      2.50

5            52         Product QSRXF      7.00

6            54         Product QAQRL      7.45

7            74         Product BKAZJ      10.00

8            13         Product POXFU      6.00

Ejercicio 2: Retorna N Productos con el Precio Unitario más Bajo por Proveedor

En este ejercicio, practica el uso de operadores CROSS APPLY y OUTER APPLY.

1.    Definir una función con valores de tabla InLine que acepta un supplierid como entrada (@supplierid), además a un número (@n), y retorna los @n productos con los precios más bajos para el proveedor de entrada. En caso de empate en el precio unitario, utilice el productid como el desempate. Utilice el código siguiente para definir la función.

IF OBJECT_ID('Production.GetTopProducts','IF') IS NOT NULL DROP FUNCTION

Production.GetTopProducts;

GO

CREATE FUNCTION Production.GetTopProducts(@supplierid AS INT, @n AS BIGINT)

RETURNS TABLE

AS

RETURN

  SELECT productid, productname, unitprice

  FROM Production.Products

  WHERE supplierid = @supplierid

  ORDER BY unitprice, productid

  OFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;

GO

2.    Consulte la función para probarlo, proporcionando el ID Proveedor de 1 y el número 2 para retornar los dos productos con los precios más bajos para el proveedor de entrada.

SELECT * FROM Production.GetTopProducts(1, 2) AS P;

Este código genera el siguiente resultado:

productid  productname    unitprice

---------- -------------- ----------

3          Product IMEHJ      10.00

1          Product HHYDP      18.00

3.    Luego, retorne por cada proveedor de Japón los dos productos con los precios más bajos. Para lograr esto, utilice el operador CROSS APPLY, con Production.Suppliers como el lado izquierdo y la función Production.GetTopProducts como el lado derecho, como sigue.

SELECT S.supplierid, S.companyname AS supplier, A.*

FROM Production.Suppliers AS S

  CROSS APPLY Production.GetTopProducts(S.supplierid, 2) AS A

WHERE S.country = N'Japan';

Este código genera el siguiente resultado.

supplierid  supplier          productid  productname   unitprice

----------- --------------- ---------- -------------- ----------

4           Supplier QOVFD    74         Product BKAZJ   10.00

4           Supplier QOVFD    10         Product YHXGE   31.00

6           Supplier QWUSF    13         Product POXFU   6.00

6           Supplier QWUSF    15         Product KSZOI   15.50

4.    En el paso anterior, utilizó el operador CROSS APPLY, y por lo tanto, los proveedores de Japón con productos no relacionados fueron descartados. Suponga que necesita retornar estos también. Necesita preservar el lado izquierdo, y para lograrlo, utiliza el operador OUTER APPLY, de la siguiente manera.

SELECT S.supplierid, S.companyname AS supplier, A.*

FROM Production.Suppliers AS S

  OUTER APPLY Production.GetTopProducts(S.supplierid, 2) AS A

WHERE S.country = N'Japan';

Esta vez la salida incluye proveedores sin productos.

supplierid  supplier         productid  productname    unitprice

----------- --------------- ---------- -------------- ----------

4           Supplier QOVFD    74         Product BKAZJ   10.00

4           Supplier QOVFD    10         Product YHXGE   31.00

6           Supplier QWUSF    13         Product POXFU   6.00

6           Supplier QWUSF    15         Product KSZOI   15.50

30          Supplier XYZ      NULL       NULL            NULL

5.    Cuando haya terminado, ejecute el siguiente código para la limpieza.

IF OBJECT_ID('Production.GetTopProducts','IF') IS NOT NULL DROP FUNCTION

Production.GetTopProducts;