Subconsultas

De MediaWiki
Ir a la navegación Ir a la búsqueda

Introdución

Para as seguintes explicacións utilizarase:

Qué son as subconsultas

Unha subconsulta é unha sentenza SELECT contida noutra sentenza da linguaxe de manipulación de datos. Os resultados da subconsulta afectan ao resultado da sentenza que a contén. Pódese dicir que unha subconsulta está aniñada na consulta externa ou consulta principal e é posible aniñar subconsultas dentro doutras subconsultas ata unha profundidade considerable.

Nesta sección utilizaranse as subconsultas como parte dunha sentenza SELECT para resolver consultas complexas; tamén reciben o nome de consultas subordinadas.

Exemplo:

1  SELECT * FROM t1 
2  WHERE column1 = (
3                   SELECT column1 
4                   FROM t2);
 
# Esta sentenza sería a subconsulta ou consulta subordinada.
SELECT column1 FROM t2  

# Esta sentenza sería a consulta externa ou principal
SELECT * FROM t1 WHERE column1 =

Características das subconsultas

  • Aparecen pechadas entre parénteses, dentro da consulta principal.
  • Poden retornar
  • un escalar (un valor único)
  • unha táboa formada por unha ou varias columnas e unha ou varias filas ou o valor NULL (se non existe ningunha fila que cumpra as condicións da subconsulta).
  • Os nomes de columna que aparecen nunha subconsulta poden facer referencia a táboas da consulta principal. A estas referencias chámaselles 'referencias externas' na subconsulta.
  • Pódense utilizar en calquera parte da sentenza SELECT
  • aínda que o máis habitual é que formen parte dunha condición na cláusula WHERE ou na cláusula HAVING.

Vantaxes do uso de subconsultas

As vantaxes do uso de subconsultas:

  • Permite escribir consultas estruturadas de forma que é posible illar cada parte da consulta, e descompoñer un problema grande en varios pequenos.
  • Proporcionan unha maneira alternativa de resolver consultas que doutro modo necesitarían combinacións de táboas (JOIN) ou unións complexas.
  • Fan consultas máis fáciles de ler.

Tipos de subconsultas

Subconsultas como parte dunha condición

A forma máis habitual de utilización das subconsultas é formando parte dunha condición dentro das cláusulas WHERE ou HAVING.

A forma de utilizar as subconsultas e os operadores que se poden utilizar na sentenza principal para facer comparacións co resultado da subconsulta, está en función dos valores que devolve a subconsulta, que poden ser valores escalares ou táboas.

Subconsultas que devolven un escalar

Na súa forma máis simple, unha subconsulta devolve un só valor, e pode ser utilizada como operando en calquera expresión (coma se fose un literal ou unha columna) ou como parámetro dunha función. Normalmente compárase o valor que devolve a subconsulta cunha expresión mediante un operador relacional. Sintaxe:

expresión operador_relacional (sentenza SELECT escalar)

Exemplo: mostrar dni, apelidos e nome dos empregados do mesmo departamento que o empregado que ten o DNI '12852654'.

Resultado da execución
  • Paso 1: coñecer o departamento ao que pertence ese empregado. Iso pódese saber executando a consulta:
1 SELECT departamento
2 FROM empregado
3 WHERE dni = '12852654';


  • Paso 2: a consulta anterior devolve un valor escalar e pode ser utilizada como subconsulta na sentenza SELECT que permite mostrar os datos solicitados dos empregados que traballan nese departamento:
Resultado da execución
1 select dni,apelidos,nome
2 from empregado
3 where departamento = (select departamento
4 				from empregado
5 				where dni = '12852654');

Subconsultas que devolven unha táboa

As subconsultas poden devolver unha táboa cunha fila polo menos. Para a comparación coa subconsulta hai que utilizar os operadores relacionais combinados coas palabras ALL ou ANY. Sintaxe:

expresión operador_relacional {ALL | ANY} (sentencia SELECT)
Cando se utiliza a palabra ALL diante do operador relacional
a condición será verdadeira se é verdadeira para todos os valores que devolve a subconsulta.
Cando se utiliza a palabra ANY diante do operador relacional
a condición é verdadeira se é verdadeira para polo menos un dos valores que devolve a subconsulta.

Exemplo con ALL: mostrar dni, apelidos, nome e salario_bruto dos empregados que teñan un salario bruto superior ao de todos os empregados que traballan no departamento 4.

  • Paso 1: seleccionar os salarios de todos os empregados do departamento 4:
Resultado da execución
1 select salario_bruto
2 from empregado
3 where departamento = 4


  • Paso 2: a consulta anterior devolve unha táboa que ten varias filas, e unha columna cos salarios de todos os empregados do departamento 4. A sentenza SELECT que hai que construír ten que comparar o salario de cada empregado con todos os valores que devolve a subconsulta, e seleccionar só os que teñen un salario bruto maior que todos eses valores.
Resultado da execución
1 select dni,apelidos,nome,salario_bruto
2 from empregado
3 where salario_bruto > ALL (select salario_bruto
4 					from empregado
5 					where departamento = 4);


Unha solución alternativa para resolver esta consulta consiste en calcular o salario bruto máis alto dos empregados do departamento 4, que sería un valor escalar, e despois comparar o salario bruto de cada empregado con ese valor escalar utilizando un operador relacional.

  • Paso 1: calcular o salario máis alto dos empregados do departamento 4.
Resultado da execución
1 select max(salario_bruto)
2 from empregado
3 where departamento = 4


  • Paso 2: o resultado da consulta é un valor escalar e pódese comparar co operador relacional 'maior que' (>) .
1 select dni,apelidos, nome, salario_bruto
2 from empregado
3 where salario_bruto > (select max(salario_bruto)
4 				from empregado
5 				where departamento = 4);

Exemplo con ANY: mostrar dni, apelidos, nome, salario_bruto e departamento dos empregados que traballan nalgún departamento da provincia de Lugo (id_provincia = 27).

  • Paso 1: Seleccionamos os códigos dos departamentos que están situados na provincia de Lugo.
Resultado da execución
1 select codigo
2 from departamento
3 where id_provincia = '27';


  • Paso 2: na sentenza principal hai que comparar os departamentos de cada empregado dos valores que devolve a subconsulta, e seleccionar só aqueles que teñan un departamento igual a algún dos valores que devolve a subconsulta.
Resultado da execución
1 select dni,apelidos, nome, salario_bruto, departamento
2 from empregado
3 where departamento = ANY (select codigo
4 					from departamento
5 					where id_provincia = '27');

Utilizando o predicado BETWEEN

Pódese utilizar o predicado BETWEEN con subconsultas que devolven un escalar. Serve para comparar unha expresión cun intervalo de valores que se obteñen dunha ou dúas subconsultas.

Exemplo: mostrar dni, apelidos, nome, departamento e salario_bruto dos empregados que teñan un salario bruto entre o salario máis alto do departamento 2 e o salario máis alto do departamento 8.

  • Paso 1: calcular o salario máis alto dos empregados do departamento 2.
Resultado da execución
1 select max(salario_bruto)
2 from empregado
3 where departamento = 2;
  • Paso 2: calcular o salario máis alto dos empregados do departamento 8.
Resultado da execución
1 select max(salario_bruto)
2 from empregado
3 where departamento = 8;
  • Paso 3: subconsulta final utilizando as dúas anteriores.
Resultado da execución
1 select dni,apelidos,nome,departamento,salario_bruto
2 from empregado
3 where salario_bruto between 	(select max(salario_bruto)
4 					from empregado
5 					where departamento = 2)
6                     and	(select max(salario_bruto)
7 				from empregado
8 				where departamento = 8);

Utilizando o predicado IN

O predicado IN utilízase cando a subconsulta devolve unha táboa cunha ou máis filas e permite comparar o valor que corresponde a unha expresión cos valores que devolve a subconsulta. Se de antemán se sabe que a subconsulta vai devolver unha táboa dunha fila, non é recomendable utilizar este predicado e deberíanse de utilizar operadores relacionais.

Sintaxe:
expresión [ NOT ] IN (sentenza SELECT)
  • A condición é verdadeira cando o valor que representa a expresión é igual a algún dos valores que devolve a subconsulta.
  • Cando se utiliza o operador NOT diante, a condición é verdadeira cando o valor que representa a expresión é distinto de todos os valores que devolve a subconsulta. No caso que na lista de valores estea incluído o valor NULL hai que ter en conta que a comparación de calquera valor co valor NULL devolve NULL e non true ou false, o que pode provocar problemas e obter resultados que non son correctos.
  • Cando comparamos o valor dunha expresión cos valores que devolve unha subconsulta que produce como resultado unha táboa de máis dunha fila:
  • IN equivale a unha condición = ANY
  • NOT IN equivale a unha condición != ALL

Exemplo: mostrar dni, apelidos, nome, salario_bruto e departamento dos empregados que traballan nalgún departamento que non estea situado na provincia de Lugo.

  • Paso 1: seleccionar os códigos dos departamentos que están situados na provincia de Lugo.
Archivo:Res exec 11.png
Resultado da execución
1 select codigo
2 from departamento
3 where id_provincia = '27';
  • Paso 2: na sentenza principal hai que comparar o departamento de cada empregado cos valores que devolve a subconsulta, e seleccionar só aqueles que teñan un departamento distinto a algún dos valores que devolve a subconsulta.
Resultado da execución
1 select dni,apelidos, nome, salario_bruto, departamento
2 from empregado
3 where departamento NOT IN (select codigo
4 				 from departamento
5 				 where id_provincia = '27');
  • Tarefa 3. Realizar consultas que utilicen o predicado IN combinado con subconsultas dentro dunha condición.

Utilizando o predicado EXISTS

Permite comprobar se a subconsulta devolve algunha fila ou non.

Os valores que devolve a subconsulta non se comparan con ningún outro valor.

Sintaxe:
[ NOT ] EXISTS (sentencia SELECT)
  • A condición:
  • é verdadeira se a subconsulta devolve unha o máis filas
  • é falsa se a subconsulta non devolve ningunha fila

Para que este tipo de condición teña utilidade, a subconsulta debe conter algunha condición que devolva resultados diferentes para cada fila da táboa da sentenza principal. Isto supón que nalgunha condición da subconsulta ten que intervir algunha columna das táboas da consulta principal ('referencias externas'); posto que se isto non fose así, a condición sería verdadeira ou falsa para todas as filas da consulta principal.

Exemplo: mostrar dni, apelidos, nome, salario_bruto e departamento dos compañeiros de departamento do empregados con DNI 33456345, que teñan un salario maior có seu.

1 select dni,apelidos,nome,salario_bruto,departamento
2 from empregado as emp1
3 where exists ( select emp2.departamento
4 				from empregado as emp2
5 				where emp2.dni = '33845215'
6 				and emp1.departamento = emp2.departamento
7 				and emp1.salario_bruto > emp2.salario_bruto);
  • Tarefa 4. Realizar consultas que utilicen o predicado EXISTS combinado con subconsultas dentro dunha condición.

Subconsultas que non forman parte dunha condición

As subconsultas non só se utilizan como parte dunha condición nas cláusulas WHERE ou HAVING, se non que poden aparecer noutras partes dunha instrución SELECT. Exemplo: mostrar os salarios brutos de todos os empregados e a diferenza entre o seu salario e o salario medio de todos os empregados que teñan como xefe ao empregado con DNI 33252141.

1 select dni, apelidos, nome, salario_bruto,
2 	round(salario_bruto-(select avg(salario_bruto) from empregado),2) as diferenza
3 from (select dni, apelidos, nome, salario_bruto 
4 		from empregado 
5 		where dni_xefe ='33456852') as xefe_33456852
6 order by diferenza;

Créditos e referencias

  • Materiales elaborados por Marta Fernández García, María del Carmen Fernández Lameiro, Miguel Fraga Vila, María Carmen Pato González e Andrés del Río Rodríguez durante unha licenza de formación retribuída pola Consellería de Cultura, Educación e Ordenación Universitaria e ten licenza CreativeCommons BY-NC-SA (recoñecemento - non comercial - compartir igual). Para ver unha copia desta licenza, visitar a ligazón http://creativecommons.org/licenses/by-nc-sa/3.0/es/.
  • ANY/ALL en w3schools
  • subconsultas

Boletín