Firebird - Conhecendo as Subqueries no Firebird

O que é uma Subquery?

Podemos definir como um comando SELECT dentro de outro comando SELECT, UPDATE, DELETE ou INSERT, é muito utilizado e sua principal finalidade é a de retornar para a Query principal um conjunto de linhas, ou seja, a subquery é executada antes da consulta principal. As Subqueries podem correlacionar tanto com tabelas distintas quanto consigo mesma.

Temos dois tipos de subqueries (subconsultas):

• Subquery que retorna uma única linha. Estas utilizam os operadores = , > , >= , < , <= e <>.

• Subquery que retorna várias linhas. Utiliza o operador IN.

Utilidade

As denominadas Subqueries são muito úteis e essenciais para o trabalho com Bancos de Dados, neste caso estaremos montando e aprendendo a utilizá-la no Firebird.  

Para demonstrar seu poderio iremos criar duas tabelas: uma chamada TBFORNECEDOR e outra TBPRODUTO, teremos um relacionamento 1 -> N entre estas duas entidades. Figura 01,  veja abaixo a instrução SQL:

Figura 01. Relacionamento 1 para N.

CREATE TABLE TBFORNECEDOR (

    COD_FORNECEDOR   INTEGER NOT NULL,

    NOME_FORNECEDOR  VARCHAR(60)

);

CREATE TABLE TBPRODUTO (

    COD_PRODUTO     INTEGER NOT NULL,

    NOME_PRODUTO    VARCHAR(60),

    VALOR           NUMERIC(15,2),

    COD_FORNECEDOR  INTEGER

);

Preencheremos com dados.

COD_FORNECEDOR    NOME_FORNECEDOR

=================     ===================

                                       1     THE CLUB

                                       2     FATEC

        3    ACTIVENET

        4    AVARÉ 

COD_PRODUTO    COD_FORNECEDOR    NOME_PRODUTO    VALOR     DATA

==============    =================    ===============    ======     ========

                               1                                             1      SUPORTE                     80,00         10/10/2007

                               2                                             1      REVISTA                      80,00         08/10/2007

                               3                                             3      INTERNET                   60,00         25/10/2007

                               4                                             3      INSTALAÇÃO             55,00         27/10/2007

Utilizando em SELECT

Recuperando os fornecedores que possuem registros na tabela de produtos.

select *

from TBFORNECEDOR

where COD_FORNECEDOR in (select COD_FORNECEDOR from TBPRODUTO)

Poderíamos também utilizar a cláusula Exists que o resultado seria o mesmo.

select *

from TBFORNECEDOR F

where not exists (select COD_FORNECEDOR from TBPRODUTO P where F.cod_fornecedor=P.cod_fornecedor)

COD_FORNECEDOR    NOME_FORNECEDOR

=================     ===================

                                       1     THE CLUB

                                       3    ACTIVENET

  

Importante: O Operador in ou a cláusula Exists é utilizado quando temos mais de um resultado, caso contrário poderia utilizar o sinal de (=).

Poderíamos facilmente recuperar os registros que não possuem referência na tabela de fornecedor utilizando o not in ou not exists.

select *

from TBFORNECEDOR

where COD_FORNECEDOR not in (select COD_FORNECEDOR from TBPRODUTO)

ou

select *

from TBFORNECEDOR F

where not Exists (select COD_FORNECEDOR from TBPRODUTO P where F.cod_fornecedor=P.cod_fornecedor)

COD_FORNECEDOR    NOME_FORNECEDOR

=================     ===================

2         FATEC

4     AVARÉ

Podermos utilizar a cláusula where em nosso SubSelect, abaixo traremos os fornecedores que possuem os valores dos produtos com valor inferior a 60.

select *

from TBFORNECEDOR

where COD_FORNECEDOR in (select COD_FORNECEDOR from TBPRODUTO where valor <= 60)

COD_FORNECEDOR    NOME_FORNECEDOR

=================     ===================

3         ACTIVENET

Utilizando em UPDATE

Iremos atualizar todos os fornecedores que tiverem o código na tabela de produtos.

update tbfornecedor set nome_fornecedor = 'The Club'

where cod_fornecedor in (select cod_fornecedor from tbproduto)

 Utilizando em DELETE

Deletaremos todos os fornecedores que tiverem o código na tabela de produtos

delete from tbfornecedor

where cod_fornecedor in (select cod_fornecedor from tbproduto)

  

Exemplos de utilização

A seguir iremos simular algumas situações e resolvê-las com as denominadas subqueries.

  

Excluindo registros repetidos

Suponhamos que temos uma tabela com o código repetido, causado por uma falha de integridade referencial, assim teríamos que excluí-los. Para resolver este problema faremos um Delete seguido de um select agrupando pelo código e utilizando a cláusula having para nos trazer apenas os registros repetidos.

Delete from TBPRODUTO

where (COD_FORNECEDOR) in (select COD_FORNECEDOR from TBPRODUTO group by COD_FORNECEDOR having count(*)>1)

  

Trazendo os registros que possuem a maior data

Imaginem que precisamos trazer apenas os registros da tabela de produto que possuem a maior data, inicialmente faremos um select na tabela de produtos, com os campos desejados, em seguida outro trazendo a maior data agrupando pelo código. E por final na query principal, agrupamos pelos campos informados anteriormente.

select cod_fornecedor, nome_produto, data from tbproduto

where data in

(select max(data) from tbproduto group by cod_fornecedor)

group by cod_fornecedor, nome_produto, data

COD_FORNECEDOR    NOME_PRODUTO    DATA

 =================    ===============    =======

                                         1      SUPORTE                  10/10/2007

                                         3      INSTALAÇÃO          27/10/2007

Copiando registros de uma tabela para outra de mesma estrutura

Várias vezes precisamos transferir os registros de uma tabela para outra, para isto temos vários recursos, tais como: utilizar o Delphi, por Stored Procedures, entre outras coisas. Mas a maneira mais fácil e rápida seria utilizando subqueries.

O primeiro passo é fazer um insert na tabela destino, no nosso a produto, informar os campos entre parênteses e depois realizar um select na tabela origem.

Observação: a quantidade e tipo de campos deverá ser a mesma da tabela destino com a de origem.

insert into produto

(cod_produto, nome_produto, valor, cod_fornecedor)

select cod_produto, nome_produto, valor, cod_fornecedor from tbproduto

  

Conclusão

Pudemos aprender um pouco sobre subqueries e perceber a importância delas em nossos sistemas. Verificamos alguns exemplos práticos a fim de poder aprender e colocá-las em prática. Sucesso a todos e até a próxima.