Subconsultas e Operações Aninhadas no MySQL

Introdução

Neste laboratório, você explorará o poder das subconsultas (subqueries) e operações aninhadas (nested operations) do MySQL. O foco principal é o uso de subconsultas na cláusula WHERE para filtrar dados com base em condições derivadas de outras tabelas ou da mesma tabela.

Você aprenderá a se conectar a um servidor MySQL, criar um banco de dados e tabelas (clientes e pedidos), e então construir consultas SQL que utilizam subconsultas para identificar clientes que fizeram pedidos com um valor total superior a um valor específico. O laboratório também aborda o uso de EXISTS com uma subconsulta, o teste de subconsultas correlacionadas e a comparação de desempenho de subconsultas.

Escreva uma Subconsulta na Cláusula WHERE

Nesta etapa, você aprenderá a usar uma subconsulta dentro da cláusula WHERE de uma instrução SQL. Uma subconsulta é uma consulta aninhada dentro de outra consulta. É uma ferramenta poderosa para recuperar dados com base em condições derivadas de outras tabelas ou da mesma tabela.

Compreendendo Subconsultas

Uma subconsulta (ou consulta interna) é uma consulta SQL aninhada dentro de uma consulta maior. A subconsulta é executada primeiro e seu resultado é usado pela consulta externa. Subconsultas podem aparecer nas cláusulas WHERE, SELECT, FROM e HAVING.

Na cláusula WHERE, uma subconsulta é tipicamente usada para filtrar os resultados da consulta externa com base em uma condição. A subconsulta retorna um único valor ou um conjunto de valores que a consulta externa usa para comparação.

Cenário

Imagine que você tem duas tabelas: customers e orders. A tabela customers contém informações do cliente (por exemplo, customer_id, name, city), e a tabela orders contém informações de pedidos (por exemplo, order_id, customer_id, order_date, total_amount).

Você deseja encontrar todos os clientes que fizeram pelo menos um pedido com um valor total superior a $100.

Etapas

Conectar ao Servidor MySQL:

Abra seu terminal e execute o seguinte comando para se conectar ao seu servidor MySQL como usuário root:
```
sudo mysql -u root
```
Você deverá ver o prompt do MySQL: mysql>.

Criar o Banco de Dados e as Tabelas:

Se você ainda não tem um banco de dados e tabelas, crie-os agora. Vamos criar um banco de dados chamado labdb e as tabelas customers e orders. Execute os seguintes comandos SQL no prompt do MySQL:

CREATE DATABASE IF NOT EXISTS labdb;
USE labdb;

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Inserir Dados de Exemplo:

Insira alguns dados de exemplo nas tabelas. Execute os seguintes comandos SQL no prompt do MySQL:

INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice Smith', 'New York'),
(2, 'Bob Johnson', 'Los Angeles'),
(3, 'Charlie Brown', 'Chicago'),
(4, 'David Lee', 'Houston');

INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2023-01-15', 120.00),
(2, '2023-02-20', 80.00),
(1, '2023-03-10', 150.00),
(3, '2023-04-05', 200.00),
(2, '2023-05-12', 110.00),
(4, '2023-06-18', 90.00);

Escrever a Subconsulta na Cláusula WHERE:

Agora, escreva a consulta para encontrar clientes que fizeram pedidos com um valor total superior a $100. Execute o seguinte comando SQL no prompt do MySQL:
```
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 100);
```
Explicação:
- A subconsulta (SELECT customer_id FROM orders WHERE total_amount > 100) seleciona o customer_id da tabela orders onde o total_amount é maior que 100.
- A consulta externa SELECT * FROM customers WHERE customer_id IN (...) seleciona todas as colunas da tabela customers onde o customer_id está no conjunto de customer_ids retornados pela subconsulta.

Observar a Saída:

Você deverá ver a seguinte saída, mostrando os clientes que fizeram pedidos com um valor total superior a $100:

+-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
+-------------+-------------+-----------+
3 rows in set (0.00 sec)

Use EXISTS com uma Subconsulta

Nesta etapa, você aprenderá a usar o operador EXISTS com uma subconsulta no MySQL. O operador EXISTS é usado para testar a existência de linhas em uma subconsulta. Ele retorna TRUE se a subconsulta retornar alguma linha e FALSE caso contrário.

Compreendendo EXISTS

O operador EXISTS é frequentemente usado na cláusula WHERE de uma instrução SQL para filtrar resultados com base na existência de dados relacionados em outra tabela. É uma alternativa poderosa ao uso de operações IN ou JOIN, e às vezes pode ser mais eficiente, especialmente ao lidar com grandes conjuntos de dados.

Ao contrário de IN, EXISTS não recupera realmente os dados da subconsulta. Ele simplesmente verifica se alguma linha é retornada. Isso pode torná-lo mais rápido que IN quando você só precisa saber se uma correspondência existe, não os valores reais.

Cenário

Continuando com as tabelas customers e orders da etapa anterior, vamos encontrar todos os clientes que fizeram pelo menos um pedido.

Pré-requisitos

Certifique-se de ter completado a etapa anterior ("Escrever uma Subconsulta na Cláusula WHERE") e ter o banco de dados labdb, a tabela customers e a tabela orders preenchidos com dados.

Etapas

Escrever a Consulta usando EXISTS:

Escreva a consulta para encontrar clientes que fizeram pelo menos um pedido. Execute o seguinte comando SQL no prompt do MySQL:
```
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
Explicação:
- A consulta externa SELECT * FROM customers c seleciona todas as colunas da tabela customers, com o alias c.
- A cláusula WHERE EXISTS (...) verifica se a subconsulta retorna alguma linha.
- A subconsulta SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id seleciona o valor 1 (pode ser qualquer valor constante) da tabela orders, com o alias o, onde o customer_id na tabela orders corresponde ao customer_id na tabela customers.
- O operador EXISTS retorna TRUE se a subconsulta retornar pelo menos uma linha, indicando que o cliente fez pelo menos um pedido.

Observar a Saída:

Você deverá ver a seguinte saída, mostrando todos os clientes que fizeram pelo menos um pedido:

+-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
|           4 | David Lee   | Houston   |
+-------------+-------------+-----------+
4 rows in set (0.00 sec)

Modificar a Consulta (Opcional):

Vamos modificar a consulta para encontrar clientes que não fizeram nenhum pedido. Você pode fazer isso usando NOT EXISTS. Execute o seguinte comando SQL no prompt do MySQL:
```
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
Observar a Saída:

Como todos os clientes em nossos dados de exemplo fizeram pedidos, esta consulta deverá retornar um conjunto de resultados vazio:
```
Empty set (0.00 sec)
```

Teste uma Subconsulta Correlacionada

Nesta etapa, você aprenderá sobre subconsultas correlacionadas no MySQL. Uma subconsulta correlacionada é uma subconsulta que referencia uma coluna da consulta externa. Isso significa que a subconsulta é executada uma vez para cada linha na consulta externa.

Compreendendo Subconsultas Correlacionadas

Ao contrário de uma subconsulta simples, que é executada apenas uma vez, uma subconsulta correlacionada depende da consulta externa para seus valores. A subconsulta usa valores da linha atual da consulta externa para determinar seu resultado. Isso torna as subconsultas correlacionadas mais poderosas para certos tipos de consultas, mas também potencialmente menos eficientes do que subconsultas simples, especialmente para grandes conjuntos de dados.

Cenário

Continuando com as tabelas customers e orders, vamos encontrar todos os clientes que fizeram um pedido com um valor superior ao valor médio do pedido para todos os pedidos.

Pré-requisitos

Certifique-se de ter completado as etapas anteriores e ter o banco de dados labdb, a tabela customers e a tabela orders preenchidos com dados.

Etapas

Escrever a Subconsulta Correlacionada:

Escreva a consulta para encontrar clientes que fizeram um pedido com um valor superior ao valor médio do pedido. Execute o seguinte comando SQL no prompt do MySQL:
```
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > (SELECT AVG(total_amount) FROM orders)
);
```
Explicação:
- A consulta externa SELECT c.customer_id, c.name FROM customers c seleciona o customer_id e o name da tabela customers, com alias c.
- A cláusula WHERE EXISTS (...) verifica se a subconsulta retorna alguma linha.
- A subconsulta correlacionada SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total_amount > (SELECT AVG(total_amount) FROM orders) seleciona o valor 1 da tabela orders, com alias o, onde:
  - o.customer_id = c.customer_id: Esta é a correlação. A subconsulta referencia o customer_id da tabela customers da consulta externa.
  - o.total_amount > (SELECT AVG(total_amount) FROM orders): Esta condição verifica se o valor do pedido é maior que o valor médio do pedido em todos os pedidos. A subconsulta AVG(total_amount) é uma subconsulta não correlacionada que é executada apenas uma vez para obter o valor médio do pedido.

Observar a Saída:

Você deverá ver a seguinte saída, mostrando os clientes que fizeram pedidos com um valor superior ao valor médio do pedido:

+-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           1 | Alice Smith |
|           3 | Charlie Brown |
+-------------+-------------+
2 rows in set (0.00 sec)

Outro Exemplo: Encontrar o maior valor de pedido para cada cliente

Execute o seguinte comando SQL no prompt do MySQL para recuperar o ID, nome e o maior valor de pedido de cada cliente. A subconsulta correlacionada calcula o valor máximo do pedido para cada cliente individualmente.
```
SELECT c.customer_id, c.name, (
    SELECT MAX(o.total_amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) AS highest_order_amount
FROM customers c;
```

Observar a Saída:

Você deverá ver a seguinte saída:

+-------------+-------------+-----------------------+
| customer_id | name        | highest_order_amount  |
+-------------+-------------+-----------------------+
|           1 | Alice Smith |                150.00 |
|           2 | Bob Johnson |                110.00 |
|           3 | Charlie Brown |                200.00 |
|           4 | David Lee   |                 90.00 |
+-------------+-------------+-----------------------+
4 rows in set (0.00 sec)

Compare o Desempenho de Subconsultas

Nesta etapa, você aprenderá a comparar o desempenho de diferentes tipos de subconsultas no MySQL. Compreender as características de desempenho das subconsultas é crucial para escrever consultas SQL eficientes, especialmente ao lidar com grandes conjuntos de dados.

Compreendendo Considerações de Desempenho

O desempenho de uma subconsulta pode ser afetado por vários fatores, incluindo:

Tamanho dos Dados: O tamanho das tabelas envolvidas na consulta.
Tipo de Subconsulta: Se a subconsulta é correlacionada ou não correlacionada.
Indexação: A presença e eficácia de índices nas tabelas.
Versão do MySQL: A versão específica do MySQL em uso, pois as técnicas de otimização de consulta podem variar.

Cenário

Continuando com as tabelas customers e orders, vamos comparar o desempenho de uma subconsulta usando IN versus uma subconsulta usando EXISTS para encontrar todos os clientes que fizeram pelo menos um pedido.

Pré-requisitos

Certifique-se de ter completado as etapas anteriores e ter o banco de dados labdb, a tabela customers e a tabela orders preenchidos com dados. Para tornar a comparação de desempenho mais significativa, adicionaremos mais dados à tabela orders.

Etapas

Adicionar Mais Dados à Tabela orders:

Para tornar a comparação de desempenho mais realista, vamos adicionar uma quantidade significativa de dados à tabela orders. Inseriremos 1000 pedidos para cada cliente usando um stored procedure. Execute os seguintes comandos SQL no prompt do MySQL:

DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000 DO
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
    SET i = i + 1;
  END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;

Explicação:

Este script SQL cria um stored procedure chamado insert_many_orders.
O procedimento insere 1000 pedidos para cada um dos quatro clientes na tabela orders.
Após inserir os dados, o procedimento é descartado.

Consulta usando IN:

Execute a seguinte consulta usando IN para encontrar todos os clientes que fizeram pelo menos um pedido:
```
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
```
Analisar o Plano de Execução da Consulta usando EXPLAIN:

Antes de executar a consulta, use o comando EXPLAIN para analisar o plano de execução da consulta. Isso lhe dará insights sobre como o MySQL planeja executar a consulta e identificar possíveis gargalos de desempenho. Execute o seguinte comando SQL no prompt do MySQL:
```
EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
```
A saída do EXPLAIN mostrará as tabelas que estão sendo acessadas, os índices que estão sendo usados (se houver) e a ordem em que as operações são realizadas. Preste atenção à coluna type, que indica o tipo de join ou método de acesso utilizado.
Consulta usando EXISTS:

Execute a seguinte consulta usando EXISTS para encontrar todos os clientes que fizeram pelo menos um pedido:
```
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
Analisar o Plano de Execução da Consulta usando EXPLAIN:

Use o comando EXPLAIN para analisar o plano de execução da consulta EXISTS. Execute o seguinte comando SQL no prompt do MySQL:
```
EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
Compare o plano de execução com o da consulta IN. Existem diferenças nas tabelas acessadas, índices usados ou métodos de acesso?
Observações:

Em geral, EXISTS tende a ter um desempenho melhor que IN quando a subconsulta retorna um grande número de linhas. Isso ocorre porque IN precisa comparar os valores da consulta externa com todos os valores retornados pela subconsulta, enquanto EXISTS para assim que encontra uma correspondência. No entanto, o desempenho real pode variar dependendo da consulta específica, dos dados e do sistema de banco de dados. Você pode usar a função BENCHMARK() (como mostrado no documento original) para obter uma medição mais precisa do tempo de execução, mas para este laboratório, analisar a saída do EXPLAIN fornece insights suficientes sobre os planos de consulta.
Limpeza (Opcional):

Se você quiser limpar o banco de dados e as tabelas, pode executar os seguintes comandos no prompt do MySQL:
```
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
```
Quando terminar todas as etapas, você pode sair do cliente MySQL digitando:
```
exit
```

Resumo

Neste laboratório, você aprendeu a usar subconsultas na cláusula WHERE de uma instrução SQL para filtrar dados com base em condições derivadas de outras tabelas ou da mesma tabela. Você praticou a conexão a um servidor MySQL, a criação de um banco de dados e tabelas, e a inserção de dados de exemplo.

Você explorou o uso do operador IN com uma subconsulta para encontrar clientes com base em dados relacionados na tabela orders. Você também aprendeu sobre o operador EXISTS como uma alternativa ao IN e praticou seu uso para verificar a existência de linhas relacionadas.

Além disso, você foi introduzido às subconsultas correlacionadas, que referenciam colunas da consulta externa, e usou uma para encontrar clientes com pedidos que excedem o valor médio do pedido. Finalmente, você comparou o desempenho das subconsultas IN e EXISTS analisando seus planos de execução usando o comando EXPLAIN, obtendo insights sobre como o MySQL processa essas consultas.