Subconsulta MySQL

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

SELECTUma 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 paymentsdo 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 WHEREclá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 )

Experimente

+----------------+-------------+-----------+
| 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 )

Experimente

+----------------+-------------+-----------+
| 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 INou NOT INna WHEREcláusula.

Veja o seguinte customerse ordersas tabelas:

Por exemplo, você pode usar uma subconsulta com NOT INoperador 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 )

Experimente

+--------------------------------+
| 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 FROMclá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 )

Experimente

+------------+------------+-------------------+
| 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 productstabela 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 )

Experimente

+-----------------------------------------+----------+
| 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 productstabela. Portanto, precisamos usar um alias de tabela p1para a productstabela 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 productstabela (ou p1), a subconsulta correlacionada precisa ser executada uma vez para obter o preço médio de compra de todos os produtos dessa productlinelinha.

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 EXISTSou NOT EXISTS, uma subconsulta retorna um valor booleano de TRUEou FALSE. A consulta a seguir ilustra uma subconsulta usada com o EXISTSoperador:

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 subqueryretornará TRUE, caso contrário, retornará FALSE.

Os EXISTSe NOT EXISTSsão frequentemente usados ​​nas subconsultas correlacionadas.

Vamos dar uma olhada nas tabelas orderse orderdetailsdo 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 EXISTSoperador:

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.