SubQuery

Esse é um recurso avançado e extremamente útil do comando SELECT. Na prática, será colocado um SELECT dentro de outro. Isso quer dizer que serão colocados vários SELECTs internos. Subqueries faz parte do padrão SQL-86, logo todos os bancos de dados relacionados que utilizam SQL devem permitir essa utilização.

Há três tipos de subquery:

• De uma linha: O retorno do SELECT interno será uma única linha.
• Subquery de múltiplas linhas: O retorno do SELECT interno será mais de uma linha.
• Subquery de múltiplas colunas: O retorno do SELECT interno conterá mais de uma linha e coluna.

Para realizar subqueries tenha em mente:

• Coloque as subqueries entre parêntese.
• Coloque a subquery à direita do operador.
• Não coloque a cláusula ORDER BY em uma subquery
• Utilize operadores de linha apenas em buscas que retornem uma única linha.
• utilize operadores de grupo apenas em buscas que potencialmente retornem mais de uma linha. 

Subquery de uma linha

Neste caso, o resultado do SELECT mais interno servirá de base para o primeiro SELECT. Ao utilizarmos essa forma de busca, poderemos até melhorar o desempenho do banco de dados. Uma alternativa para essa forma de realizar a busca seria fazer uma União regular de tabelas. Dependendo do que queremos buscar nas tabelas, esse comando é mais indicado em virtude da velocidade. Note que fazendo a união regular de tabelas, estamos combinando todas as linhas de ambas as tabelas. Primeiro é feita a busca em uma tabela e, com base no resultado, pesquisada a outra tabela. Veja um exemplo:

SELECT NOME_CD, PRECO_VENDA FROM CD

WHERE PRECO_VENDA > (SELECT AVG(PRECO_VENDA) FROM CD);

Aqui é utilizada uma subquery para verificar a média do preço e, com base nesse resultado, é extraído o resultado da busca do primeiro SELECT (nome e preço). Só aparecerá no resultado os registros que o preço seja maior que a média.

SELECT CODIGO_GRAVADORA, NOME_CD, PRECO_VENDA FROM CD a

WHERE PRECO_VENDA > (SELECT AVG(PRECO_VENDA) FROM CD

WHERE CODIGO_GRAVADORA = a.CODIGO_GRAVADORA);

O segundo SELECT retorna a média de preço de venda dos CDs da gravadora do primeiro SELECT e somente os que tiverem preço de venda maiores que essa média é que aparecerão na listagem.

Subquery de múltiplas colunas

A primeira técnica consiste em colocar as colunas unidas na cláusula WHERE do SELECT externo e realizar a busca no SELECT interno dessas colunas unidas da mesma forma. Essa técnica tende a ser muito lenta, mas pode ser feita em qualquer banco de dados.

A utilização é muito parecida com as visões. Uma subquery na cláusula FROM define a fonte de dados para esse SELECT em particular. Imagine que desejemos extrair o nome do CD, o seu preço de venda e o preço médio da gravadora, e o preço médio da gravadora é extraído por uma subquery na própria cláusula FROM.

select a.nome_cd, a.preco_venda, b.avg from cd a,

(select codigo_gravadora, avg(preco_venda) from cd

group by codigo_gravadora) b

where a.codigo_gravadora = b.codigo_gravadora and

a.preco_venda > b.avg

Subquery de múltiplas linhas

Neste caso, o SELECT interno retorna mais de uma linha. Não se pode utilizar operadores simples como igualdade, diferença, maior ou menor. Deve-se utilizar um operador de grupo para realizar a comparação. Esses operadores são ANY, ALL e IN.

IN

Imagine que gostaríamos de saber quais CDs têm o preço igual ao menor preço de cada gravadora. Inicialmente devemos saber qual o menor preço de cada gravadora. Isso pode ser feito com o comando:

Select min(preco_venda) from CD Group by Codigo_gravadora

Select codigo_cd, nome_cd, preco_venda from cd Where preco_venda in (15,9,10.5)

Para saber quais são os CDs com esses preço, poderíamos escrever o seguinte comando:

Para fazer isso tudo em um único comando, escrevemos o comando da seguinte forma:

select codigo_cd, nome_cd, preco_venda from cd

where preco_venda in (select min(preco_venda) from cd

group by codigo_gravadora)

Uma outra situação em que poderíamos utilizar esse operador é quando queremos saber em qual CD e faixa há músicas que tenham AMOR no nome. Para isso, temos que utilizar as linhas da tabela MUSICA e compará-las com AMOR. Não esquecer de colocar a função UPPER do nome do campo pois, para efetuar a comparação com sucesso, devemos ter certeza de que o conteúdo da coluna esteja em letras maiúsculas, uma vez que escrevemos AMOR em letras maiúsculas. Veja a seguir:

Select codigo_musica from musica

Where upper(nome_musica) like '%AMOR%';

Sabendo quais são os código da música, podemos realizar uma busca na tabela FAIXA que nos informe em quais CDs e faixas estão essas músicas. Assim:

Select codigo_cd, numero_faixa from faixa

Where codigo_musica in (25,2)

Da mesma forma, podemos simplificar o comando realizando tudo isso em uma única operação:

Select codigo_cd, numero_faixa from faixa

Where codigo_musica in (select codigo_musica from musica

where upper(nome_musica) like '%AMOR%')

ANY

Esse operador permite comparar operadores simples (=, >, <, !=) com um grupo de linhas. Será feita a comparação com cada valor retornado do SELECT interno.

Imagine que desejamos saber quais CDs têm preço inferior a qualquer outro da gravadora com código 2, mas que não sejam da gravadora 2. E pesquisando na tabela CD para comparar os preços inferiores a esses

select codigo_cd, nome_cd, preco_venda from cd

where preco_venda < any(select preco_venda from cd

where codigo_gravadora = 2) and codigo_gravadora !=2

Podemos ainda utilizar o mesmo operador any com os outros operadores simples, veja o exemplo a seguir:

select codigo_cd, nome_cd, preco_venda from cd

where preco_venda = any(select preco_venda from cd

where codigo_gravadora = 2)

Observe neste exemplo não existe mais a condição do código da gravadora ser diferente de 2, caso contrário, não existiriam linhas de retorno.

ALL

Este operador é utilizado em combinação com operadores simples (>,<) para que os valores retornados de todas as linhas do SELECT interno sejam comparados com o SELECT externo.

Imagine que queiramos saber quais CDs têm o preço de venda menor que a média de preço de venda de todas as gravadoras. Inicialmente é necessário saber a média de preços por gravadora:

 

Select avg(preco_venda) from cd Group by codigo_gravadora

Depois de verificarmos quais são os CDs com preço inferior a todos anteriores. O comando seria o seguinte:

 

Select codigo_cd, nome_cd, preco_venda from cd

Where preco_venda < all (array[15,11,11.75])

 

Em um único comando teríamos:

 

select codigo_cd, nome_cd, preco_venda from cd

where preco_venda < all(select avg(preco_venda) from cd

group by codigo_gravadora)