Você pode usar várias instruções WITH no SQL?

Um guia abrangente sobre várias instruções WITH em SQL, perfeito tanto para iniciantes quanto para especialistas.

A cláusula SQL WITH permite que você defina uma CTE (expressão de tabela comum). Uma CTE é como uma tabela que é preenchida durante a execução da consulta. Você pode usar várias instruções WITH em uma consulta SQL para definir vários CTEs. Neste artigo, explicaremos como definir vários CTEs em uma única consulta. Também mostraremos como essa abordagem leva o poder expressivo de uma consulta SQL para o próximo nível.

É possível usar várias instruções WITH no SQL?

Sim! Abaixo está uma consulta simples que usa duas cláusulas WITH:

WITH wine_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘wine’
  AND qty_exported > 0
),
car_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘car’
  AND qty_exported > 0
)
SELECT country
FROM wine_exporting_country 
INTERSECT car_exporting_country

Na consulta acima, há dois CTEs. O primeiro (em vermelho) é chamado wine_exporting_country e o segundo (em azul) é car_exporting_country. A consulta principal (em preto) faz referência às duas declarações WITH como tabelas regulares usando INTERSECT. A interseção das duas tabelas retornará somente os países presentes em ambos os resultados do CTE.

Observe que você pode ver a palavra-chave WITH apenas uma vez. Após a cláusula WITH, você precisa colocar o nome do CTE, depois a palavra-chave AS e, finalmente, entre parênteses, a consulta SELECT para definir o CTE.

Se quiser aprender a usar várias instruções WITH em uma consulta, recomendo nosso curso interativo Consultas Recursivas curso interativo. Ele ensina os detalhes da sintaxe da cláusula WITH em mais de 100 exercícios práticos. Ele aborda a sintaxe básica, várias instruções WITH e consultas recursivas - o uso mais complexo da sintaxe WITH.

A cláusula WITH no SQL

Vamos primeiro explicar como funciona a cláusula WITH. Ela cria uma espécie de tabela virtual (o CTE) em tempo real, que é criada e preenchida durante a execução de uma única instrução SQL; após a execução do SQL, a "tabela" do CTE é removida automaticamente. Para entender melhor a cláusula WITH, sugiro o artigo O que é um CTE?

Neste artigo, usaremos uma tabela de banco de dados chamada product que rastreia os países e os produtos mais populares que eles produzem (por exemplo, azeite de oliva, vinho, carros). Ela registra a quantidade produzida, importada e exportada por cada país. A tabela também contém o preço do produto e a população do país.

Vejamos um exemplo simples de uma consulta que usa uma cláusula WITH para definir um CTE. Suponha que desejemos obter uma lista dos países que exportam mais azeite de oliva do que importam. Queremos que os resultados sejam ordenados pela quantidade produzida por cada país. Aqui está a consulta:

WITH olive_oil_exporter AS (
  SELECT 
    country, 
    qty_produced
  FROM product
  WHERE product_name = ‘olive oil’
  AND qty_exported > qty_imported
)
SELECT country
FROM olive_oil_exporter
ORDER BY qty_produced DESC;

Nessa consulta, temos um CTE chamado olive_oil_exporter, que é como uma tabela com duas colunas: country e qty_produced. As linhas no CTE contêm apenas os países que exportam mais azeite de oliva do que importam. Depois disso, usamos um SELECT regular para consultar o CTE olive_oil_exporter para obter os nomes dos países, que ordenamos de forma decrescente pela quantidade de azeite de oliva produzida naquele país.

Uso de várias instruções WITH no SQL

Vamos supor que queremos categorizar os países em países produtores de alimentos e países produtores de tecnologia. Para ser um produtor de alimentos, um país deve exportar mais de 100 milhões de dólares em produtos alimentícios. Para ser um produtor de alta tecnologia, um país deve produzir mais de 1.000 dólares em produtos tecnológicos por habitante.

Queremos um relatório com os nomes de todos os países e duas colunas chamadas is_a_food_producer e is_a_hightech_producer. A consulta SQL para esse relatório é:

WITH food_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘food’
  GROUP BY country
  HAVING SUM( qty_exported * unit_price) > 100000000
)
hightech_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘technology’
  GROUP BY country
  HAVING SUM( qty_produced / country_population) > 1000
)
SELECT DISTINCT 
  p.country,
  CASE 
     WHEN fp.country IS NULL THEN ‘No’ 
     ELSE ‘Yes’ END AS is_a_food_produced,
  CASE 
    WHEN htp.country IS NULL THEN ‘No’ 
    ELSE ‘Yes’ END AS is_a_hightech_produced,
FROM products p
LEFT JOIN food_producer fp 
ON fp.country = p.country
LEFT JOIN hightech_producer htp 
ON htp.country = p.country

Na consulta acima, podemos identificar claramente três consultas separadas. As duas primeiras consultas usam a cláusula WITH para definir duas tabelas CTE: food_producer e hightech_producer. A terceira consulta é a consulta principal, que consome as duas tabelas CTE criadas anteriormente.

Após a cláusula WITH, você pode ver o nome da tabela CTE (food_producer), depois a subcláusula AS e, finalmente (entre parênteses), a consulta para esse CTE. Para o segundo CTE, a cláusula WITH não é necessária; basta colocar uma vírgula e, em seguida, repetir a mesma sintaxe começando com o nome do CTE.

Ambos os CTEs food_producer e hightech_producer têm apenas uma coluna: country. A consulta principal obtém os nomes de todos os países da tabela producte, em seguida, é feita uma LEFT JOIN em cada um dos CTEs. Quando o LEFT JOIN não tem uma linha correspondente, significa que a resposta para esse país é "No"; quando há uma linha correspondente, o valor para esse país é "Yes".

Antes de ir para a próxima seção, gostaria de sugerir o artigo Como Escrever Múltiplos CTEs em SQL. Nele, você pode encontrar muitos exemplos e explicações sobre CTEs.

Uso de declarações WITH múltiplas e aninhadas no SQL

Em alguns casos, precisamos de um segundo CTE que se baseia no primeiro CTE: um CTE aninhado. Em outras palavras, a consulta para definir o segundo CTE deve ter uma referência ao primeiro CTE. Vejamos um exemplo.

Queremos obter o valor total em dólares exportado por países em produtos alimentícios. No mesmo relatório, queremos mostrar a porcentagem que esse valor representa no total de exportações do país para todos os tipos de produtos. A consulta é:

WITH country_export_by_product AS (
  SELECT country, 
  product_family, 
  SUM(qty_exported * unit_price) AS total_exports
  FROM product
  GROUP BY country, product_family
),
country_export_total AS (
  SELECT country, 
  SUM(total_exports) AS total_exports_country
  FROM country_export_by_product 
  GROUP BY country
)
SELECT 
  cp.country, 
  product_family, 
  cp.total_exports_food ,
  ROUND((cp.total_exports_food / ct.total_exports_country) * 100, 2) 
     AS percentage_of_total_exports
FROM country_export_by_product cp
JOIN country_export_total ct 
ON ct.country = cp.country
ORDER BY country, product_family;

Nessa consulta, criamos um CTE chamado country_export_by_product que tem as colunas country, product_family e total_exports (que representa o valor total desse produto exportado por esse país (em dólares)). Observe que a cláusula GROUP BY usa as colunas country e product_family.

O próximo CTE chama-se country_export_total e é baseado no CTE anterior country_export_by_product. A ideia desse CTE é obter o valor total exportado por cada país com base no CTE anterior. Observe que no segundo CTE usamos uma cláusula GROUP BY country. A necessidade de níveis diferentes da cláusula GROUP BY é o motivo pelo qual temos dois CTEs.

A consulta principal faz referência a ambos os CTEs, unindo-os pelo valor do país. Em seguida, a expressão

TRUNC((cp.total_exports_food / ct.total_exports_country) * 100, 2)

... é usada para calcular a porcentagem que cada produto representa no total de exportações desse país.

Em termos de sintaxe, você pode fazer com que um CTE faça referência a outro CTE na mesma consulta. Foi o que fizemos em nossa consulta: ao definir o CTE country_export_total, fizemos referência ao CTE country_export_by_product definido anteriormente.

Observe que podemos nos referir à instrução WITH definida antes da instrução WITH atual, mas não às posteriores. Você pode fazer referência a cada instrução WITH várias vezes em outra instrução WITH ou em uma consulta principal. Em nosso exemplo, fizemos referência ao primeiro WITH definido (o country_export_by_product CTE) em dois lugares: No segundo WITH (o country_export_total CTE) e na consulta principal.

Outras limitações relacionadas à sintaxe da cláusula WITH são:

• Você deve usar a palavra-chave WITH apenas uma vez, antes do primeiro CTE.
• Todos os CTEs são separados por vírgulas, mas não há vírgula antes da consulta principal. Isso segue o padrão de sintaxe:

            WITH cte_name1 AS (query1), 
           cte_name2 AS (query2) 
      main_query

Sugiro o artigo CTEs SQL Explicados com Exemplos para obter vários outros exemplos de consultas WITH; eles demonstram como melhorar a organização e a legibilidade de suas consultas SQL usando CTEs.

Uso da cláusula WITH para criar Consultas Recursivas

Você usa a cláusula WITH no SQL para definir consultas recursivas. As consultas recursivas o ajudam a consultar estruturas hierárquicas (ou seja, organogramas, árvores ou gráficos). Você pode ler mais sobre a consulta de estruturas hierárquicas aqui.

As consultas recursivas são baseadas na cláusula WITH. Para criar uma consulta recursiva, você só precisa de uma cláusula WITH, mas a consulta em WITH consiste em duas partes.

As consultas recursivas são úteis quando as tabelas ou o modelo de dados do banco de dados têm um tipo de hierarquia implícita. Talvez a tabela de exemplo mais comum para explicar esse tópico seja a tabela típica employee com as colunas employee_id e manager_employee_id.

Se quisermos que um relatório mostre todos os funcionários com os nomes de seus gerentes e o nível hierárquico do funcionário, podemos usar a seguinte consulta recursiva:

WITH RECURSIVE company_hierarchy AS (
  SELECT 
    employee_id, 
    firstname, 
    lastname, 
    manager_employee_id, 
    0 AS hierarchy_level
  FROM employees
  WHERE manager_employee_id IS NULL 
  UNION ALL 
  SELECT 
    e.employee_id, 
    e.firstname, 
    e.lastname, 
    e.manager_employee_id,
    hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.manager_employee_id = ch.employee_id
)
SELECT 
  ch.firstname AS employee_first_name, 
  ch.lastname AS employee_last_name,
  e.firstname AS boss_first_name, 
  e.lastname AS boss_last_name,
  hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e 
ON ch.manager_employee_id = e.employee_id
ORDER BY ch.hierarchy_level, ch.manager_employee_id;

Nessa consulta, podemos ver a cláusula WITH RECURSIVE, que é usada para criar um CTE recursivo chamado company_hierarchy. O CTE terá todos os nomes dos funcionários com os nomes de seus gerentes. Observe que o CTE tem duas declarações SELECT conectadas por UNION ALL. A primeira SELECT é para obter o primeiro funcionário na consulta recursiva (CEO John Smith).

O segundo SELECT do UNION é uma consulta que é executada várias vezes. Em cada execução, ela retorna o(s) funcionário(s) no próximo nível da hierarquia. Por exemplo, sua primeira execução retorna todos os funcionários que se reportam diretamente a John Smith.

Finalmente, há uma terceira instrução SELECT; ela está fora do CTE. Ela seleciona os nomes dos funcionários, os nomes de seus chefes e o nível hierárquico. Os dados são extraídos do CTE e unidos à tabela employees. Usamos um LEFT JOIN porque queremos todos os dados do CTE (inclusive John Smith, que tem um valor NULL na coluna manager_id). Os resultados são mostrados em ordem crescente: primeiro pelo nível hierárquico, depois pelo employee_id do chefe. Abaixo está o resultado da consulta:

Para saber mais sobre consultas recursivas, consulte o artigo O que é um CTE recursivo no SQL?

Continue aprendendo sobre várias declarações WITH no SQL

Neste artigo, abordamos o uso de várias instruções WITH em uma única consulta SQL. Também mencionamos como usar a cláusula WITH em consultas recursivas. Se você quiser continuar aprendendo sobre a cláusula WITH, recomendo nosso Consultas Recursivas curso, que oferece uma excelente oportunidade de praticar o tipo mais desafiador de consultas SQL.

As várias instruções WITH são mais úteis quando você escreve relatórios SQL complexos. Se esse for o seu caso, também recomendo nossa Folha de consulta gratuita sobre SQL para análise de dados, que foi criada especificamente para ajudá-lo a escrever consultas complexas para análise de dados.

Se quiser praticar SQL em um nível avançado, confira nossa trilha SQL Avançado Practice. Ela contém mais de 200 exercícios para ajudá-lo a praticar conceitos avançados de SQL. A cada dois meses, publicamos um novo curso de prática de SQL avançado em nossa trilha Monthly Trilha de Práticas em SQL.

Você também pode obter todos esses cursos e outros em nosso plano Ilimitado Vitalício plano. O plano lhe dá acesso vitalício a todos os nossos cursos de SQL em vários níveis de proficiência e em quatro dialetos SQL. Inscreva-se hoje mesmo!