Introducción a TSQLUnidad 5

Autor

Isabel Ponce

Unidad 5

Objetivos

• Subconsultas (Subquerys)• Modos de Subconsultas

– No Correlativas– Correlativas

• Tipos de subconsultas– De comparación– De pertenencia– De existencia

• Ventajas y Desventajas de las reuniones y subconsultas

Subconsultas

• La capacidad de crear una consulta dentro de otra fue la razón original de llamar “estructurado” a SQL.

• Esta característica es importante porque:

– Generalmente es el modo más natural de expresar una consulta.

– Son fáciles de construir descomponiendo en partes y recomponiendo la consulta.

– Hay consultas que sólo se pueden expresan con subconsultas.

• Las subconsultas son instrucciones SQL anidadas dentro de otras instrucciones SQL (Select, Insert, Update o Dalete).

Subconsultas

• El lugar para realizar esto es en cualquier parte donde se permita una expresión, generalmente en una cláusula WHERE o HAVING.

select [distinct] lista_columnasfrom lista_tablaswhere {expresión {[not] in | comparación [any |all ] } |

[ not ] exists}(select [distinct] columnas_subquery from lista_tablas

[where condiciones] [group by][having])[group by lista_columnas][having condiciones | subselect][order by lista_columnas]

• La consulta en negro es la consulta externa y la azul es la interna y siempre debe ir entre paréntesis.

Subconsultas

• Aunque ambas consultas (externa e interna) tienen la misma sintaxis, existen algunas reglas para las subconsultas (consultas internas):

– Una subconsulta debe producir una única columna de datos o expresión como resultado.

– Si se incluye una columna en el where, debe ser compatible con la expresión en la cláusula select de la consulta externa.

– Si se retorna un solo renglón no puede incluir GROUP BY y HAVING

– Si se incluye GROUP BY no se permite DISTINCT.

– No se puede especificar un ORDER BY ya que sus resultados no son visibles al usuario.

– Las columnas indicadas en la restricción WHERE/HAVING de la consulta interna, pueden referirse a columnas de la consulta externa.

Subconsultas

• Ejemplo, la siguiente subconsulta obtiene todos los títulos cuya clave de autor sea 998-72-3567:

select title from titleswhere title_id in

(select title_id from titleauthorwhere au_id = '998-72-3567')

• En este caso, primero se obtienen todos las claves de título que pertenezcan a la clave de autor indicada, estos valores se pasan a la consulta externa y con esos datos se obtienen los nombres de los libros.

Subconsultas

• Las subconsultas se manejan en dos modos básicos: correlativo y no correlativo.

• Una subconsulta no correlativa es aquella donde la consulta interna es independiente de la externa, primero se evalúa, y pasa su resultado a la consulta externa.

• Una subconsulta correlativa (de referencia externa) es aquella donde la consulta interna es dependiente de los resultados de la consulta externa.

Subconsultas

• No correlativa:select pub_name from publisherswhere pub_id in

(select pub_id from titleswhere type = 'business')

• Correlativa:select pub_name from publishers pwhere 'business' in

(select type from titles where pub_id = p.pub_id)

• Como en estos casos, la mayoría de las subconsultas se pueden expresar mediante un join.

Subconsultas No Correlativas

• A un nivel conceptual, una subconsulta no correlativa se ejecuta en dos partes.

• Primero se ejecuta la consulta interna. Después se pasan los resultados a la consulta externa, la cual busca los renglones que coinciden con la lista que recibió.

• Los nombres de las columnas son implícitamente basados en los resultados de la cláusula from en la consulta correspondiente.

• Siempre se puede definir explícitamente el nombre de la tabla. Esto se recomienda para consultas complejas.

Subconsultas Correlativas

• El proceso de subconsultas correlativas es mas complicado, pero permite manejar consultas que no se pueden crear fácilmente con consultas no correlativas o joins.

• Una subconsulta correlativa depende de los datos de la consulta externa.

• La consulta interna se ejecuta varias veces, una vez para cada renglón en la consulta externa.

• En una subconsulta correlativa o de referencia externa, se emplea alguna columna que no pertenece a las tablas de la subconsulta, sino a una columna de las tablas de la consulta principal.

Subconsultas Correlativas

• La consulta externa extrae el primer renglón candidato de datos y pasa el valor del dato a la consulta interna.

• La consulta interna encuentra todos los renglones que corresponden al dato recibido desde la consulta externa.

• Finalmente los renglones de la consulta interna son verificados contra las condiciones de la consulta externa.

• Si uno o más renglones coinciden con las condiciones, los datos correspondientes a esos renglones son retornados al usuario.

Subconsultas Correlativas en Having

• Una subconsulta correlativa se puede usar también en la cláusula HAVING de una consulta externa. Por ejemplo, la siguiente consulta obtiene las tiendas cuyo promedio de cantidad vendida es mayor al promedio global:

select stor_name, avg(qty)from stores T, sales Vwhere T.stor_id = V.stor_idgroup by stor_namehaving avg(qty) >

(select avg(qty) from sales)

Tipos de Subconsultas

• Las subconsultas además pueden ser de tres tipos diferentes:

– Con prueba de comparación. Donde retornan exactamente unelemento.

– Con prueba de pertenencia. Donde retornan cero o más elementos.

– Con prueba de existencia de un valor. Examina si se produce algún resultado.

Subconsultas conPrueba de Comparación

• Estas subconsultas se pueden presentar con uno de los operadores relacionales: =, <>, >, >=, <, !>, !<, !=

• Estas subconsultas generalmente incluyen funciones de agregado, ya que retornan un valor individual.

• Además no pueden incluir GROUP BY a menos que generen un solo valor.

• Las subconsultas con operadores relacionales sin modificador: ANY (SOME), ALL, deben retornar un solo valor y no una lista de valores, de no ser así se obtiene un error.

• La sintaxis es:

WHERE expresion operador_relacional [ANY |SOME | ALL] (subconsulta)

Subconsultas conPrueba de Comparación

• Ejemplos:• Obtener los libros cuyo precio sea

menor al precio del libro 'Life without fear‘select title, pricefrom titleswhere price <

(select pricefrom titleswhere title = 'Life without fear')

• Buscar las órdenes con una cantidad solicitada superior a la cantidad actual mínima de la orden ‘P2121’select ord_num, qtyfrom saleswhere qty >

(select min(qty)from salesgroup by ord_numhaving ord_num ='P2121')

Modificadores ANY, SOME y ALL

• Los modificadores ANY y ALL pueden ser usados con un operador relacional en una subconsulta. Cuando se usan, se retorna una lista de cero o más valores y pueden incluir GROUP BY

• SOME es equivalente a ANY.

– > ALL significa mayor que cada valor en el resultado de la consulta interna (mayor al valor máximo).

– > ANY significa mayor que cualquier valor en el resultado de la consulta interna (mayor al valor mínimo).

ALL Resultado ANY Resultado

> all (1,2,3) > 3 > any (1,2,3) > 1

< all (1,2,3) < 1 < any (1,2,3) < 3

= all (1,2,3) =1 y =2 y =3 = any (1,2,3) =1 o =2 o =3

<>all es igual a NOT IN <>any diferente a NOT IN (son or no and)

Any y All

• La siguiente consulta obtiene todos los títulos que tengan un anticipo mayor a todos los anticipos de la editorial New Moon Books.

select title, advance, pub_idfrom titles where advance > all

(select advance from publishers, titles where titles.pub_id = publishers.pub_id and pub_name = 'New Moon Books')

title

---------------------------------------------------------- The Gourmet Microwave

(1 filas afectadas)

Subconsultas conPrueba de Pertenencia

• Este tipo de subconsulta debe ser precedida por un operador IN, ya que obtiene una lista de cero o más valores.where column = (select…) retornará un error si la subconsulta retorna más de un elemento.

• Su sintaxis es:

WHERE expresion [NOT] IN (subconsulta)

• El siguiente ejemplo muestra todos los libros que no hayan tenido ventas:

select titlefrom titleswhere title_id not in

(select title_idfrom sales)

Subconsultas conPrueba de Existencia

• Las subconsultas con el operador EXIST verifican la existencia de algo.

• La cláusula WHERE de la consulta externa comprueba la existencia de las filas retornadas por la subconsulta.

• La subconsulta no produce un dato, sino que devuelve el valor TRUE o FALSE.

• Tiene la siguiente sintaxis:

WHERE [NOT] EXISTS (subconsulta)

Subconsultas conPrueba de Existencia

• Estas subconsultas difieren de las anteriores en que:

– La palabra EXISTS no se precede de alguna expresión.

– La lista de selección en la subconsulta puede tener una columna o un asterisco en la lista select. No tendría sentido incluir una lista de columnas, porque este tipo de consultas sólo verifica si existe o no un renglón y no retorna algún dato.

• Este tipo de consulta es útil porque hay ocasiones en que no existe otra alternativa de expresar la consulta.

• Aunque algunas consultas formuladas con EXISTS no se pueden expresar de otra forma, todas las consultas que usan IN u operadores relacionales con modificadores se pueden expresar con EXISTS.

Subconsultas conPrueba de Existencia

• Las subconsultas con EXISTS y NOT EXISTS son equivalentes a las operaciones de álgebra relacional INTERSECCIÓN y DIFERENCIA respectivamente.

• Ejemplo: para encontrar todas las editoriales que publican libros de administración se podría hacer lo siguiente:

select distinct pub_name from publishers where exists

(select 1 from titles where pub_id = publishers.pub_id

and type = 'business')

• Se puede comprobar que se puede cambiar el 1 por asterisco, titles o ‘CadenaX’

Subconsultas en Otras Expresiones

• Ya que una subconsulta se puede usar donde se pueda indicar una expresión, también se puede usar en las cláusulas SELECT, UPDATE, INSERT Y DELETE, excepto en un ORDER BY.

• Por ejemplo, la siguiente consulta obtiene los títulos, precios, promedio de precio y diferencia entre el promedio y el precio de todos los títulos:

select title, price, (select avg(price) from titles) as promedio,(select avg(price) from titles) -

price as diferenciafrom titles

Joins o Subconsultas

select distinct pub_name

from publishers, authors

where publishers.city = authors.city

Yselect pub_name

from publishers

where city in

(select city from authors)

• Retornan el mismo resultado

• Pero si se desean datos de ambas tablas (publishers y authors), se debe emplear un join.

select pub_name, au_fname, au_lname

from publishers, authors

where publishers.city = authors.city

Joins o Subconsultas

select au_lname, au_fname, city

from authors

where city in

(select city

from authors

where au_fname = 'Dean'

and au_lname = 'Straight')

Puede ser expresada también como

select a1.au_lname, a1.au_fname, a1.city

from authors a1, authors a2

where a1.city = a2.city and a2.au_fname = 'Dean'

and a2.au_lname = 'Straight'

• Este self-join obtiene los autores que viven en la misma ciudad que Dean

Joins o Subconsultas

• Usar joins o subconsultas es generalmente una cuestión de gusto

• La mayoría de los joins pueden ser expresados como subconsultas y viceversa.

• El cálculo, las funciones de agregado y el uso de estas en el criterio de selección es una ventaja de las subconsultas.

select title, price from titleswhere price =

(select min(price) from titles)

• El despliegue de datos desde múltiples tablas es hecho mediante un join.

• Una subconsulta puede contener otra subconsulta.

• En efecto se pueden anidar tantos niveles como se requieran. Sin embargo, para la mayoría de las aplicaciones mas de cuatro niveles es un indicativo de un diseño de base de datos incorrecto.

select au_lname, au_fname from authors where au_id in

(select au_id from titleauthor where title_id in

(select title_id from titles where type = 'popular_comp'))

• Esta consulta retorna la lista de autores que han escrito al menos un libro popular de computación.

Subconsultas Anidadas

Resumen Unidad 5

• Las subconsultas son instrucciones SQL anidadas.

• Existen dos modos de subconsultas: correlativas y no correlativas.

• También existen tres tipos: – retornan exactamente un valor,

– retornan cero o más valores, o

– verifican la existencia.

• La mayoría de los joins pueden ser escritos como subconsultas y viceversa.

• Una subconsulta es usada cuando se requiere incluir un cálculo en la condición.

Resumen Unidad 5

• Un join es usado cuando se requieren extraer datos de más de una tabla.

• All significa cada valor (mayor que cada valor).

• Any y Some significan al menos un valor (mayor que al menos un valor).

• Exists permite verificar si existe un valor.

• Las consultas con exists son usadas con subconsultas correlacionadas.

• Las subconsultas pueden estar anidadas cualquier número de niveles.

Ejercicios

1. Usando una subconsulta, seleccionar las editoriales que publican libros de ‘business’. Y compararla con el join de la unidad anterior.

2. Usando una subconsulta, seleccionar el número de autores que escribieron libros de ‘business’. Y compararla con el join de la unidad anterior.

3. Mostrar todos los títulos publicados por Algodata Infosystems

4. Usando un exists, encontrar todas las editoriales que publiquen libros de business.