Multiplos ‘With’

Olá pessoal.

Venho aqui com uma dica bem útil de SQL, que é quando trabalhamos com o “WITH”.

O “WITH” é muito útil, pois ele permite externar uma subconsulta como se esta fosse uma tabela, podendo ser novamente filtrada e se for o caso, com outros “WITH” e assim por diante.

Vamos a um exemplo simples do uso do “WITH”:

Imaginemos a seguinte tabela de usuários:

Tabela: Usuario
Campo: ID as integer
Campo: NOME as varchar(20)
Campo: LOGIN as varchar(8)
Campo: EMAIL as varchar(30)

Com os seguintes dados:
ID   NOME           LOGIN          SEM NOME
1    Juca Legal     juca.legal     juca.legal@email.com
2    Pedro Bacana   pedro.bacana   pedro.bacan@email.com
3    Suzan Suzana   suzan.suzana   
4    Janaina Ina    janaina.ina    janaina.ina@email.com
5    Lucas Louco    lucas.louco

 Vamos supor que eu queira filtrar todos os usuários que possuam e-mail e listar os e-mails deles, posso fazer tudo em uma única query, mas vou dividir em duas para exemplificar o “WITH”.

Então primeiro, vou filtrar todos os usuários, retornando “SIM” para os que possuem e-mail e “NAO” para os que não possuem e-mail cadastrado, dessa forma:

select ID,
       NOME,
       LOGIN,
       iif(EMAIL = '' or EMAIL = null,'NAO','SIM')
from Usuario

Esse select irá retornar os seguintes dados:
ID   NOME           LOGIN          SEM NOME
1    Juca Legal     juca.legal     SIM
2    Pedro Bacana   pedro.bacana   SIM
3    Suzan Suzana   suzan.suzana   NAO
4    Janaina Ina    janaina.ina    SIM
5    Lucas Louco    lucas.louco    NAO

 Com o “select” acima, filtramos todos usuários, informando na última coluna “SIM” e “NAO”.

Agora vamos transformar este select em uma tabela temporária com o “WITH”, que vou chamar de “EMAIL_SN” e vamos selecionar todos dados desta tabela temporária:

with EMAIL_SN as
(select ID as ID,
        NOME as NOME,
        LOGIN as LOGIN,
        iif(EMAIL = '' or EMAIL = null,'NAO','SIM') as SN
 from Usuario)

 select *
 from EMAIL_SN

ID   NOME           LOGIN          SN
1    Juca Legal     juca.legal     SIM
2    Pedro Bacana   pedro.bacana   SIM
3    Suzan Suzana   suzan.suzana   NAO
4    Janaina Ina    janaina.ina    SIM
5    Lucas Louco    lucas.louco    NAO

O “WITH” é usado seguido do nome da nossa tabela temporária: “EMAIL_SN”, após o nome usamos o comando “as” e em seguida colocamos nosso select entre parenteses “(select ID …)”, porém neste caso, todas as colunas do select dentro do parenteses do “with” precisam possuir nomes de colunas, como vocês podem notar, usei o mesmo nome dos campos, somente o último campo, que não possuía nome por ser um “iif”, eu nomeei de “SN”.

O “select” logo após o “with” retorna todos os dados da tabela temporária “EMAIL_SN”, com os mesmo dados, mas com os nomes de colunas que colocamos dentro do “select” do “with”.

Agora, vamos usar a nova coluna nomeada “SN” para fazer uma consulta de todos os usuários que possuem e-mail, com um “join”, e exibir seus nomes e e-mails:

with EMAIL_SN as
(select ID as ID,
        NOME as NOME,
        LOGIN as LOGIN,
        iif(EMAIL = '' or EMAIL = null,'NAO','SIM') as SN
 from Usuario)

 select US.Nome,
        US.Email
 from Usuarios US
 inner join EMAIL_SN EM on (US.Login = EM.Login and EM.SN = 'SIM')

Resultado:
NOME           EMAIL
Juca Legal     juca.legal@email.com
Pedro Bacana   pedro.bacan@email.com
Janaina Ina    janaina.ina@email.com

 Vejam que o “with” gerou os resultados com uma tabela temporária, fazendo um pré-filtro que em seguida utilizamos com um “join” para filtrar os usuários com e-mail e exibir Nome e Email de cada um. No exemplo acima, a consulta retornada é a do “select” logo abaixo do “with”, pois o “select” contido “with” ficam contidos na tabela temporária “EMAIL_SN”.

Usando o “WITH”, podemos fazer o mesmo com mais dados, vamos por exemplo, filtrar além dos usuários que possuem e-mail, filtrar somente o Pedro, usando outro “WITH” em conjunto com o primeiro:

with EMAIL_SN as
(select ID as ID,
        NOME as NOME,
        LOGIN as LOGIN,
        iif(EMAIL = '' or EMAIL = null,'NAO','SIM') as SN
 from Usuario),
EMAIL_PEDRO as
(select ID as ID,
        NOME as NOME,
        LOGIN as LOGIN
 from Usuario
 where NOME = 'Pedro Bacana')

select US.Nome,
       US.Email
from Usuarios US
inner join EMAIL_SN EM on (US.Login = EM.Login and EM.SN = 'SIM')
inner join EMAIL_PEDRO EP on (US.Login = EP.Login)

O resultado será:
NOME           EMAIL
Pedro Bacana   pedro.bacan@email.com

 Nos exemplos acima usei o “WITH” para exemplificar, pois nestas consultas eles não são necessários, mas acredito que tenha conseguido ser suficientemente didático.

Espero que tenha sido útil, e em caso de dúvidas, postem nos comentários.

T+