Tema 13: Usar Técnicas Avanzadas de Consulta para Acceder a los Datos

Una vez que ha logrado sentirse confortable con los fundamentos del comando SELECT, y está familiarizado con varias de sus cláusulas, Ud. está listo para aprender técnicas de consultas más avanzadas. Una de estas técnicas es la de combinar el contenidos de una o mas tablas para producir un conjunto de resultados que incorpore filas y columnas de cada tabla. Otra técnica es la de usar subconsultas, las cuales se anidan dentro de otros comando SELECT, INSERT, UPDATE, o DELETE. Subconsultas pueden también ser anidadas dentro de otras subconsultas. Se pueden usar los elementos de Transact-SQL tales con CUBE y ROLLUP para resumir datos.

Usar combinaciones para recuperar datos

Usando combinaciones (joins), se pueden recuperar datos desde dos o más tablas basados en relaciones lógicas entre las tablas. Las combinaciones indican cómo el SQL Server utilizará los datos de una tabla para seleccionar las filas en otra tabla.

Las combinaciones se pueden especificar tanto en la cláusulas FROM o WHERE. Las condiciones de combinación se unen con las condiciones de búsqueda de las cláusulas WHERE y HAVING para controlar qué filas serán seleccionadas desde las tablas base referenciadas en la cláusula FROM. Especificar las condiciones de combinación en la cláusula FROM, sin embargo, sirve para separarlas de cualquier otra condición que podría ser especificada en una cláusula WHERE y se recomienda para especificar combinaciones.

Cuando se referencian múltiples tablas en una sola consulta, todas las columnas referenciadas deben ser indicadas de manera no ambigua. El nombre de la tabla debe ser utilizado para calificar cualquier nombre de columna que esté duplicado en dos o más tablas referenciadas en una consulta simple.

La lista de selección para una combinación puede referenciar a todas las columnas de las tablas combinadas o a cualquier subconjunto de columnas. No se requiere que la lista de selección contenga las columnas de todas las tablas en la combinación. Por ejemplo, en una combinación de tres tablas, una tabla puede ser utilizada como puente desde una de las otras tablas a la tercera tabla, y no ser referenciada en la lista de selección ninguna de las columnas de la tabla puente

Aunque las condiciones de combinación utilizan generalmente el operador de comparación igual (=), se puede especificar cualquier otro operador relacional, así como otros predicados.

Cuando SQL Server procesa combinaciones, el motor de consultas elige el método más eficiente (de entre varias posibilidades) para procesar la combinación. Aunque la ejecución física de varias combinaciones usa diferentes caminos, se respeta la siguiente secuencia lógica:

• Se aplican las condiciones de combinación de la cláusula FROM.
• Se aplican las condiciones de combinación y las condiciones de búsqueda de la cláusula WHERE.
• Se aplican las condiciones de búsqueda de la cláusula HAVING.

Esta secuencia puede, algunas veces, influenciar el resultado de la consulta si se mueven las condiciones entre la cláusulas FROM y WHERE.

Las columnas usadas en las condiciones de combinación, no necesariamente deben tener el mismo nombre o el mismo tipo de datos. Si los tipos de datos no son idénticos, sin embargo, deben ser compatibles o ser tipos que SQL Server pueda convertir de manera implícita. En caso que los tipos de datos no puedan ser convertidos de manera implícita, la condición de combinación debe explícitamente convertir los tipos de datos usando la función CAST.

NOTA

La mayoría de las combinaciones pueden ser reescritas como subconsultas (una consulta anidada dentro de otra consulta), y muchas subconsultas pueden ser reescritas como combinaciones. Veremos subconsultas en detalle mas adelante.

Muchas combinaciones pueden ser categorizadas como combinaciones inner o outer. Las combinaciones inner retornan las filas sólo cuando existe al menos una fila en ambas tablas que cumplen con la condición de combinación, eliminando las filas que no se asocian a otra fila en la otra tabla. Las combinaciones outer, sin embargo, retornan todas las filas desde al menos una tabla o vista, mencionada en la cláusula FROM, siempre que esa fila cumpla con las condiciones de búsqueda de las cláusulas WHERE y HAVING.

Combinaciones Inner

Una combinación inner es una combinación en la cual los valores de las columnas a ser combinadas son comparados a través de un operador de comparación. En el SQL-92 estándar, las combinaciones inner pueden ser especificadas tanto en las cláusulas FROM o WHERE. Las combinaciones inner son el único tipo de combinación que el SQL-92 soporta en la cláusula WHERE. Combinaciones inner establecidas en la cláusula WHERE son conocidas como combinaciones al viejo estilo.

El siguiente comando SELECT usa una combinación inner para recuperar datos desde la tabla Editores y la tabla Titulos en la base de datos Pubs.

SELECT t.Titulo, p.Ed_Nombre

FROM Editores AS p INNER JOIN Titulos AS t

ON p.Ed_id = t.Ed_id

ORDER BY Titulo ASC

Este comando SELECT recupera datos desde la columna Titulo en la tabla Titulos (t) y desde la columna Ed_Nombre en la tabla Editores (p). Dado que este comando utiliza una combinación inner, esta retornará sólo aquellas columnas que tengan un valor igual en las columnas de la combinación (la columna p.Ed_id y la t.Ed Publishers).

Combinaciones Outer

SQL Server soporta tres tipos de combinaciones outer: izquierda (left), derecha (right), y completa (full). Todas las filas recuperadas desde la tabla izquierda son referenciadas con una combinación outer izquierda, y todas las filas de la tabla derecha son referenciadas en una combinación outer derecha. Todas las filas de ambas tablas son retornadas en una combinación outer completa.

Usar combinaciones outer izquierdas

Un conjunto de resultados generado por un comando SELECT que incluye una combinación outer izquierda incluye todas las filas de la tabla referenciada a la izquierda del LEFT OUTER JOIN. Las únicas filas de la tabla de la derecha que se recuperarán serán aquellas que cumplan con la condición de la combinación.

En el siguiente comando SELECT, se usa una combinación outer izquierda para recuperar los apellidos y nombres de los autores y (cuando corresponda) los nombres de cualquier editor que se encuentre en la misma ciudad que el autor:

USE Pubs

SELECT a.Au_nombre, a.Au_apellido, p.Ed_nombre

FROM Autores a LEFT OUTER JOIN Editores p

ON a.Ciudad = p.Ciudad

ORDER BY p.Ed_nombre ASC, a.Au_apellido ASC, a.Au_nombre ASC

El conjunto de resultados para esta consulta listará el nombre de todos los autores de la tabla Autores. El conjunto de resultados incluirá sólo aquellos editores que se encuentren en las mismas ciudades de los autores. Si un editor no se encuentra en la ciudad del autor, un valor nulo es retornado para la columna Ed_nombre del conjunto de resultados.

Usar combinaciones outer derecha

Un conjunto de resultados generado por un comando SELECT que incluye una combinación outer derecha incluirá todas las filas de la tabla referenciada a la derecha del RIGHT OUTER JOIN. Las únicas columnas que serán recuperadas de la tabla izquierda serán aquellas que cumplan con la condición de la combinación.

En el siguiente comando SELECT, se usa una combinación outer derecha para obtener la lista de editores y los nombres y apellidos de los autores, si estos autores se encuentran en la misma ciudad de los editores:

USE Pubs

SELECT a.Au_nombre, a.Au_apellido, p.Ed_nombre

FROM Autores a RIGHT OUTER JOIN Editores p

ON a.Ciudad = p.Ciudad

ORDER BY p.Ed_nombre ASC, a.Au_apellido ASC, a.Au_nombre ASC

El conjunto de resultados de esta consulta listará los nombre de todos los editores de la tabla Editores. El conjunto de resultados incluirá solo aquellos autores que se encuentren en la misma ciudad del editores. Si un autor no se encuentra en la ciudad del editor, un valor nulo se retorna para las columnas Au_apellido y Au_ nombre del conjunto de resultados.

Usar combinaciones outer completas

Un conjunto de resultados generado por un comando SELECT que incluya una combinación outer completa incluirá todas las filas desde ambas tablas, sin tener en cuenta si los valores cumplen con la condición de la combinación.

En el siguiente comando SELECT, s usa una combinación outer completa para obtener la lista de los editores y los nombres y apellidos de los autores:

USE Pubs

SELECT a.Au_nombre, a.Au_apellido, p.Ed_nombre

FROM Autores a FULL OUTER JOIN Editores p

ON a.Ciudad = p.Ciudad

ORDER BY p.Ed_nombre ASC, a.Au_apellido ASC, a.Au_nombre ASC

El conjunto de resultados de esta consulta listará el nombre de todos los editores de la tabla Editores y todos los autores de la tabla Autores. Si un autor no se encuentra ubicado en la misma ciudad del editor, un valor nulo se retorna en la columnas Au_apellido y Au_nombre del conjunto de resultados. Si un editor no se encuentra en la misma ciudad del autor, se retorna un valor nulo en la columna Ed_nombre del conjunto de resultados. Cuando se cumple la condición todas las columnas del conjunto de resultados tendrán un valor.

Definir subconsultas dentro del comando SELECT

Una subconsulta es un comando SELECT que retorna un valor simple y se encuentra anidado dentro de un comando SELECT, INSERT, UPDATE, o DELETE o dentro de otra subconsulta. Una subconsulta puede usarse en cualquier lugar en que una expresión lo permita.

En el ejemplo siguiente, se anida una subconsulta en la cláusula WHERE de un comando SELECT:

USE Northwind

SELECT NombreProducto

FROM Productos

WHERE PreciioUnit =

(

SELECT PrecioUnit

FROM Productos

WHERE NombreProducto = 'Sir Rodney''s Scones'

)

El comando SELECT embebido primero ubica el valor de PrecioUnit de los Sir Rodney's Scones, el cual es de $10. Este valor ($10) se usa luego en el comando SELECT exterior para obtener los nombres de los productos que tienen el mismo precio unitario.

Si una tabla figura solo en la subconsulta y no en la consulta exterior, las columnas de esta tabla no pueden ser incluidas en el resultado de la consulta exterior.

En algunos comandos Transact-SQL, la subconsulta puede ser evaluada como una consulta independiente. Conceptualmente, el resultado de una subconsulta se sustituye dentro de la consulta exterior (aún cuando no sea necesariamente cómo SQL Server resuelve los comandos Transact-SQL que tienen subconsultas)

Tipos de subconsultas

Las subconsultas se pueden definir en muchos lugares dentro de un comando SELECT. Sin embargo, los comandos que incluyen subconsultas generalmente adoptan uno de los siguientes formatos:

• WHERE [NOT] IN ()
• WHERE < expresión > [ANY | ALL] ()
• WHERE [NOT] EXISTS ()

Subconsultas que se usan con IN o con NOT IN

El resultado de una subconsulta introducida con IN (o con NOT IN) es una lista de cero o más valores. Después que la subconsulta devuelve el resultado, la consulta exterior lo utiliza.

En el ejemplo siguiente. Una subconsulta se anida dentro de la cláusula WHERE, usando la palabra clave IN:

USE Pubs

SELECT Ed_nombre

FROM Editores

WHERE Ed_id IN

(

SELECT Ed_id

FROM Titulos

WHERE Tipo = 'negocios'

)

Se puede evaluar este comando en dos pasos. Primero, la subconsulta retorna los números de ID de los editores que han publicado libros de negocios. Luego estos valores son sustituidos en la consulta exterior. la cual encuentra los nombres que igualan estos números de identificación. Primero, la subconsulta retorna los números de identificación de los editores que tienen libros de negocios publicados. Segundo, estos valores se sustituyen en la consulta exterior, la cual encuentra los nombres de los editores que tienen números de identificación dentro del conjunto de resultados de la subconsulta en la tabla Editores.

Las subconsultas introducidas con la palabra clave NOT IN también retornan una lista de cero o más valores. La consulta trabaja exactamente igual que una con IN, excepto que con NOT IN se seleccionan a todos aquellas filas cuyos valores en la columna de comparación (Ed_in en este caso) no se encuentren del conjunto de resultados de la subconsulta.

Subconsultas que son usadas con operadores de comparación

Los operadores de comparación que introduce una subconsulta se pueden modificar con las palabras claves ALL o ANY. La palabra clave SOME del SQL-92 estándar es equivalente a ANY. Las subconsultas introducidas con un operador de comparación modificado retornan una lista de cero o más valores y pueden incluir una cláusula GROUP BY o HAVING. Estas consultas pueden tener la cláusula EXISTS.

Las palabras clave ALL y ANY comparan un valor escalar con un conjunto de valores simples. La palabra clave ALL indica que se debe aplicar a todos los valores, mientras que ANY indica que al menos a un valor.

En el siguiente ejemplo, el operador de comparación mayor que (>) es usado con ANY:

USE Pubs

SELECT Titulo

FROM Titulos

WHERE Adelanto > ANY

(

SELECT Adelanto

FROM Editores INNER JOIN Titulos

ON Titulos.Ed_id = Editores.Ed_id

AND Ed_nombre = 'Algodata Infosystems'

)

Este comando encuentra los títulos que reciben un adelanto mayor que el mínimo adelanto pagado por Algodata Infosystems. La cláusula WHERE en el comando SELECT exterior contiene una subconsulta que usa una combinación para recuperar monto de adelantos para Algodata Infosystems. El mínimo adelanto es utilizado para determinar que títulos obtener de la tabla Titulos.

Subconsultas que se usan con EXISTS y NOT EXISTS

Cuando se introduce una subconsulta con la palabra clave EXISTS. Esta funciona como un test de existencia. La cláusula WHERE de la consulta exterior comprueba por la existencia de las filas retornadas por la subconsulta. La subconsulta en realidad no produce ningún dato, solo retorna un valor de TRUE o FALSE.

En el siguiente ejemplo, la cláusula WHERE en el comando SELECT exterior contiene una subconsulta que usa EXISTS:

USE Pubs

SELECT Ed_nombre

FROM Editores

WHERE EXISTS

(

SELECT * FROM Titulos

WHERE Titulos.Ed_id = Editores.Ed_id

AND Tipo = 'negocios'

)

Para determinar el resultado de esta consulta, se toma cada fila de la tabla Editores y se verifica que exista dentro de las filas de la base titulo que sean de ese editor (Titulos.Ed_id = Editores.Ed_id) y el tipo sea "negocios". Si existe algún título que cumpla, el nombre de ese editor figurará en el conjunto de resultados.

La palabra clave NOT EXISTS trabaja como EXISTS, excepto que la cláusula WHERE que tiene NOT EXIST será satisfecha solo si la subconsulta no devuelve ninguna fila.

Resumir datos

Transact-SQL incluye varios elementos que lo habilitan para generar reportes simples resumidos. Se pueden usar los operadores CUBE o ROLLUP, los que son parte de la cláusula GROUP BY del comando SELECT. También se pueden usar los operadores COMPUTE o COMPUTE BY, los que también se asocian a la cláusula GROUP BY.

Los operadores COMPUTE y COMPUTE BY se mantienen por compatibilidad con versiones anteriores.

Usar el operador CUBE para resumir datos

El operador CUBE genera un conjunto de resultados que es un cubo multidimensional. Un cubo multidimensional es una expansión de datos sobre sucesos, o datos que registran eventos individuales. La expansión está basada sobre columnas que el usuario desea analizar. Estas columnas son llamadas dimensiones. El cubo es un conjunto de resultados conteniendo un tabla cruzada de todas las combinaciones posibles.

El siguiente comando SELECT usa al operador CUBE en la cláusula GROUP BY:

USE Pubs

SELECT SUBSTRING(Titulo, 1, 65) AS Titulo,

SUM(cant) AS 'Cantidad'

FROM Ventas INNER JOIN Titulos

ON Ventas.Titulo_id = Titulos.Titulo_id

GROUP BY Titulo

WITH CUBE

ORDER BY Titulo

Este comando SELECT cubre una relación uno a muchos entre los títulos de los libros y las cantidades vendidas de cada uno. Al usar el operador CUBE, el comando devuelve una columna extra. La columna extra (la cual contiene un valor nulo en la columna Titulo del conjunto de resultados) representa a todos los valores en la columna Titulo de la tabla Titulos. El conjunto de resultado devuelve los valores para la cantidad vendida de todos los títulos. En este caso, usar el operador CUBE o el operador ROLLUP retorna el mismo resultado.

Usar el operador ROLLUP para resumir datos

El operador ROLLUP es útil para generar reportes que contienen subtotales y totales. El operador ROLLUP genera un conjunto de resultados que es similar al conjunto de resultados del operador CUBE. Las diferencias entre los operadores CUBE y ROLLUP son las siguientes:

• CUBE genera un conjunto de resultados mostrando agregaciones para todas las combinaciones de valores en las columnas seleccionadas.
• ROLLUP genera un conjunto de resultados mostrando agregaciones para jerarquías en las columnas seleccionadas.

El siguiente comando SELECT contiene un operador ROLLUP en la cláusula GROUP BY:

USE Pubs

SELECT Ed_nombre, Au_apellido, Titulo, SUM(cant) AS 'Sum'

FROM Autores a INNER JOIN TituloAutor ta

ON a.Au_id = ta.Au_id INNER JOIN Titulos t

ON t.Titulo_id = ta.Titulo_id INNER JOIN Editores p

ON p.Ed_id = t.Ed_id INNER JOIN Ventas s

ON s.Titulo_id = t.Titulo_id

GROUP BY Ed_nombre, Au_apellido, Titulo

WITH ROLLUP

Al usar el operador ROLLUP, se pueden crear agrupamientos en el conjunto de resultados. Para las filas agrupadas, se usa un valor nulo para representar todos los valores para la columna (excepto la columna Sum). Si se usa un comando SELECT sin el operador ROLLUP, el comando generará los siguientes datos cuando se listen las columnas Ed_nombre, Au_nombre, y Titulo (en ese orden) en la cláusula GROUP_BY:

• Cantidad de cada título que cada editor vendió para cada autor.
• Cantidad de todos los títulos que cada editor vendió
• Cantidad total de todos los títulos vendidos por todos los editores para todos los autores.