Como Usar Consultas Aninhadas no SQL

Introdução

A Linguagem de Consulta Estruturada (SQL) é usada para gerenciar dados em um sistema de gerenciamento de banco de dados relacional (RDBMS). Uma função útil no SQL é criar uma consulta dentro de outra consulta, também conhecida como subconsulta ou consulta aninhada. Uma consulta aninhada é uma declaração SELECT que geralmente é envolvida por parênteses e incorporada dentro de uma operação primária SELECT, INSERT ou DELETE.

Neste tutorial, você usará consultas aninhadas com as declarações SELECT, INSERT e DELETE. Você também usará funções de agregação dentro de uma consulta aninhada para comparar os valores dos dados com os valores de dados ordenados especificados com as cláusulas WHERE e LIKE.

Pré-requisitos

Para seguir este guia, você precisará de um computador executando algum tipo de sistema de gerenciamento de banco de dados relacional (RDBMS) que use SQL. As instruções e exemplos neste tutorial foram validados usando o seguinte ambiente:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• O MySQL foi instalado e protegido no servidor. Siga nosso guia Como Instalar o MySQL no Ubuntu 20.04 para configurá-lo. Este guia presume que você também configurou um usuário MySQL não-root, conforme descrito no Passo 3 deste guia.

Para praticar o uso de consultas aninhadas neste tutorial, você precisará de um banco de dados e tabela carregados com dados de amostra. Se você não tiver um pronto para inserir, você pode ler a seguinte seção Conectando-se ao MySQL e Configurando um Banco de Dados de Amostra para aprender como criar um banco de dados e uma tabela. Este tutorial se referirá a este banco de dados e tabela de amostra ao longo.

Conectando-se ao MySQL e Configurando um Banco de Dados de Amostra

Se seu banco de dados SQL é executado em um servidor remoto, faça SSH em seu servidor a partir de sua máquina local:

ssh sammy@your_server_ip

Em seguida, abra o prompt do MySQL, substituindo sammy pelas informações de sua conta de usuário MySQL:

mysql -u sammy -p

Crie um banco de dados chamado zooDB:

CREATE DATABASE zooDB;

Se o banco de dados foi criado com sucesso, você receberá a seguinte saída:

Output
Query OK, 1 row affected (0.01 sec)

Para selecionar o banco de dados zooDB, execute a seguinte instrução USE:

USE zooDB;

Output
Database changed

Após selecionar o banco de dados, crie uma tabela dentro dele. Para o exemplo deste tutorial, vamos criar uma tabela que armazena informações sobre os visitantes do zoológico. Esta tabela terá as seguintes sete colunas:

• guest_id: armazena valores para os visitantes do zoológico, e usa o tipo de dados int. Isso também serve como a chave primária da tabela, significando que cada valor nesta coluna funcionará como um identificador único para sua respectiva linha.
• first_name: contém o primeiro nome de cada visitante usando o tipo de dados varchar com um máximo de 30 caracteres.
• last_name: usa o tipo de dados varchar, novamente com um máximo de 30 caracteres, para armazenar o sobrenome de cada visitante.
• guest_type: contém o tipo de visitante (adulto ou criança) para cada visitante usando o tipo de dados varchar com um máximo de 15 caracteres.
• membership_type: representa o tipo de associação que cada visitante possui, usando o tipo de dados varchar para armazenar um máximo de 30 caracteres.
• membership_cost: armazena o custo para vários tipos de associação. Esta coluna usa o tipo de dados decimal com uma precisão de cinco e uma escala de dois, o que significa que os valores nesta coluna podem ter cinco dígitos e dois dígitos à direita do ponto decimal.
• total_visits: utiliza o tipo de dados int para registrar o número total de visitas de cada convidado.

Crie uma tabela chamada guests que contenha cada uma dessas colunas executando o seguinte comando CREATE TABLE:

CREATE TABLE guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
guest_type varchar(15),
membership_type varchar(30),
membership_cost decimal(5,2), 
total_visits int,
PRIMARY KEY (guest_id)
); 

Em seguida, insira alguns dados de exemplo na tabela vazia:

INSERT INTO guests
(guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)
VALUES
(1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),
(2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),
(3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),
(4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),
(5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),
(6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),
(7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),
(8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),
(9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),
(10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),
(11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 
(12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 
(13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 
(14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),
(15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);

Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

Depois de inserir os dados, você está pronto para começar a usar consultas aninhadas no SQL.

Usando Consultas Aninhadas com SELECT

No SQL, uma consulta é uma operação que recupera dados de uma tabela em um banco de dados e sempre inclui uma instrução SELECT. Uma consulta aninhada é uma consulta completa incorporada dentro de outra operação. Uma consulta aninhada pode ter todos os elementos usados em uma consulta regular, e qualquer consulta válida pode ser incorporada dentro de outra operação para se tornar uma consulta aninhada. Por exemplo, uma consulta aninhada pode ser incorporada em operações INSERT e DELETE. Dependendo da operação, uma consulta aninhada deve ser incorporada, envolvendo a declaração dentro do número correto de parênteses para seguir uma determinada ordem de operações. Uma consulta aninhada também é útil em cenários onde você deseja executar vários comandos em uma única declaração de consulta, em vez de escrever várias para retornar seu(s) resultado(s) desejado(s).

Para entender melhor as consultas aninhadas, vamos ilustrar como elas podem ser úteis usando os dados de exemplo da etapa anterior. Por exemplo, digamos que você queira encontrar todos os convidados na tabela guests que visitaram o zoológico com uma frequência maior do que a média. Você pode supor que pode encontrar essa informação com uma consulta como a seguinte:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > AVG(total_visits);

No entanto, uma consulta usando esta sintaxe retornará um erro:

Output
ERROR 1111 (HY000): Invalid use of group function

O motivo desse erro é que as funções agregadas como AVG() não funcionam a menos que sejam executadas dentro de uma cláusula SELECT.

Uma opção para recuperar essas informações seria primeiro executar uma consulta para encontrar o número médio de visitas de convidados e, em seguida, executar outra consulta para encontrar resultados com base nesse valor, como nos seguintes dois exemplos:

SELECT AVG(total_visits) FROM guests;

Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 57.5333;

Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

No entanto, você pode obter o mesmo conjunto de resultados com uma única consulta aninhando a primeira consulta (SELECT AVG(total_visits) FROM guests;) dentro da segunda. Lembre-se de que, com consultas aninhadas, usar a quantidade apropriada de parênteses é necessário para concluir a operação que você deseja realizar. Isso ocorre porque a consulta aninhada é a primeira operação que é executada:

SELECT first_name, last_name, total_visits 
FROM guests 
WHERE total_visits > 
(SELECT AVG(total_visits) FROM guests);

Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

De acordo com este resultado, cinco convidados estavam visitando mais do que a média. Essas informações poderiam oferecer insights úteis para pensar em maneiras criativas de garantir que os membros atuais continuem a visitar o zoológico com frequência e renovem suas passagens de associação a cada ano. Além disso, este exemplo demonstra o valor de usar uma consulta aninhada em uma única declaração completa para obter os resultados desejados, em vez de ter que executar duas consultas separadas.

Usando Consultas Aninhadas com INSERT

Com uma consulta aninhada, você não está limitado apenas a incorporá-la dentro de outras declarações SELECT. Na verdade, você também pode usar consultas aninhadas para inserir dados em uma tabela existente incorporando sua consulta aninhada dentro de uma operação INSERT.

Para ilustrar, vamos supor que um zoológico afiliado solicite algumas informações sobre seus hóspedes porque estão interessados em oferecer um desconto de 15% para os hóspedes que comprarem uma adesão “Residente” em sua localização. Para fazer isso, use CREATE TABLE para criar uma nova tabela chamada upgrade_guests que contenha seis colunas. Preste atenção especial aos tipos de dados, como int e varchar, e aos caracteres máximos que podem conter. Se eles não estiverem alinhados com os tipos de dados originais da tabela guests que você criou na seção de configuração de um banco de dados de exemplo, então você receberá um erro ao tentar inserir dados da tabela guests usando uma consulta aninhada e os dados não serão transferidos corretamente. Crie sua tabela com as seguintes informações:

CREATE TABLE upgrade_guests (
guest_id int, 
first_name varchar(30),
last_name varchar(30),
membership_type varchar(30),
membership_cost decimal(5,2),
total_visits int, 
PRIMARY KEY (guest_id)
); 

Para consistência e precisão, mantivemos a maioria das informações sobre tipos de dados nesta tabela iguais à tabela guests. Também removemos quaisquer colunas extras que não desejamos na nova tabela. Com esta tabela vazia pronta para uso, o próximo passo é inserir os valores de dados desejados na tabela.

Nesta operação, escreva INSERT INTO e a nova tabela upgrade_guests, para que haja uma direção clara de onde os dados estão sendo inseridos. Em seguida, escreva sua consulta aninhada com a declaração SELECT para recuperar os valores de dados relevantes e FROM para garantir que eles estejam vindo da tabela guests.

Além disso, aplique o desconto de 15% a qualquer um dos membros “Resident” incluindo a operação matemática de multiplicação, * para multiplicar por 0,85, dentro da declaração de consulta aninhada (membership_cost * 0.85). Em seguida, utilize a cláusula WHERE para ordenar os valores na coluna membership_type. Você pode reduzir ainda mais os resultados apenas para associações “Resident” usando a cláusula LIKE e colocar o símbolo de porcentagem % antes e depois da palavra “Resident” entre aspas simples para selecionar todas as associações que seguem o mesmo padrão, ou neste caso, a mesma terminologia. Sua consulta será escrita da seguinte forma:

INSERT INTO upgrade_guests
SELECT guest_id, first_name, last_name, membership_type, 
(membership_cost * 0.85), total_visits
FROM guests
WHERE membership_type LIKE '%resident%';

Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

A saída indica que foram adicionados cinco registros à nova tabela upgrade_guests. Para confirmar que os dados que você solicitou foram transferidos com sucesso da tabela guests para a tabela vazia upgrade_guests que você criou, e com as condições que você especificou com a consulta aninhada e a cláusula WHERE, execute o seguinte:

SELECT * FROM upgrade_guests;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

De acordo com essa saída da sua nova tabela upgrade_guests, as informações de associação de hóspedes relacionadas a “Resident” da tabela guest foram inseridas corretamente. Além disso, o novo membership_cost foi recalculado com o desconto de 15% aplicado. Como resultado, essa operação ajudou a segmentar e direcionar o público apropriado e tem os preços com desconto prontamente disponíveis para compartilhar com esses potenciais novos membros.

Usando Consultas Aninhadas com DELETE

Para praticar o uso de uma consulta aninhada com uma instrução DELETE, digamos que você queira remover qualquer hóspede que seja visitante frequente, porque você quer se concentrar apenas em promover o desconto no passe premium atualizado para membros que não estejam visitando muito o zoológico no momento.

Comece esta operação com a instrução DELETE FROM para que fique claro de onde os dados estão sendo excluídos, neste caso, da tabela upgrade_guests. Em seguida, use a cláusula WHERE para classificar quaisquer total_visits que sejam mais do que a quantidade especificada na consulta aninhada. Em sua consulta aninhada embutida, use SELECT para encontrar a média, AVG, de total_visits, para que a cláusula WHERE precedente tenha os valores de dados apropriados para comparar. Por fim, use FROM para recuperar essas informações da tabela guests. A declaração de consulta completa será como a seguinte:

DELETE FROM upgrade_guests
WHERE total_visits >
(SELECT AVG(total_visits) FROM guests);

Output
Query OK, 2 rows affected (0.00 sec)

Confirme se esses registros foram excluídos com sucesso da tabela upgrade_guests e use ORDER BY para organizar os resultados por total_visits em ordem numérica e ascendente:

SELECT * FROM upgrade_guests ORDER BY total_visits;

Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

Como este resultado indica, a instrução DELETE e a consulta aninhada funcionaram corretamente ao excluir os valores de dados especificados. Esta tabela agora contém as informações dos três convidados com menos visitas do que a média, o que é um ótimo ponto de partida para o representante do zoológico entrar em contato com eles sobre a atualização para um passe premium com desconto e, esperançosamente, incentivá-los a ir ao zoológico com mais frequência.

Conclusão

As consultas aninhadas são úteis porque permitem obter resultados altamente granulares que de outra forma só seriam possíveis através da execução de consultas separadas. Além disso, o uso das instruções INSERT e DELETE com consultas aninhadas oferece outra maneira de inserir ou excluir dados em um único passo. Se você gostaria de aprender mais sobre como organizar seus dados, confira nossa série sobre Como Usar SQL.