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.

Guia do artigo:

• Visão geral
• Sintaxe
• Exemplos

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.

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.

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 colina 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

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).

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.

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

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).

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.

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_projetoa
  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.

Confira também

Eu preciso aprender SQL?

Vídeo

Você usa Triggers?

Vídeo

Avançando no comando SQL Select

Curso

MySQL

Curso

Update Triggers

Vídeo

Curso de SQL

Curso