SQL WITH

SQL > Linguagem SQL avançada > WITH

Às vezes, você se depara com uma pergunta complexa que não pode ser facilmente respondida por meio de uma única declaração SQL.

Você poderia tentar chegar à resposta em uma única passagem usando várias visualizações embutidas ou várias subconsultas, mas isso provavelmente tornaria seu SQL difícil de entender, e algumas variedades de SQL, como o Hive SQL, não permitem mais de uma subconsulta.

Uma segunda abordagem é decompor a complexidade em várias etapas diferentes. Uma maneira de fazer isso é criar várias tabelas, sendo que cada tabela armazena os resultados de cada etapa. Isso é frequentemente uma boa estratégia, pois torna a depuração mais fácil e o acompanhamento de como o código é executado se torna mais simples.

No entanto, o efeito colateral indesejado é que você precisa se lembrar de excluir essas tabelas após concluir sua análise; caso contrário, essas tabelas permanecerão no banco de dados, criando assim problemas de gerenciamento de banco de dados.

A alternativa à criação de várias tabelas é usar a cláusula WITH no SQL.

Sintaxe

A sintaxe básica para a cláusula WITH é a seguinte:

WITH <nome_da_consulta_1> AS (
SELECT Declaração 1
)
Declaração SELECT Principal

O uso da cláusula WITH é muito semelhante à criação de tabelas. Quando você cria uma tabela, dá a ela um nome. Da mesma forma, ao usar a cláusula WITH, você também dá a ela um nome, e esse nome age essencialmente como um nome de tabela na declaração SQL principal.

Porque o WITH não cria uma tabela ou uma visualização, o objeto associado à declaração WITH desaparece após a execução da declaração SQL principal e não há nada para limpar.

Você também pode ter várias cláusulas WITH. A sintaxe é a seguinte:

WITH <nome_da_consulta_1> AS (
SELECT Declaração 1
), <nome_da_consulta_2> AS (
SELECT Declaração 2
),
..
<nome_da_consulta_n> AS (
SELECT Declaração N
)
Declaração SELECT Principal

Para ter várias cláusulas WITH, você não precisa especificar WITH várias vezes. Em vez disso, após a conclusão da primeira cláusula WITH, adicione uma vírgula e, em seguida, você pode especificar a próxima cláusula começando com <nome_da_consulta> seguido de COMO. Não há vírgula entre a última cláusula WITH e a consulta SQL principal.

Exemplos

Usamos a seguinte tabela em nossos exemplos.

Tabela Store_Sales

Store_Name Sales

Los Angeles 15000

San Diego 2500

New York 3000

Boston 7000

Exemplo 1: Usar o WITH em uma declaração SELECT

Digamos que queremos listar todas as lojas que têm mais vendas do que a média. Para fazer isso, podemos usar a seguinte declaração WITH:

WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

Resultado:

Store_Name	Sales
Los Angeles	15000
Boston	7000

A declaração WITH calcula o valor médio das vendas (que é de $6.875), e então a consulta SQL principal simplesmente retorna todas as linhas em que o valor da coluna Vendas é maior do que esse valor médio.

Um SQL equivalente usando uma visualização em linha seria:

SELECT a1.* FROM Store_Sales a1,
(SELECT AVG(Sales) AVG_SALES FROM Store_Sales) t1
WHERE a1.Sales > t1.AVG_SALES;

Da mesma forma, isso pode ser alcançado através do seguinte SQL usando uma subconsulta:

SELECT a1.* FROM Store_Sales a1
WHERE a1.Sales >
(SELECT AVG(Sales) AVG_SALES FROM Store_Sales);

Observe que a versão com subconsulta não funciona no Hive SQL devido à forma como o Hive SQL suporta subconsultas.

Exemplo 2: Usar o WITH em uma instrução CREATE TABLE

Também podemos usar o WITH juntamente com uma instrução CREATE TABLE. Digamos que desejamos criar uma tabela usando a cláusula WITH no exemplo anterior, digitamos:

CREATE TABLE Above_Average_Sales
AS
WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

A parte WITH da consulta vem após o CREATE TABLE AS. Não coloque a cláusula WITH no início. A consulta abaixo resultará em um erro:

WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
CREATE TABLE Above_Average_Sales
AS
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

SQL EXISTS >>

Esta página foi atualizada pela última vez em 15/10/2023

	SQL WITH
Tutorial de SQL Comandos SQL Manipulação de Tabelas SQL Avançados SQL Sintaxe