Artigos Banco de Dados SQL: SUBQUERY

Uma subconsulta (mais conhecida como SUBQUERY ou SUBSELECT) é uma instrução do tipo SELECT dentro de outra instrução SQL. Desta forma, se torna possível efetuar consultas que de outra forma seriam extremamente complicadas ou impossíveis de serem feitas de outra forma.

Visão geral

Os exemplo de subquery foram dividos em sessões diferentes, podendo ser vistos também em SELECT AS FIELD e SELECT FROM SELECT, que são formas de realizar subqueries.

Para este exemplo, considere a estrutura de tabelas abaixo, onde temos uma tabela chamada produto (Tabela 1), responsável por armazenar todos os produtos do estoque do cliente, a tabela categoria_produto (Tabela 2), responsável por armazenar todas as categorias existentes no banco de dados, e a tabela venda_produto (Tabela 3), que relaciona os produtos que foram vendidos.

id nome preco Id_categoria
1 Bola 35.00 1
2 Patinete 120.00 1
3 Carrinho 15.00 1
4 Skate 296.00 1
5 Notebook 3500.00 2
6 Monitor LG 19 450.00 2
7 O Diário de Anne Frank 45.00 3
8 O dia do Curinga 65.00 3
9 O mundo de Sofia 48.00 3
10 Através do Espelho 38.00 3
Tabela 1. Tabela produto

id nome
1 Infantil
2 Informatica
3 Educacional
Tabela 2. Tabela categoria_produto

id id_produto valor data
1 1 35.00 2018-05-15
2 1 35.00 2018-06-15
3 1 35.00 2018-07-15
4 2 120.00 2018-07-15
5 2 120.00 2018-07-14
6 3 15.00 2018-07-15
7 7 45.00 2018-07-15
8 8 65.00 2018-07-15
9 8 65.00 2018-07-16
10 9 48.00 2018-07-16
11 5 3500.00 2018-07-16
12 5 3500.00 2018-07-16
13 6 450.00 2018-07-16
Tabela 3. Tabela venda_produto

Suponha que seja necessário listar da tabela produtos, todos os registros que tenham um preço acima da média dos outros produtos. Para isso podemos utilizar o trecho de código abaixo:

SELECT
    nome,
    preco
FROM 
    Produto 
WHERE
    preco > (
SELECT 
    AVG(preco) 
FROM 
    produto
)

No trecho de código acima, nas linhas 1 a 3 informamos que as colunas nome e preco serão trazidas na consulta, e em seguida, nas linhas 4 e 5 informamos que a consulta será realizada na tabela produto.

Nas linhas 6 a 11 informamos que serão trazidos somente resultados que o valor da coluna preco seja maior que o resultado do select da linha 8 que é responsável por trazer o valor da média de preco da tabela produto.

O resultado da query pode ser visto naTabela 4.

nome preco
Notebook 3500.00
Tabela 4. Resultado da consulta do produtos com preço acima da média dos outros produtos

Sintaxe

SELECT
 [ coluna1, coluna2, … | subquery ] AS [ coluna3 ]
 FROM
 [ tabela | subquery AS tabela]
 WHERE
 [ condicao1,  condicao2, … |  coluna ] = [ subquery ]
[ coluna ] IN [ subquery ]

Na prática

Exemplo 1

Neste exemplo iremos realizar uma consulta com base no resultado de outra consulta.

Suponha que seja necessário saber de todos os produtos qual foi a sua quantidade vendida, e logo após, desse resultado será necessário saber qual o produto que teve a maior quantidade de itens vendidos. Para isso podemos utilizar a query abaixo:

SELECT
    id,
    Produto,
    MAX(TOTAL_VENDIDO) as MAIOR
FROM (
        SELECT
            P.id,
            P.nome as Produto,
            (SELECT
                COUNT(VP.id_produto)
                FROM 
                    venda_produto VP
                WHERE
                    P.id = VP.id_produto
                GROUP BY
                    P.id
            ) as TOTAL_VENDIDO
        FROM
            produto P
        GROUP BY
            P.id
) as Tabela

Observe que no trecho de código acima, fizemos um SELECT em outro SELECT. Nas linhas 2 a 4 selecionamos as colunas que irão vir na consulta. Observe que a linha 4 solicita o valor máximo da coluna TOTAL_VENDIDO.

Nas linhas 5 a 19 definimos que a consulta será feita (FROM) em um outro SELECT. Na linha 8 solicitamos que seja feita a contagem de id_produto presente na tabela venda_produto, e logo no fim pedimos para agrupar o resultado por id do produto, e na linha 15 definimos que essa coluna se chamará TOTAL_VENDIDO

Na linha 19 atribuímos um ALIAS para a consulta que esta sendo utilizada como tabela.

O resultado da query pode ser visto abaixo na Tabela 5

Id produto maior
1 Bola 3
Tabela 5. Resultado da busca pelo maior valor entre os produtos vendidos

Exemplo 2

Para este exemplo considere o seguinte cenário:

A empresa AT&F possui uma tabela no banco de dados para armazenar todos os seus gerentes (Tabela 6), e outra tabela para armazenar todos os supervisores (Tabela 7).

Id nome departamento data_admissao
1 Enrique Santos Tecnologia 2008-09-01
2 Nadia Cristina Contratos 2007-05-01
Tabela 6. Tabela gerente

Id nome departamento data_admissao
1 Marcus Vinicius Administracao 2012-05-05
2 Ana Clara Recepcao 2015-04-01
3 Kelly Cristina Faculdade 2015-01-01
4 Michele Francisca Faculdade 2017-01-01
5 Ana Flavia Comercial 2017-02-01
6 Sarah Camila Comercial 2017-01-01
Tabela 7. Tabela supervisor

A empresa AT&F decidiu promover a gerente todos os supervisores que foram contratados antes de 2016, e solicitou que todos esses funcionários fossem adicionados na tabela gerente. Para isso, podemos utilizar o seguinte trecho de código: 

INSERT INTO
  gerente(nome, departamento, data_admissao)
      (SELECT
          nome,
          departamento,
          data_admissao
      FROM
          supervisor
      WHERE
          YEAR(data_admissao) < 2016
)

No trecho de código acima nas linhas 1 e 2 informamos que a inserção dos dados será feita na tabela gerente, e que serão preenchidas as colunas nome, departamento e data_admissao.

Em seguida, na linha 3 informamos que os dados para essa inserção serão obtidos através de uma outra consulta.

Nas linhas 3 a 8 realizamos essa segunda query, solicitando para o banco de dados todos os funcionários da tabela supervisor (linha 8) que tenham o ano da data de admissão menor que 2016 (linha 10).

Após a execução dessa query, todos esses dados da tabela supervisor foram adicionados a tabela gerente, que pode ser vista na Tabela 8.

Id nome departamento data_admissao
1 Enrique Santos Tecnologia 2008-09-01
2 Nadia Cristina Contratos 2007-05-01
3 Marcus Vinicius Administracao 2012-05-05
4 Ana Clara Recepcao 2015-04-01
5 Kelly Cristina Faculdade 2015-01-01
Tabela 8. Tabela gerente com os supervisores promovidos

Exemplo 3

Para este exemplo utilizaremos a mesma estrutura de tabelas que foi utilizada no exemplo anterior, as tabelas gerente (Tabela 6) e supervisor (Tabela 7).

Com a execução da query do exemplo 3, todos os supervisores que foram promovidos a gerente foram para a tabela de gerente, porém, continuam na tabela de supervisor. Para resolver este problema podemos utilizar mais um exemplo de subquery, esse em conjunto ao comando DELETE. Para isso podemos utilizar a query abaixo: 

DELETE FROM supervisor
WHERE
 nome IN (SELECT
            nome
          FROM
            gerente G
          WHERE 
            G.nome = nome
);

Observe que na query acima, acionamos o comando DELETE na tabela de supervisor com a condição de o nome do supervisor estar no resultado de outra consulta. O resultado da query acima pode ser visto na Tabela 8

Id nome departamento data_admissao
4 Michele Francisca Faculdade 2017-01-01
5 Ana Flavia Comercial 2017-02-01
6 Sarah Camila Comercial 2017-01-01
Tabela 8. Tabela supervisor sem os recém promovidos a gerente

Observe que no resultado acima, os registros que foram promovidos a gerente já não fazem mais parte dessa tabela, afinal foram removidos.

O comando INSERT em conjunto a outra query só irá funcionar se as duas tabelas possuírem a mesma quantidade e configuração de colunas.

Exemplo 4

Para este exemplo utilizaremos a estrutura a seguir, as tabelas projetos (Tabela 9) e comentario (Tabela 10).

ID titulo data url
7 Criando uma aplicação com React 2018-04-10 https://www.devmedia.com.br/exemplo/api-rest-react-mobile-aplicacao-completa-gotour/78
8 API RestFUL com Lumen 2018-05-10 https://www.devmedia.com.br/exemplo/criando-uma-api-restful-com-lumen/71
9 Consumindo uma API RestFUL com Laravel 2018-05-20 https://www.devmedia.com.br/exemplo/projeto-gotour-cliente-web-para-a-api-de-moderacao-de-uma-agencia-de-turismo/72
10 Documentação SQL 2018-05-21 https://www.devmedia.com.br/exemplo/documentacao-sql/76
Tabela 9. Tabela projetos

ID comentario data id_projeto id_usuario
1 Muito legal essa aplicação! Adorei usar o React 10/04/2018 7 1
2 React é muito simples! Curti o projeto! parabéns! 10/05/2018 7 3
3 Muito top! 20/05/2018 9 1
4 Parabéns aos envolvidos! 20/05/2018 9 2
5 Muito legal, sempre gostei do Laravel. 20/05/2018 9 3
6 Interessante a forma de recueprar as informações. Não sabia. Gostei! 21/05/2018 9 4
7 Consigo fazer o mesmo com CodeIgniter? 22/05/2018 9 5
8 React é apenas JavaScript, há uma API bem pequena para aprender, apenas algumas funções e como usá-las. Depois disso, suas habilidades em JavaScript serão o que te farão um melhor desenvolvedor React 23/05/2018 7 6
Tabela 10. Tabela comentario

Para selecionar todos os projetos que tenham algum comentário, podemos utilizar a query abaixo:

SELECT
  P.*
FROM 
  projetos P
WHERE 
    P.id IN 
(
  SELECT
    C.id_projeto
  FROM 
    comentario C
  WHERE 
    C.id_projeto = P.id
)

Observe no trecho de código acima, que na Linha 6 encontramos uma cláusula IN que verifica se a coluna P.id está contida na subquery definida nas Linhas 8 a 13, que verifica todos os comentários presentes na tabela de comentários. Observe que na Linha 13 existe um vínculo entre o ID do projeto e o ID do projeto dentro de comentários, o que otimiza a consulta.

Veja o resultado da query ilustrado na Tabela 11.

ID titulo data url
7 Criando uma aplicação com React 2018-04-10 https://www.devmedia.com.br/exemplo/api-rest-react-mobile-aplicacao-completa-gotour/78
9 Consumindo uma API RestFUL com Laravel 2018-05-20 https://www.devmedia.com.br/exemplo/projeto-gotour-cliente-web-para-a-api-de-moderacao-de-uma-agencia-de-turismo/72
Tabela 11. Resultado da query trazendo todos os projetos com comentários

Exemplo 5

Podemos agora, com a mesma estrutura de tabelas do exemplo anterior (Tabela 9 e Tabela 10), trazer todos os projetos que não possuam nenhum comentário vinculado. Para isso, podemos utilizar a query abaixo:

SELECT
  P.*
FROM 
  projetos P
WHERE 
    P.id NOT IN 
(
  SELECT
    C.id_projeto
  FROM 
    comentario C
  WHERE 
    C.id_projeto = P.id
)

Da mesma forma que o exemplo anterior, na Linha 6 temos a utilização de uma cláusula para filtro na subquery, porém, dessa vez foi a cláusula NOT IN, que faz exatamente o contrário, trazendo tudo aqui que não esta incluso no resultado da subquery.

Veja o resultado da query na Tabela 12.

ID titulo data url
8 API RestFUL com Lumen 2018-05-10 https://www.devmedia.com.br/exemplo/criando-uma-api-restful-com-lumen/71
10 Documentação SQL 2018-05-21 https://www.devmedia.com.br/exemplo/documentacao-sql/76
Tabela 12. Resultado da query trazendo todos os projetos sem comentários