Esta variação da instrução SELECT permite que uma outra subconsulta do tipo SELECT seja utilizada como uma coluna da consulta principal, permitindo a construção de comandos SQL mais flexíveis, suportando o uso de funções agregadoras.

Visão geral

Essa abordagem de subquery é capaz de trazer informações que foram coletadas de outras consultas (sejam em outras tabelas ou até em resultados) utilizando funções agregadas e exibi-las como uma nova coluna, o que não é possível utilizando a prática de JOINS, pois a função agregada precisa agir em um todo, e não registro a registro, como acontece com junções.

Uma casa de festas planeja aumentar seu espaço físico e para isso precisa saber se a quantidade de convidados em cada evento se aproxima do limite máximo suportado pelo lugar. Para construir este tipo de relatório contamos com o histórico de entradas armazenados em um banco de dados SQL que contém as tabelas evento (Tabela 1) e entrada_evento (Tabela 2).

id nome tipo data
101 Casamento do Thiago e Marina Casamento 2018-07-30
102 Aniversário do Pedrão Aniversario 2017-12-12
103 Aniversário da Camila Aniversario 2018-02-01
Tabela 1. Tabela evento

id idevento
15 101
16 101
17 101
18 101
19 101
20 102
21 102
22 102
23 103
24 103
25 103
26 103
27 103
28 103
Tabela 2. Tabela entrada_evento

Para construir este tipo de relatório utilizamos a consulta SQL que pode ser vista abaixo:

SELECT
      E.nome,
      E.data,
      (
         SELECT
            COUNT(id)
         FROM
            entrada_evento EV
         WHERE
            EV.idevento = E.id
      ) AS total
  FROM
      evento E
  GROUP BY
      E.ID DESC
  ORDER BY
      E.data;

Nesta consulta utilizamos o SELECT para recuperar o nome e a data de um evento nas linhas 2 e 3. Em seguida utilizamos uma subconsulta que recupera o total de entradas de um evento na tabela entrada_evento utilizando como filtro o id do evento ser igual ao id da tabelaevento entre as linhas 4 e 10 e na linha 11 utilizando o operador AS para criar um ALIAS para essa coluna, onde será retornado o número de entradas deste evento.

Em seguida, nas linhas 14 e 15 agrupamos os dados com o GROUP BY por id em ordem decrescente. Logo após finalizamos a consulta ordenando os resultados da consulta pela data de realização do evento.

O resultado gerando o resultado que pode ser visto na Tabela 3.


nome data total
Aniversário do Pedrão 2017-12-12 3
Aniversário da Camila 2018-02-01 6
Casamento do Thiago e Marina 2018-07-30 5
Tabela 3. Resultado da consulta dos eventos com uma coluna nova chamada total, exibindo o total de convidados

Sintaxe

SELECT
    [
        coluna1,
        coluna2,
        [
           SELECT
               [ coluna ]
           FROM
              [ tabela1 ]
        ] AS coluna 3
    ... | *
    ]
    FROM
  [ tabela1, tabela2, ... ]

Na prática

Exemplo 1

Como outro exemplo, considere o seguinte cenário, temos duas tabelas no banco de dados, uma chamada vendas (Tabela 4), responsável por armazenar as informações de vendas da aplicação, e outra chamada vendas_parcelas (Tabela 5), essa responsável pelo detalhamento do parcelamento das vendas.


ID ID_CLIENTE DATA VALOR
1 1 2018-06-07 1200.00
2 5 2018-05-17 800.00
3 3 2018-07-17 620.00
4 1 2018-05-18 900.00
Tabela 4. Tabela vendas

'
ID ID_VENDA NUMERO_PARCELA VALOR_PARCELA VENCIMENTO DATA_PAGEMENTO
1 1 1 600.00 2018-06-10 NULL
2 1 2 600.00 2018-07-10 NULL
3 2 1 200.00 2018-05-10 2018-05-10
4 2 2 200.00 2018-06-10 2018-06-10
5 2 3 200.00 2018-07-10 2018-07-10
6 2 4 200.00 2018-08-10 NULL
7 3 1 310.00 2018-07-10 2018-07-10
8 3 2 310.00 2018-08-10 2018-07-10
9 4 1 300.00 2018-05-20 2018-07-17
10 4 2 300.00 2018-06-20 2018-07-17
11 4 3 300.00 2018-07-20 2018-07-17
Tabela 5. Tabela vendas_parcelas

Suponhamos que seja necessário listar as informações da venda e, além disso, listar também o total de parcelas pagas de cada uma delas. Para isso, podemos utilizar a query abaixo:

SELECT
      V.ID, 
      V.VALOR,
      V.DATA,
      (SELECT 
         COUNT(P.ID_VENDA) 
       FROM 
         VENDAS_PARCELAS P 
       WHERE 
         (V.ID = P.ID_VENDA) AND
         (P.DATA_PAGAMENTO is NOT NULL)
      ) AS QTD_PARCELAS_PAGAS
  FROM
      VENDAS V

Através da query acima obtivemos o resultado ilustrado na Tabela 6.

ID VALOR DATA QTD_PARCELAS_PAGAS
1 1200.00 2018-06-07 0
2 800.00 2018-05-17 3
3 620.00 2018-07-17 2
4 900.00 2018-05-18 3
Tabela 6. Resultado da consulta na tabela de vendas, trazendo também as informações de parcelas pagas

Esse resultado foi possível porque nas linhas 1 a 4 selecionamos as colunas da tabela venda que irão vir na consulta e, logo em seguida, criamos nas linhas 5 a 10 uma coluna nova que esta vindo de uma subconsulta feita na tabela vendas_parcelas. Essa coluna foi nomeadas como QTD_PARCELAS_PAGAS

.

Na linha 5 selecionamos o total vendas da tabela vendas_parcelas, através da função agregadora COUNT e, em seguida, nas linhas 8 e 9 definimos que o ID da venda deve ser o mesmo nas duas tabelas, e a data de pagamento não pode ser nula, ou seja parcelas paga. Na linha 10 nomeamos essa consulta como a coluna QTD_PARCELAS_PAGAS..

Exemplo 2

Neste exemplo faremos como o anterior, porém, dessa vez será que necessário listar as informações da venda, o total de parcelas pagas e o total de parcelas em aberto de cada uma delas. Para isso, podemos utilizar a query abaixo:

SELECT
      V.ID, 
      V.VALOR,
      V.DATA,
      (
       SELECT 
          COUNT(P.ID_VENDA) 
       FROM 
          VENDAS_PARCELAS P 
       WHERE
          (V.ID = P.ID_VENDA) AND
          (P.DATA_PAGAMENTO is NOT NULL)
      ) AS QTD_PARCELAS_PAGAS,
      (
       SELECT 
         COUNT(P.ID_VENDA) 
       FROM 
         VENDAS_PARCELAS P 
       WHERE 
         (V.ID = P.ID_VENDA) AND
         (P.DATA_PAGAMENTO is NULL)
      ) AS QTD_PARCELAS_ABERTAS
  FROM
      VENDAS V

Através da query acima obtivemos o resultado ilustrado na Tabela 7.

ID VALOR DATA QTD_PARCELAS_PAGAS QTD_PARCELAS_ABERTAS
1 1200.00 2018-06-07 0 2
2 800.00 2018-05-17 3 1
3 620.00 2018-07-17 2 0
4 900.00 2018-05-18 3 0
Tabela 7. Resultado da consulta na tabela de vendas, trazendo também as informações de parcelas pagas

Esse resultado foi possível porque nas linhas 1 a 4 selecionamos as colunas da tabela venda que irão vir na consulta e, logo em seguida, criamos nas linhas 5 a 10 uma coluna nova que esta vindo de uma subconsulta feita na tabela vendas_parcelas. Essa coluna foi nomeadas como QTD_PARCELAS_PAGAS

.

Na linha 5 selecionamos o total vendas da tabela vendas_parcelas, através da função agregadora COUNT e, em seguida, nas linhas 8 e 9 definimos que o ID da venda deve ser o mesmo nas duas tabelas, e a data de pagamento não pode ser nula, ou seja parcelas paga. Na linha 10 nomeamos essa consulta como a coluna QTD_PARCELAS_PAGAS..

Nas linhas 10 a 16 realizamos a mesma consulta que acima, porém, solicitamos todas as parcelas onde a data de pagamento é NULA, ou seja, que ainda não esteja paga.

Exemplo 3

Para este exemplo utilizaremos a estrutura a seguir, as tabela produto (Tabela 8) e a tabela venda_produto (Tabela 9).

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
12 Bicicleta Mormai 1350.00 2
Tabela 8. Tabela produto

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

Suponhamos que seja necessário todos os produtos da tabela produto, porém, com uma nova coluna que irá trazer o valor da média geral de preço de todos os produtos. Para isso podemos utilizar a query abaixo:

SELECT
      P.id,
      P.nome,
      P.preco,
      (
           SELECT
              AVG(P2.preco)
           FROM
              produto P2
      ) as MEDIA_GLOBAL
  FROM
    produto P
  

O grande ponto da query acima esta nas Linhas 6 a 10, onde realizamos uma subquery que o resultado irá vir como uma nova coluna no resultado. Na Linha 10 definimos o nome da nova coluna como MEDIA_GLOBAL.

O resultado da query pode ser visto na Tabela 10.

ID nome preco MEDIA_GLOBAL
1 Bola 35.00 542.000000
2 Patinete 120.00 542.000000
3 Carrinho 15.00 542.000000
4 Skate 296.00 542.000000
5 Notebook 3500.00 542.000000
6 Monitor LG 19 450.00 542.000000
7 O Diário de Anne Frank 45.00 542.000000
8 O dia do Curinga 65.00 542.000000
9 O mundo de Sofia 48.00 542.000000
10 Através do Espelho 38.00 542.000000
12 Bicicleta Mormai 1350.00 542.000000
Tabela 10. Resultado da query trazendo todos os produtos e a média geral de preço
Saiba mais Veja a Série SQL nível Jedi: Subqueries