Tabelas derivadas do MySQL

Resumo : neste tutorial, você aprenderá sobre tabelas derivadas do MySQL e como usá-las para simplificar consultas complexas.

Introdução às tabelas derivadas do MySQL

Uma tabela derivada é uma tabela virtual retornada de uma SELECTinstrução. Uma tabela derivada é semelhante a uma tabela temporária , mas usar uma tabela derivada na SELECTinstrução é muito mais simples do que uma tabela temporária porque não requer a criação de uma tabela temporária.

Os termos tabela derivada e subconsulta são frequentemente usados ​​de forma intercambiável. Quando uma subconsulta independente é usada na FROMcláusula de uma SELECTinstrução, ela também é chamada de tabela derivada.

O exemplo a seguir ilustra uma consulta que usa uma tabela derivada:

Observe que uma subconsulta independente é uma subconsulta que pode ser executada independentemente da consulta externa.

Ao contrário de uma subconsulta, uma tabela derivada deve ter um alias para que você possa referenciar seu nome posteriormente na consulta. Se uma tabela derivada não tiver um alias, o MySQL emitirá o seguinte erro:

Every derived table must have its own alias.

O seguinte ilustra a sintaxe de uma consulta que usa uma tabela derivada:

SELECT 
    select_list
FROM
    (SELECT 
        select_list
    FROM
        table_1) derived_table_name
WHERE 
    derived_table_name.c1 > 0;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Exemplo básico de tabela derivada do MySQL

A consulta a seguir obtém os cinco principais produtos por receita de vendas em 2003 a partir das tabelas orderse orderdetailsno banco de dados de exemplo :

SELECT 
    productCode, 
    ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Você pode usar o resultado desta consulta como uma tabela derivada e juntá-la à productstabela da seguinte maneira:

SELECT 
    productName, sales
FROM
    (SELECT 
        productCode, 
        ROUND(SUM(quantityOrdered * priceEach)) sales
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2003
INNER JOIN
    products USING (productCode);
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O seguinte mostra a saída da consulta acima:

Neste exemplo:

1. Primeiro, a subconsulta é executada para criar um conjunto de resultados ou tabela derivada.
2. Em seguida, é executada a consulta externa que une a top5product2003tabela derivada com a productstabela usando a productCodecoluna.

Um exemplo de tabela derivada do MySQL mais complexo

Suponha que você tenha que classificar os clientes que compraram produtos em 2003 em 3 grupos: platinum, gold, e silver. E você precisa saber a quantidade de clientes de cada grupo com as seguintes condições:

• Clientes Platinum que possuem pedidos com volume superior a 100K.
• Clientes Gold que possuem pedidos com volume entre 10K e 100K.
• Clientes Silver que possuem pedidos com volume inferior a 10K.

Para formar esta consulta, primeiro você precisa colocar cada cliente no respectivo grupo usando a CASEexpressão e GROUP BYa cláusula a seguir:

SELECT 
    customerNumber,
    ROUND(SUM(quantityOrdered * priceEach)) sales,
    (CASE
        WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
        WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
    END) customerGroup
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY customerNumber;
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A seguir está a saída da consulta:

Em seguida, você pode usar esta consulta como tabela derivada e realizar o agrupamento da seguinte maneira:

SELECT 
    customerGroup, 
    COUNT(cg.customerGroup) AS groupCount
FROM
    (SELECT 
        customerNumber,
            ROUND(SUM(quantityOrdered * priceEach)) sales,
            (CASE
                WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
                WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
                WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
            END) customerGroup
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;    
Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A consulta retorna os grupos de clientes e o número de clientes em cada um.

Neste tutorial, você aprendeu como usar tabelas derivadas do MySQL, que são subconsultas na FROMcláusula, para simplificar consultas complexas.