SQL Server admite funciones tabulares que son mostradas en ejecución, y que son funciones que devuelven datos en forma de tablas.
Las operaciones JOIN en SQL Server son utilizadas para unir dos o más tablas. Sin embargo, las operaciones JOIN son las que no se pueden usar para unir una tabla con la salida de una función con valores de tabla.
APPLY se utilizan operadores para este propósito.
Hay dos tipos principales de operadores APPLY. 1) CROSS APPLY y 2) OUTER APPLY.
Es fundamental entender que el operador CROSS APPLY es semánticamente similar al operador INNER JOIN. El mismo recupera esos registros de la función de valor de tabla y la tabla que se está uniendo, en la misma donde encuentra filas coincidentes entre los dos.
Por otro lado, OUTER APPLY es una opción que recupera todos los registros tanto de la función con valor de tabla como de la tabla, independientemente de la coincidencia.
En este artículo echaremos un vistazo a los operadores CROSS APPLY y OUTER APPLY. Veremos cómo se implementan de manea más práctica con la ayuda de un ejemplo y también lo discutiremos cómo difieren entre sí.
Preparando datos ficticios
Primero, deberemos crear una base de datos ficticia con algunos registros igualmente ficticios. A tal final de esto, utilizaremos esta base de datos ficticia para efectuar muchas operaciones diferentes a lo largo de este artículo. Como siempre y es habitual, si está probando cosas en una base de datos en vivo, usted deberá asegurarse de verificar que los datos estén completamente respaldados.
Ejecute el siguiente script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
CREATE DATABASE Library GO USE Library; CREATE TABLE Author ( id INT PRIMARY KEY, author_name VARCHAR(50) NOT NULL, ) CREATE TABLE Book ( id INT PRIMARY KEY, book_name VARCHAR(50) NOT NULL, price INT NOT NULL, author_id INT NOT NULL ) USE Library; INSERT INTO Author VALUES (1, 'Author1'), (2, 'Author2'), (3, 'Author3'), (4, 'Author4'), (5, 'Author5'), (6, 'Author6'), (7, 'Author7') INSERT INTO Book VALUES (1, 'Book1',500, 1), (2, 'Book2', 300 ,2), (3, 'Book3',700, 1), (4, 'Book4',400, 3), (5, 'Book5',650, 5), (6, 'Book6',400, 3) |
En el script anterior creamos una base de datos llamada biblioteca. En esta base de datos se tienen dos tablas: Autor y Libro. El libro tiene una columna de author_id que contiene valores de la columna id de la tabla Autor. Esto significa que existen una o varias relaciones entre las columnas Autor y Libro.
Uniendo las tablas usando operadores JOIN
Primero usaremos el operador INNER JOIN para recuperar filas coincidentes de ambas tablas.
Ejecute el siguiente script:
|
1 2 3 4 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A INNER JOIN Book B ON A.id = B.author_id |
Se seleccionarán los siguientes registros.
Como se puede observar, solo se han seleccionado esos registros de la tabla Autor donde hay una fila coincidente en la tabla Libro. Consiguientemente, para recuperar todos los registros de la tabla Autor, se puede utilizar LEFT JOIN.
|
1 2 3 4 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A LEFT JOIN Book B ON A.id = B.author_id |
El resultado de la consulta anterior se ve así:
En esta situación, usted puede ver que todos los registros se recuperan de la tabla Autor, independientemente de que haya filas coincidentes en la tabla Libro.
Uniendo las funciones tabuladas ubicadas en la tabla utilizando operadores APPLY
En esta oportunidad es que podemos observar cómo los operadores de JOIN unen los resultados de dos tablas. Sin embargo, como se mencionó anteriormente, las mismas no se pueden usar para unir una función tabulada con una tabla. Una función con valores de tabla es una función que devuelve registros en forma de tabla.
Primero, escriba una función simple con valores de tabla que acepte la identificación del autor como los mismos parámetros y devuelva todos los libros escritos por ese autor.
Ejecute el siguiente script:
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION fnGetBooksByAuthorId(@AuthorId int) RETURNS TABLE AS RETURN ( SELECT * FROM Book WHERE author_id = @AuthorId ) |
En esta oportunidad podemos probar la función anterior. Consiguientemente, pasaremos 3 como la identificación del autor a la función fnGetBooksByAuthorId. La misma que Debería devolver book4 y book6, ya que estos son los dos libros escritos por el autor con la identificación de tres.
Ejecute el siguiente script:
|
1 |
SELECT * FROM fnGetBooksByAuthorId(3) |
El resultado del script anterior se ve así:
Por esta razón intentaremos usar un operador INNER JOIN para unir la tabla de Autor con la función de valor de tabla fnGetBooksByAuthorId.
Por ello deberá echar un vistazo al siguiente script:
|
1 2 3 4 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A INNER JOIN fnGetBooksByAuthorId(A.Id) B ON A.id = B.author_id |
Aquí estamos utilizando el operador INNER JOIN con objeto de unir una tabla física (Autor) con una función ficticia con valores de tabla fnGetBooksByAuthorId. Todos los identificadores de la tabla Autor se pasan a la función. Sin embargo, el script anterior arroja un error que se ve así:
Uniendo una tabla y un valor de una función tabulada utilizando CROSS APPLY
Consiguientemente a continuación ahora, usemos el operador CROSS APPLY para unir la tabla de Autor con la función de valor de tabla fnGetBooksByAuthorId. Hay que considerar que el operador CROSS APPLY es semánticamente similar a INNER JOIN. El miso recupera todos los registros de la tabla donde hay filas coincidentes correspondientes en la salida de retorno por la función con valores de la tabla.
Eche un vistazo al siguiente script:
|
1 2 3 4 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A CROSS APPLY fnGetBooksByAuthorId(A.Id) B |
En el script delineas arriba, todos los identificadores de la tabla Autor se pasan a la función fnGetBooksByAuthorId. Por ello, para cada ID en la tabla Autor, la función retorna los registros correspondientes de la tabla Libro. Por ello, el resultado de esta función con valores de tabla se une con la tabla Autor.
El resultado del script anterior se ve así:
Como se podrá observar, esto es similar a la operación INNER JOIN efectuada por medio de las tablas Autor y Libro. Por ello la APLICACIÓN CRUZADA devuelve solo aquellos registros de una tabla física en la misma que se encuentra donde hay filas coincidentes en la salida de la función con valores de la tabla.
Uniendo una tabla y una tabla con una función valuada utilizando valores OUTER APPLY
Con objeto de recuperar todas las filas tanto de la tabla física como de la salida de los valores de la función tabulada, se utiliza OUTER APPLY. OUTER APPLY es semánticamente similar a la operación OUTER JOIN.
Para tal fin deberá echar un vistazo al siguiente script para ver OUTER APPLY en acción.
|
1 2 3 |
SELECT A.author_name, B.id, B.book_name, B.price FROM Author A OUTER APPLY fnGetBooksByAuthorId(A.Id) B |
El resultado de la función anterior se ve así:
De esta forma usted puede ver que todos los registros de la tabla Autor se han recuperado independientemente de las filas coincidentes en la salida de la función con valor de tabla fnGetBookByAuthorId.
Conclusión
Vale la pena mencionar que, en este artículo, estudiamos cuáles son las funciones CROSS APPLY y OUTER APPLY, y cómo las mismas se pueden usar para realizar operaciones de unión entre una tabla física y una con los valores de una función ya tabulada.
Ver todas las entradas de Ben Richardson
- Cómo usar las funciones de Windows en SQL Server - December 16, 2019
- Como comprender el tipo de datos GUID en SQL Server - September 30, 2019
- Cómo utilizar las funciones integradas de SQL Server y crear funciones escalares definidas por el usuario - September 30, 2019