Resumo : neste tutorial, você aprenderá como usar a subconsulta MySQL para escrever consultas complexas e entender o conceito de subconsulta correlacionada.
Introdução à subconsulta MySQL
SELECT
Uma subconsulta MySQL é uma consulta aninhada em outra consulta , como INSERT
, UPDATE
ou DELETE
. Além disso, uma subconsulta pode ser aninhada em outra subconsulta.
Uma subconsulta MySQL é chamada de consulta interna, enquanto a consulta que contém a subconsulta é chamada de consulta externa. Uma subconsulta pode ser usada em qualquer lugar em que a expressão seja usada e deve ser fechada entre parênteses.
Por exemplo, a consulta a seguir utiliza uma subconsulta para retornar os funcionários que trabalham nos escritórios localizados nos EUA.
SELECT
lastName, firstName
FROM
employees
WHERE
officeCode IN (SELECT
officeCode
FROM
offices
WHERE
country = 'USA');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste exemplo:
- A subconsulta retorna todos os códigos dos escritórios localizados nos EUA.
- A consulta externa seleciona o sobrenome e o nome dos funcionários que trabalham nos escritórios cujos códigos de escritório estão no conjunto de resultados retornado pela subconsulta.
Ao executar a consulta, o MySQL avalia primeiro a subconsulta e usa o resultado da subconsulta para a consulta externa.
Usando uma subconsulta MySQL na cláusula WHERE
Usaremos a tabela payments
do banco de dados de exemplo para a demonstração.
Subconsulta MySQL com operadores de comparação
Você pode usar operadores de comparação, por exemplo, =, >, < para comparar um único valor retornado pela subconsulta com a expressão na WHERE
cláusula.
Por exemplo, a consulta a seguir retorna o cliente que recebeu o pagamento mais alto.
SELECT
customerNumber,
checkNumber,
amount
FROM
payments
WHERE
amount = (SELECT MAX(amount) FROM payments);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
+----------------+-------------+-----------+
| customerNumber | checkNumber | amount |
+----------------+-------------+-----------+
| 141 | JE105477 | 120166.58 |
+----------------+-------------+-----------+
1 row in set (0.00 sec)
Linguagem de código: JavaScript ( javascript )
Além do =
operador, você pode usar outros operadores de comparação, como maior que ( >
), maior que ou igual a (>=) <
, menor que ( ) e menor que ou igual a (<=).
Por exemplo, você pode encontrar clientes cujos pagamentos são maiores que o pagamento médio usando uma subconsulta:
SELECT
customerNumber,
checkNumber,
amount
FROM
payments
WHERE
amount > (SELECT
AVG(amount)
FROM
payments);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
+----------------+-------------+-----------+
| customerNumber | checkNumber | amount |
+----------------+-------------+-----------+
| 112 | HQ55022 | 32641.98 |
| 112 | ND748579 | 33347.88 |
| 114 | GG31455 | 45864.03 |
| 114 | MA765515 | 82261.22 |
...
Neste exemplo:
- Primeiro, obtenha o pagamento médio usando uma subconsulta.
- Em seguida, selecione os pagamentos maiores que o pagamento médio retornado pela subconsulta na consulta externa.
Subconsulta MySQL com operadores IN e NOT IN
Se uma subconsulta retornar mais de um valor, você poderá usar outros operadores, como o operador IN
ou NOT IN
na WHERE
cláusula.
Veja o seguinte customers
e orders
as tabelas:
Por exemplo, você pode usar uma subconsulta com NOT IN
operador para encontrar os clientes que não fizeram nenhum pedido da seguinte forma:
SELECT
customerName
FROM
customers
WHERE
customerNumber NOT IN (SELECT DISTINCT
customerNumber
FROM
orders);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
+--------------------------------+
| customerName |
+--------------------------------+
| Havel & Zbyszek Co |
| American Souvenirs Inc |
| Porto Imports Co. |
| Asian Shopping Network, Co |
...
Subconsulta MySQL na cláusula FROM
Ao usar uma subconsulta na FROM
cláusula, o conjunto de resultados retornado de uma subconsulta é usado como uma tabela temporária. Essa tabela é chamada de tabela derivada ou subconsulta materializada.
A subconsulta a seguir encontra o número máximo , mínimo e médio de itens em pedidos de venda:
SELECT
MAX(items),
MIN(items),
FLOOR(AVG(items))
FROM
(SELECT
orderNumber, COUNT(orderNumber) AS items
FROM
orderdetails
GROUP BY orderNumber) AS lineitems;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
+------------+------------+-------------------+
| MAX(items) | MIN(items) | FLOOR(AVG(items)) |
+------------+------------+-------------------+
| 18 | 1 | 9 |
+------------+------------+-------------------+
1 row in set (0.01 sec)
Linguagem de código: JavaScript ( javascript )
Observe que o FLOOR()
é usado para remover casas decimais dos valores médios dos itens.
Subconsulta correlacionada do MySQL
Nos exemplos anteriores, você percebe que uma subconsulta é independente. Isso significa que você pode executar a subconsulta como uma consulta independente, por exemplo:
SELECT
orderNumber,
COUNT(orderNumber) AS items
FROM
orderdetails
GROUP BY orderNumber;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Ao contrário de uma subconsulta autônoma, uma subconsulta correlacionada é uma subconsulta que usa os dados da consulta externa. Em outras palavras, uma subconsulta correlacionada depende da consulta externa. Uma subconsulta correlacionada é avaliada uma vez para cada linha da consulta externa.
Consulte a products
tabela a seguir do banco de dados de exemplo :
O exemplo a seguir usa uma subconsulta correlacionada para selecionar produtos cujos preços de compra são maiores que o preço médio de compra de todos os produtos em cada linha de produtos.
SELECT
productname,
buyprice
FROM
products p1
WHERE
buyprice > (SELECT
AVG(buyprice)
FROM
products
WHERE
productline = p1.productline);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
+-----------------------------------------+----------+
| productname | buyprice |
+-----------------------------------------+----------+
| 1952 Alpine Renault 1300 | 98.58 |
| 1996 Moto Guzzi 1100i | 68.99 |
| 2003 Harley-Davidson Eagle Drag Bike | 91.02 |
| 1972 Alfa Romeo GTA | 85.68 |
| 1962 LanciaA Delta 16V | 103.42 |
| 1968 Ford Mustang | 95.34 |
..
Neste exemplo, tanto a consulta externa quanto a subconsulta correlacionada fazem referência à mesma products
tabela. Portanto, precisamos usar um alias de tabela p1
para a products
tabela na consulta externa.
Ao contrário de uma subconsulta normal, você não pode executar uma subconsulta correlacionada de forma independente como esta. Se você fizer isso, o MySQL não conhece a tabela p1 e emitirá um erro.
SELECT
AVG(buyprice)
FROM
products
WHERE
productline = p1.productline;
Para cada linha da products
tabela (ou p1), a subconsulta correlacionada precisa ser executada uma vez para obter o preço médio de compra de todos os produtos dessa productline
linha.
Se o preço de compra da linha atual for maior que o preço médio de compra retornado pela subconsulta correlacionada, a consulta incluirá a linha no conjunto de resultados.
Subconsulta MySQL com EXISTS e NOT EXISTS
Quando uma subconsulta é usada com o operador EXISTS
ou NOT EXISTS
, uma subconsulta retorna um valor booleano de TRUE
ou FALSE
. A consulta a seguir ilustra uma subconsulta usada com o EXISTS
operador:
SELECT
*
FROM
table_name
WHERE
EXISTS( subquery );
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Na consulta acima, se a subconsulta retornar alguma linha, EXISTS subquery
retornará TRUE
, caso contrário, retornará FALSE
.
Os EXISTS
e NOT EXISTS
são frequentemente usados nas subconsultas correlacionadas.
Vamos dar uma olhada nas tabelas orders
e orderdetails
do banco de dados de exemplo :
A consulta a seguir localiza pedidos de vendas cujos valores totais são superiores a 60 mil.
SELECT
orderNumber,
SUM(priceEach * quantityOrdered) total
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+-------------+----------+
| orderNumber | total |
+-------------+----------+
| 10165 | 67392.85 |
| 10310 | 61234.67 |
| 10287 | 61402.00 |
+-------------+----------+
3 rows in set (0.01 sec)
Linguagem de código: JavaScript ( javascript )
Ele retorna 3 linhas, o que significa que há três pedidos de venda cujos valores totais são superiores a 60K.
Você pode usar a consulta acima como uma subconsulta correlacionada para encontrar clientes que fizeram pelo menos um pedido de venda com valor total superior a 60 mil usando o EXISTS
operador:
SELECT
customerNumber,
customerName
FROM
customers
WHERE
EXISTS( SELECT
orderNumber, SUM(priceEach * quantityOrdered)
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
WHERE
customerNumber = customers.customerNumber
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Saída:
+----------------+--------------------------+
| customerNumber | customerName |
+----------------+--------------------------+
| 148 | Dragon Souveniers, Ltd. |
| 259 | Toms Spezialitäten, Ltd |
| 298 | Vida Sport, Ltd |
+----------------+--------------------------+
3 rows in set (0.01 sec)
Linguagem de código: JavaScript ( javascript )
Resumo
- Uma subconsulta é uma consulta aninhada em outra consulta (ou consulta externa).
- Uma subconsulta correlacionada depende da consulta externa.