Praticando SQL: entendendo subconsultas

Subconsultas - Entendendo Subconsultas

Olá, eu sou Danielle Oliveira, instrutora da Escola de Dados, e vou acompanhar você neste curso sobre subconsultas na linguagem SQL.

Audiodescrição: Danielle se identifica como uma mulher de pele morena. Possui cabelos cacheados castanhos e olhos também castanhos. Está sentada em uma cadeira e no corpo, veste uma camiseta preta. No fundo, há uma parede branca iluminada em tons de verde e azul, com duas pequenas prateleiras decoradas com itens que representam o Nordeste.

Vamos relembrar e conhecer algumas formas de utilizar subconsultas no nosso dia a dia, ao montar relatórios.

Compreendendo os tipos de subconsultas

Subconsultas são consultas alinhadas dentro de outras consultas. Podemos utilizar subconsultas nas seguintes cláusulas:

• SELECT, como se fosse um campo;
• FROM, como se fosse uma tabela virtual, para buscar informações;
• WHERE, para aplicar um filtro nos dados que estamos retornando.

Também temos as subconsultas correlacionadas, que referenciam colunas da consulta externa para cada linha processada. Por fim, temos as subconsultas com os operadores EXISTS e NOT EXISTS, que verificam se informações existem ou não, aplicando filtros nos dados.

Vamos revisitar alguns exemplos de como aplicar esses tipos de consultas.

Subconsultas na cláusula SELECT

Iniciaremos utilizando subconsultas na cláusula SELECT. Ao utilizar esse tipo de consulta, retornamos valores adicionais baseados em cálculos ou condições definidos pela subconsulta. Utilizaremos a subconsulta como um campo, que será retornado no resultado.

Vamos abordar o exemplo abaixo:

SELECT  
    nome,  
    (SELECT  
        count(*)  
     FROM vendas vd  
     WHERE vd.vendedor_id = v.id) AS total_vendas  
FROM vendedores v;

Nele, queremos retornar o nome da pessoa vendedora e sua quantidade de vendas. Na consulta mais externa, especificamos o nome relacionado à tabela de pessoas vendedoras, que está no FROM mais externo. Dentro do SELECT, criamos uma subconsulta que faz o cálculo, contando a quantidade de vendas da pessoa vendedora.

Especificamos SELECT (a subconsulta), COUNT para realizar a contagem na tabela de vendas, e utilizamos o alias (apelido) vd para a tabela. Na cláusula WHERE, definimos que isso seja feito onde o vendedor_id da tabela de vendas for igual ao v.id (ID da pessoa vendedora) na tabela de pessoas vendedoras. Aplicamos esse filtro para contar de acordo com o ID da pessoa vendedora e fornecemos um alias para a subconsulta, que é total_vendas.

O resultado é o seguinte:

Tabela de pessoas vendedoras:

id	nome	departamento_id
1	'Carlos Andrade'	2
2	'Julia Martins'	2
3	'Marcos Souza'	1
4	'Ana Carolina'	3

Na tabela de pessoas vendedoras, temos "id", "nome" e "departamento_id". Temos quatro pessoas vendedoras, com IDs de 1 a 4.

Tabela de vendas:

id	vendedor_id	data_venda	valor
1	1	'2023-01-10'	500.00
2	2	'2023-01-11'	1500.00
3	1	'2023-02-15'	700.00
4	1	'2023-02-20'	300.00
5	3	'2023-03-05'	1200.00

Na tabela de vendas, a pessoa vendedora 1 tem 3 vendas, a pessoa vendedora 2 tem 1, a pessoa vendedora 3 tem 1, e a pessoa vendedora 4 não tem vendas.

Resultado da consulta:

nome	total_venda
'Carlos Andrade'	3
'Julia Martins'	1
'Marcos Souza'	1
'Ana Carolina'	0

O resultado da consulta com subconsulta é:

• Pessoa vendedora 1 com 3 vendas;
• Pessoas vendedoras 2 e 3 com 2 vendas
• Pessoa vendedora 4, sem vendas.

Utilizando subconsultas na cláusula FROM

Além disso, podemos utilizar a subconsulta na cláusula FROM, como uma fonte de dados temporária para a consulta externa. Em vez de especificar uma tabela real do banco de dados, utilizamos uma subconsulta que executa outra consulta como tabela.

Vamos abordar o seguinte exemplo:

SELECT  
    avg(salario) as média  
FROM (  
    SELECT  
        salario  
    FROM pessoas colaboradoras  
    WHERE departamento_id = 1  
) AS salarios_dept;

No SELECT principal, selecionamos a média dos salários das pessoas colaboradoras. No FROM, especificamos a subconsulta que calcula os salários. No outro SELECT, buscamos o campo, utilizando o FROM na tabela de pessoas colaboradoras e aplicando um filtro para a média dos salários onde o departamento é igual a 1. Retornamos apenas a média do departamento com ID igual a 1.

No FROM, filtramos para retornar apenas os salários das pessoas colaboradoras do departamento 1, e na consulta externa, aplicamos a função que calcula a média.

O resultado é o seguinte:

Tabela de pessoas colaboradoras:

id	nome	departamento_id	valor
1	'Alice Santos'	1	7000.00
2	'Bob Smith'	2	4500.00
3	'Charlie Oliveira'	1	5000.00
4	'Diana Silva'	3	4000.00

Na tabela de pessoas colaboradoras, temos quatro integrantes, sendo duas do departamento 1: Alice Santos e Charlie Oliveira.

Resultado da consulta:

Média
6000.00

O resultado é a soma dos dois salários, dividida por 2, resultando em 6 mil reais.

Aplicando subconsultas na cláusula WHERE

Outra forma é utilizar subconsultas na cláusula WHERE para aplicar filtros nos resultados, baseados no resultado da subconsulta.

Vamos abordar o seguinte exemplo:

SELECT  
    nome,  
    salario  
FROM colaboradores  
WHERE departamento_id  
IN (SELECT  
        id  
    FROM departamentos  
    WHERE nome = 'TI');

Nele, selecionamos o nome e salário da tabela de pessoas colaboradoras, onde o departamento_id seja igual a "TI". No SELECT, especificamos os campos, o FROM e o WHERE, e usamos o operador IN, que verifica a existência. Para o IN, passamos a subconsulta que seleciona o ID da tabela de departamentos, onde o nome seja igual a "TI".

O resultado é o seguinte:

Tabela de departamentos:

id	nome
1	'TI'
2	'Vendas'
3	'Recursos Humanos'

Na tabela de departamentos, temos os departamentos "TI", "Vendas" e "Recursos Humanos". "TI" tem ID 1.

Tabela de pessoas colaboradoras:

id	nome	departamento_id	valor
1	'Alice Santos'	1	7000.00
2	'Bob Smith'	2	4500.00
3	'Charlie Oliveira'	1	5000.00
4	'Diana Silva'	3	4000.00

Na tabela de pessoas colaboradoras, temos o nome e salário das pessoas colaboradoras. Notaremos que Alice e Charlie trabalham no departamento 1.

Resultado da consulta:

nome	salario
'Alice Santos'	7000.00
'Charlie Oliveira'	6000.00

O resultado é Alice Santos e Charlie Oliveira, que trabalham no departamento de TI.

Explorando subconsultas correlacionadas

Além disso, temos subconsultas correlacionadas, que referenciam colunas da consulta externa para cada linha processada.

Vamos abordar o seguinte exemplo:

SELECT  
    nome,  
    (SELECT  
        data_venda  
     FROM vendas vd  
     WHERE vd.vendedor_id = v.id  
     ORDER BY data_venda DESC  
     ) AS ultima_venda  
FROM vendedores v;

Nele, buscamos o nome da pessoa vendedora e aplicamos uma regra para retornar a última venda efetuada por ela. Retornaremos o nome das quatro pessoas vendedoras e, para cada uma, a data da última venda.

No SELECT mais externo, selecionamos o nome da pessoa vendedora. Já no SELECT interno, utilizamos a subconsulta para extrair a última venda. Selecionamos a data da venda da tabela de vendas, aplicando o filtro onde o vendedor_id de vendas seja igual ao vd_id.

Em seguida, ordenamos pela data da venda. Para cada pessoa vendedora, a consulta é executada.

O resultado ficou assim:

Tabela de pessoas vendedoras:

id	nome	departamento_id
1	'Carlos Andrade'	2
2	'Julia Martins'	2
3	'Marcos Souza'	1
4	'Ana Carolina'	3

Na tabela de pessoas vendedoras, temos Carlos, Julia, Marcos e Ana.

Tabela de vendas:

id	vendedor_id	data_venda	valor
1	1	'2023-01-10'	500.00
2	2	'2023-01-11'	1500.00
3	1	'2023-02-15'	700.00
4	1	'2023-02-20'	300.00
5	3	'2023-03-05'	1200.00

Na tabela das vendas, a pessoa vendedora 1 tem três vendas — a mais recente em 20/02/2023. Já as pessoas vendedoras 2 e 3 têm uma venda cada.

Resultado da consulta:

nome	ultima_venda
'Carlos Andrade'	'2023-02-20'
'Julia Martins'	'2023-01-11'
'Marcos Souza'	'2023-03-05'
'Ana Carolina'	null

A venda mais recente de Carlos André é 20/02/2023. Julia e Marcos têm uma venda cada. Por fim, Ana Carolina é retornada com valor nulo, pois não possui vendas, mas existe na tabela de pessoas vendedoras.

Utilizando o operador EXISTS

Por fim, temos os operadores EXISTS e NOT EXISTS. No operador EXISTS, testamos a existência de linhas em uma subconsulta correlacionada.

Vamos abordar o seguinte exemplo:

SELECT  
    nome  
FROM as pessoas fornecedoras f  
WHERE EXISTS  
    (SELECT  
        *  
     FROM produtos p  
     WHERE p.fornecedor_id = f.id);

No código acima, queremos retornar o nome das pessoas fornecedoras onde existem produtos cadastrados no banco de dados. Retornamos apenas os nomes das pessoas fornecedoras que têm produtos.

Selecionamos com SELECT o nome, o FROM parte da tabela de pessoas fornecedoras, e no WHERE, especificamos o operador EXISTS. Para este, passamos a subconsulta que seleciona informações da tabela de produtos onde o fornecedor_id da tabela de produtos seja igual ao f.id da tabela de pessoas fornecedoras.

O resultado ficou assim:

Tabela de pessoas fornecedoras:

id	nome	telefone
1	'ElectroMax'	'+1-800-555-0101'
2	'GadgetPro'	'+1-800-555-0202'
3	'HomeKitch'	'+1-800-555-0303'

Na tabela de pessoas fornecedoras, temos três: ElectroMax, GadgetPro e HomeKitch.

Tabela de produtos:

id	nome	fornecedor_id	descricao
1	'Notebook Profissional A1'	1	'Notebook de alto desempenho para profissionais'
2	'Smartphone XYZ Pro'	2	'Smartphone com a melhor câmera do mercado'
3	'Cafeteira Elétrica Max'	2	'Cafeteira rápida para seu café da manhã'

Na tabela de produtos, temos as pessoas fornecedoras 1 e 2. A pessoa fornecedora 3 não tem produtos e não é retornada. Ela só seria apresentada no retorno da consulta se existissem produtos relacionados a ela.

Utilizando o operador NOT EXISTS

Além do EXISTS, temos o operador NOT EXISTS, que nega a operação. Ele testa a existência de linhas em uma subconsulta correlacionada, mas faz o inverso.

Abordaremos o seguinte exemplo:

SELECT  
    nome  
FROM fornecedores f  
WHERE  
    NOT EXISTS  
    (SELECT  
        *  
     FROM reclamacoes r  
     WHERE r.fornecedor_id = f.id);

No código acima, temos as pessoas fornecedoras e as reclamações. Se não existirem reclamações associadas à pessoa fornecedora, ela será retornada na consulta. Selecionamos o nome das pessoas fornecedoras novamente, com o FROM que parte da tabela de pessoas fornecedoras, e no WHERE, passamos o NOT EXISTS.

Para esse operador, passamos a subconsulta que seleciona campos da tabela de reclamações, onde o fornecedor_id da tabela de reclamações seja igual ao f.id da tabela de pessoas fornecedoras.

O resultado é o seguinte:

Tabela de pessoas fornecedoras:

id	nome	telefone
1	'ElectroMax'	'+1-800-555-0101'
2	'GadgetPro'	'+1-800-555-0202'
3	'HomeKitch'	'+1-800-555-0303'

Na tabela de pessoas fornecedoras, temos três. Contudo, na tabela de reclamações, apenas duas possuem reclamações associadas.

Tabela de reclamações:

id	fornecedor_id	descricao	data_reclamacao
1	1	'Defeito no carregador'	'2023-01-10'
2	2	'Atraso na entrega'	'2023-01-11'

O resultado da consulta é o único fornecedor sem reclamações, o HomeKitch.

Resultado da consulta:

nome
'HomeKitch'

Conclusão

Dessa forma, conseguimos utilizar subconsultas de diversas maneiras para montar relatórios.

A partir de agora, não deixe de praticar! Faça todos os desafios e atividades disponibilizadas na plataforma do curso.

Em caso de dúvidas, recorra ao fórum de discussão e à comunidade no Discord.

Esperamos que você pratiquem bastante e coloque em prática todos os conhecimentos adquiridos. Até a próxima!

Sobre o curso Praticando SQL: entendendo subconsultas

O curso Praticando SQL: entendendo subconsultas possui 14 minutos de vídeos, em um total de 13 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• SQL: Subconsultas