7.2. Expressões de tabela

PostgreSQL 14.5: Expressões de tabela

Uma expressão de tabela computa uma tabela. A expressão de tabela contém a cláusula FROM seguida, opcionalmente, pelas cláusulas WHERE, GROUP BY e HAVING. As expressões de tabela triviais fazem, simplesmente, referência as tão faladas tabelas em disco, chamadas de tabelas base, mas podem ser utilizadas expressões mais complexas para modificar ou combinar tabelas base de várias maneiras.

As cláusulas opcionais WHERE, GROUP BY e HAVING, da expressão de tabela, especificam um processo de transformações sucessivas realizadas na tabela produzida pela cláusula FROM. Todas estas transformações produzem uma tabela virtual que fornece as linhas passadas para a lista de seleção, para então serem computadas as linhas de saída da consulta.

7.2.1. A cláusula FROM

A Cláusula FROM deriva uma tabela a partir de uma ou mais tabelas especificadas na lista, separada por vírgulas, de referências a tabela.

FROM referência_a_tabela [, referência_a_tabela [, ...]]

Uma referência a tabela pode ser um nome de tabela (possivelmente qualificado pelo esquema) ou uma tabela derivada, como uma subconsulta, uma junção de tabelas ou, ainda, uma combinação complexa destas. Se for listada mais de uma referência a tabela na cláusula FROM, é feita uma junção cruzada (cross-join) (veja abaixo) para formar a tabela virtual intermediária que poderá, então, estar sujeita às transformações das cláusulas WHERE, GROUP BY e HAVING, gerando o resultado final de toda a expressão de tabela.

Quando uma referência a tabela especifica uma tabela ancestral em uma hierarquia de herança de tabelas, a referência a tabela não produz linhas apenas desta tabela, mas inclui as linhas de todas as tabelas descendentes, a não ser que a palavra chave ONLY preceda o nome da tabela. Entretanto, esta referência produz apenas as colunas existentes na tabela especificada — são ignoradas todas as colunas adicionadas às tabelas descendentes.

7.2.1.1. Junção de tabelas

Uma tabela juntada é uma tabela derivada de outras duas tabelas (reais ou derivadas), de acordo com as regras do tipo particular de junção. Estão disponíveis as junções internas, externas e cruzadas.

Tipos de junção

Junção cruzada
T1 CROSS JOIN T2

Para cada combinação de linhas de T1 e T2, a tabela derivada contém uma linha formada por todas as colunas de T1 seguidas por todas as colunas de T2. Se as tabelas possuírem N e M linhas, respectivamente, a tabela juntada terá N * M linhas.

FROM T1 CROSS JOIN T2 equivale a FROM T1, T2. Também equivale a FROM T1 INNER JOIN T2 ON TRUE (veja abaixo).

Junções qualificadas
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON expressão_booleana
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( lista de colunas de junção )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

As palavras INNER e OUTER são opcionais em todas as formas. INNER é o padrão; LEFT, RIGHT e FULL implicam em junção externa.

A condição de junção é especificada na cláusula ON ou USING, ou implicitamente pela palavra NATURAL. A condição de junção determina quais linhas das duas tabelas de origem são consideradas "correspondentes", conforme explicado detalhadamente abaixo.

A cláusula ON é o tipo mais geral de condição de junção: recebe uma expressão de valor booleana do mesmo tipo utilizado na cláusula WHERE. Um par de linhas de T1 e T2 são correspondentes se a expressão da cláusula ON for avaliado como verdade para este par de linhas.

USING é uma notação abreviada: recebe uma lista de nomes de colunas, separados por vírgula, que as tabelas juntadas devem possuir em comum, e forma a condição de junção especificando a igualdade de cada par destas colunas. Além disso, a saída de JOIN USING possui apenas uma coluna para cada par da igualdade de colunas da entrada, seguidas por todas as outras colunas de cada tabela. Portanto, USING (a, b, c) equivale a ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c), mas quando ON é utilizado existem duas colunas a, b e c no resultado, enquanto usando USING existe apenas uma de cada.

Finalizando, NATURAL é uma forma abreviada de USING: gera uma lista USING formada pelas colunas cujos nomes aparecem nas duas tabelas de entrada. Assim como no USING, estas colunas aparecem somente uma vez na tabela de saída.

Os tipos possíveis de junção qualificada são:

INNER JOIN

Para cada linha L1 de T1, a tabela juntada possui uma linha para cada linha de T2 que satisfaz a condição de junção com L1.

LEFT OUTER JOIN

Primeiro, é realizada uma junção interna. Depois, para cada linha de T1 que não satisfaz a condição de junção com nenhuma linha de T2, é adicionada uma linha juntada com valores nulos nas colunas de T2. Portanto, a tabela juntada possui, incondicionalmente, no mínimo uma linha para cada linha de T1.

RIGHT OUTER JOIN

Primeiro, é realizada uma junção interna. Depois, para cada linha de T2 que não satisfaz a condição de junção com nenhuma linha de T1, é adicionada uma linha juntada com valores nulos nas colunas de T1. É o oposto da junção esquerda: a tabela resultante possui, incondicionalmente, uma linha para cada linha de T2.

FULL OUTER JOIN

Primeiro, é realizada uma junção interna. Depois, para cada linha de T1 que não satisfaz a condição de junção com nenhuma linha de T2, é adicionada uma linha juntada com valores nulos nas colunas de T2. Também, para cada linha de T2 que não satisfaz a condição de junção com nenhuma linha de T1, é adicionada uma linha juntada com valores nulos nas colunas de T1.

As junções de todos os tipos podem ser encadeadas ou aninhadas: tanto T1 como T2, ou ambas, podem ser tabelas juntadas. Podem colocados parênteses em torno das cláusulas JOIN para controlar a ordem de junção. Na ausência de parênteses, as cláusulas JOIN são aninhadas da esquerda para a direita.

Para reunir tudo isto, vamos supor que temos as tabelas t1

 num | nome
-----+------
   1 | a
   2 | b
   3 | c

e t2

 num | valor
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

e mostrar os resultados para vários tipos de junção:

=> SELECT * FROM t1 CROSS JOIN t2;

 num | nome | num | valor
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 linhas)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;

 num | nome | num | valor
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 linhas)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);

 num | nome | valor
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 linhas)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;

 num | nome | valor
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 linhas)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;

 num | nome | num | valor
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 linhas)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);

 num | nome | valor
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 linhas)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;

 num | nome | num | valor
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 linhas)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;

 num | nome | num | valor
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 linhas)

A condição de junção especificada em ON também pode conter condições não relacionadas diretamente com a junção. Pode ser útil em algumas consultas, mas deve ser usado com cautela. Por exemplo:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.valor = 'xxx';

 num | nome | num | valor
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 linhas)

A Tabela 7-1 mostra os tipos de junção suportados pelos gerenciadores de banco de dados PostgreSQL, SQL Server, Oracle e DB2. [1]

Tabela 7-1. Tipos de junção no PostgreSQL, no SQL Server, no Oracle e no DB2

Tipo de junção PostgreSQL 8.0.0 SQL Server 2000 Oracle 10g DB2 8.1
INNER JOIN ON sim sim sim sim
LEFT OUTER JOIN ON sim sim sim sim
RIGHT OUTER JOIN ON sim sim sim sim
FULL OUTER JOIN ON sim sim sim sim
INNER JOIN USING sim não sim não
CROSS JOIN sim sim sim não
NATURAL JOIN sim não sim não

7.2.1.2. Aliases de tabela e de coluna

Pode ser dado um nome temporário às tabelas, e às referências a tabela complexas, para ser usado nas referências à tabela derivada no restante do comando. Isto é chamado de aliás de tabela. [2] [3]

Para criar um aliás de tabela, escreve-se

FROM referência_a_tabela AS aliás

ou

FROM referência_a_tabela aliás

A palavra chave AS é opcional. O aliás pode ser qualquer identificador.

Uma utilização típica de aliás de tabela é para atribuir identificadores curtos a nomes de tabelas longos, para manter a cláusula de junção legível. Por exemplo:

SELECT * FROM um_nome_muito_comprido u JOIN outro_nome_muito_comprido o ON u.id = o.num;

O aliás se torna o novo nome da referência à tabela na consulta corrente — não é mais possível fazer referência à tabela pelo seu nome original. Portanto,

SELECT * FROM minha_tabela AS m WHERE minha_tabela.a > 5;

não é uma sintaxe SQL válida. O que acontece de verdade (isto é uma extensão do PostgreSQL ao padrão), é que uma referência a tabela implícita é adicionada à cláusula FROM. Portanto, a consulta é processada como se tivesse sido escrita assim

SELECT * FROM minha_tabela AS m, minha_tabela AS minha_tabela WHERE minha_tabela.a > 5;

resultando em uma junção cruzada, que geralmente não é o que se deseja.

Os aliases de tabela servem principalmente como uma notação conveniente, mas sua utilização é necessária para fazer a junção de uma tabela consigo mesma. Por exemplo:

SELECT * FROM minha_tabela AS a CROSS JOIN minha_tabela AS b ...

Além disso, um aliás é requerido se a referência a tabela for uma subconsulta (consulte a Seção 7.2.1.3).

Os parênteses são utilizados para resolver ambigüidades. A declaração abaixo atribui o aliás b ao resultado da junção, diferentemente do exemplo anterior:

SELECT * FROM (minha_tabela AS a CROSS JOIN minha_tabela) AS b ...

Uma outra forma de aliás de tabela especifica nomes temporários para as colunas da tabela, assim como para a mesma:

FROM referência_a_tabela [AS] aliás ( coluna1 [, coluna2 [, ...]] )

Se for especificado um número de aliases de coluna menor que o número de colunas da tabela, as demais colunas não terão o nome mudado. Esta sintaxe é especialmente útil em autojunções e subconsultas.

Quando um aliás é aplicado à saída da cláusula JOIN, utilizando qualquer uma destas formas, o aliás esconde o nome original dentro do JOIN. Por exemplo:

SELECT a.* FROM minha_tabela AS a JOIN sua_tabela AS b ON ...

é um comando SQL válido, mas

SELECT a.* FROM (minha_tabela AS a JOIN sua_tabela AS b ON ...) AS c

não é válido: o aliás de tabela a não é visível fora do aliás c.

7.2.1.3. Subconsultas

Subconsultas especificando uma tabela derivada devem estar entre parênteses, e devem ter um nome de aliás de tabela atribuído (consulte a Seção 7.2.1.2). Por exemplo:

FROM (SELECT * FROM tabela1) AS nome_aliás

Este exemplo equivale a FROM tabela1 AS nome_aliás. Casos mais interessantes, que não podem ser reduzidos a junções simples, ocorrem quando a subconsulta envolve agrupamento ou agregação.

7.2.1.4. Funções de tabela

As funções de tabela são funções que produzem um conjunto de linhas, formadas por um tipo de dado base (tipos escalar), ou por um tipo de dado composto (linhas de tabela). São utilizadas como uma tabela, visão ou subconsulta na cláusula FROM da consulta. As colunas retornadas pelas funções de tabela podem ser incluídas nas cláusulas SELECT, JOIN ou WHERE da mesma maneira que uma coluna de tabela, visão ou de subconsulta.

Se a função de tabela retornar um tipo de dado base, a única coluna do resultado recebe o nome da função. Se a função retornar um tipo composto, as colunas do resultado recebem o mesmo nome dos atributos individuais do tipo.

A função de tabela pode receber um aliás na cláusula FROM, mas também pode ser deixada sem aliás. Se a função for utilizada na cláusula FROM sem aliás, o nome da função é utilizado como o nome da tabela resultante.

Alguns exemplos:

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (select foosubid from getfoo(foo.fooid) z
                           where z.fooid = foo.fooid);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

Em alguns casos é útil definir funções de tabela que possam retornar conjuntos de colunas diferentes dependendo de como são chamadas. Para permitir que isto seja feito, a função de tabela pode ser declarada como retornando o pseudotipo record. Quando este tipo de função é utilizada em uma consulta, a estrutura esperada para a linha deve ser especificada na própria consulta, para que o sistema possa saber como analisar e planejar a consulta. Considere o seguinte exemplo:

SELECT *
    FROM dblink('dbname=meu_bd', 'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

A função dblink executa uma consulta remota (consulte contrib/dblink). É declarada como retornando record, uma vez que pode ser utilizada em qualquer tipo de consulta. O conjunto real de colunas deve ser especificado na consulta fazendo a chamada, para que o analisador saiba, por exemplo, como expandir o *.

7.2.2. A cláusula WHERE

A sintaxe da Cláusula WHERE é

WHERE condição_de_pesquisa

onde a condição_de_pesquisa é qualquer expressão de valor (consulte a Seção 4.2) que retorne um valor do tipo boolean.

Após o processamento da cláusula FROM ter sido feito, cada linha da tabela virtual derivada é verificada com relação à condição de pesquisa. Se o resultado da condição for verdade, a linha é mantida na tabela de saída, senão (ou seja, se o resultado for falso ou nulo) a linha é desprezada. Normalmente a condição de pesquisa faz referência a pelo menos uma coluna da tabela gerada pela cláusula FROM; embora isto não seja requerido, se não for assim a cláusula WHERE não terá utilidade.

Nota: A condição de junção de uma junção interna pode ser escrita tanto na cláusula WHERE quanto na cláusula JOIN. Por exemplo, estas duas expressões de tabela são equivalentes:

FROM a, b WHERE a.id = b.id AND b.val > 5

e

FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5

ou talvez até mesmo

FROM a NATURAL JOIN b WHERE b.val > 5

Qual destas formas deve ser utilizada é principalmente uma questão de estilo. A sintaxe do JOIN na cláusula FROM provavelmente não é muito portável para outros sistemas gerenciadores de banco de dados SQL. Para as junções externas não existe escolha em nenhum caso: devem ser feitas na cláusula FROM. A cláusula ON/USING da junção externa não é equivalente à condição WHERE, porque determina a adição de linhas (para as linhas de entrada sem correspondência) assim como a remoção de linhas do resultado final.

Abaixo estão mostrados alguns exemplos de cláusulas WHERE:

SELECT ... FROM fdt WHERE c1 > 5

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)

sendo que fdt é a tabela derivada da cláusula FROM. As linhas que não aderem à condição de pesquisa da cláusula WHERE são eliminadas de fdt. Deve ser observada a utilização de subconsultas escalares como expressões de valor. Assim como qualquer outra consulta, as subconsultas podem utilizar expressões de tabela complexas. Deve ser observado, também, como fdt é referenciada nas subconsultas. A qualificação de c1 como fdt.c1 somente será necessária se c1 também for o nome de uma coluna na tabela de entrada derivada da subconsulta. Entretanto, a qualificação do nome da coluna torna mais clara a consulta, mesmo quando não é necessária. Este exemplo mostra como o escopo do nome da coluna de uma consulta externa se estende às suas consultas internas.

7.2.3. As cláusulas GROUP BY e HAVING

Após passar pelo filtro WHERE, a tabela de entrada derivada pode estar sujeita ao agrupamento, utilizando a cláusula GROUP BY, e à eliminação de grupos de linhas, utilizando a cláusula HAVING.

SELECT lista_de_seleção
    FROM ...
    [WHERE ...]
    GROUP BY referência_a_coluna_de_agrupamento [, referência_a_coluna_de_agrupamento]...

A Cláusula GROUP BY é utilizada para agrupar linhas da tabela que compartilham os mesmos valores em todas as colunas da lista. Em que ordem as colunas são listadas não faz diferença. O efeito é combinar cada conjunto de linhas que compartilham valores comuns em uma linha de grupo que representa todas as linhas do grupo. Isto é feito para eliminar redundância na saída, e/ou para calcular agregações aplicáveis a estes grupos. Por exemplo:

=> SELECT * FROM teste1;

 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 linhas)

=> SELECT x FROM teste1 GROUP BY x;

 x
---
 a
 b
 c
(3 linhas)

Na segunda consulta não poderia ser escrito SELECT * FROM teste1 GROUP BY x, porque não existe um valor único da coluna y que poderia ser associado com cada grupo. As colunas agrupadas podem ser referenciadas na lista de seleção, desde que possuam um valor único em cada grupo.

De modo geral, se uma tabela for agrupada as colunas que não são usadas nos agrupamentos não podem ser referenciadas, exceto nas expressões de agregação. Um exemplo de expressão de agregação é:

=> SELECT x, sum(y) FROM teste1 GROUP BY x;

 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 linhas)

Aqui sum() é a função de agregação que calcula um valor único para o grupo todo. Mais informações sobre as funções de agregação disponíveis podem ser encontradas na Seção 9.15.

Dica: Um agrupamento sem expressão de agregação computa, efetivamente, o conjunto de valores distintas na coluna. Também poderia ser obtido por meio da cláusula DISTINCT (consulte a Seção 7.3.3).

Abaixo está mostrado um outro exemplo: cálculo do total das vendas de cada produto (e não o total das vendas de todos os produtos).

SELECT cod_prod, p.nome, (sum(v.unidades) * p.preco) AS vendas
    FROM produtos p LEFT JOIN vendas v USING (cod_prod)
    GROUP BY cod_prod, p.nome, p.preco;

Neste exemplo, as colunas cod_prod, p.nome e p.preco devem estar na cláusula GROUP BY, porque são referenciadas na lista de seleção da consulta (dependendo da forma exata como a tabela produtos for definida, as colunas nome e preço podem ser totalmente dependentes da coluna cod_prod, tornando os agrupamentos adicionais teoricamente desnecessários, mas isto ainda não está implementado). A coluna v.unidades não precisa estar na lista do GROUP BY, porque é usada apenas na expressão de agregação (sum(...)), que representa as vendas do produto. Para cada produto, a consulta retorna uma linha sumarizando todas as vendas do produto.

No SQL estrito, a cláusula GROUP BY somente pode agrupar pelas colunas da tabela de origem, mas o PostgreSQL estende esta funcionalidade para permitir o GROUP BY agrupar pelas colunas da lista de seleção. O agrupamento por expressões de valor, em vez de nomes simples de colunas, também é permitido.

Se uma tabela for agrupada utilizando a cláusula GROUP BY, mas houver interesse em alguns grupos apenas, pode ser utilizada a cláusula HAVING, de forma parecida com a cláusula WHERE, para eliminar grupos da tabela agrupada. A sintaxe é:

SELECT lista_de_seleção FROM ... [WHERE ...] GROUP BY ... HAVING expressão_booleana

As expressões na cláusula HAVING podem fazer referência tanto a expressões agrupadas quanto a não agrupadas (as quais necessariamente envolvem uma função de agregação).

Exemplo:

=> SELECT x, sum(y) FROM teste1 GROUP BY x HAVING sum(y) > 3;

 x | sum
---+-----
 a |   4
 b |   5
(2 linhas)

=> SELECT x, sum(y) FROM teste1 GROUP BY x HAVING x < 'c';

 x | sum
---+-----
 a |   4
 b |   5
(2 linhas)

Agora vamos fazer um exemplo mais próximo da realidade:

SELECT cod_prod, p.nome, (sum(v.unidades) * (p.preco - p.custo)) AS lucro
    FROM produtos p LEFT JOIN vendas v USING (cod_prod)
    WHERE v.data > CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY cod_prod, p.nome, p.preco, p.custo
    HAVING sum(p.preco * v.unidades) > 5000;

No exemplo acima, a cláusula WHERE está selecionando linhas por uma coluna que não é agrupada (a expressão somente é verdadeira para as vendas feitas nas quatro últimas semanas, enquanto a cláusula HAVING restringe a saída aos grupos com um total de vendas brutas acima de 5000. Deve ser observado que as expressões de agregação não precisam ser necessariamente as mesmas em todas as partes da consulta.

Exemplo 7-1. Utilização de HAVING sem GROUP BY no SELECT

O exemplo abaixo mostra a utilização da cláusula HAVING sem a cláusula GROUP BY no comando SELECT. É criada a tabela produtos e são inseridas cinco linhas. Quando a cláusula HAVING exige a presença de mais de cinco linhas na tabela, a consulta não retorna nenhuma linha. [4] [5]

=> create global temporary table produtos(codigo int, valor float);
=> insert into produtos values (1, 102);
=> insert into produtos values (2, 104);
=> insert into produtos values (3, 202);
=> insert into produtos values (4, 203);
=> insert into produtos values (5, 204);

=> select avg(valor) from produtos;

 avg
-----
 163
(1 linha)

=> select avg(valor) from produtos having count(*)>=5;

 avg
-----
 163
(1 linha)

=> select avg(valor) from produtos having count(*)=5;

 avg
-----
 163
(1 linha)

=> select avg(valor) from produtos having count(*)>5;

 avg
-----
(0 linhas)

Exemplo 7-2. Utilização da expressão CASE para agrupar valores

A expressão CASE pode fazer parte da lista de agrupamento. Este exemplo usa a expressão CASE para agrupar as notas dos alunos em conceitos, e calcular a nota mínima, máxima e média, além da quantidade de notas, correspondente a cada conceito. Abaixo está mostrado o script utilizado: [6] [7]

CREATE TABLE notas (
    nota decimal(4,2) CONSTRAINT chknota
                      CHECK (nota BETWEEN 0.00 AND 10.00)
);
INSERT INTO notas VALUES(10);
INSERT INTO notas VALUES(9.2);
INSERT INTO notas VALUES(9.0);
INSERT INTO notas VALUES(8.3);
INSERT INTO notas VALUES(7.7);
INSERT INTO notas VALUES(7.4);
INSERT INTO notas VALUES(6.4);
INSERT INTO notas VALUES(5.8);
INSERT INTO notas VALUES(5.1);
INSERT INTO notas VALUES(5.0);
INSERT INTO notas VALUES(0);
SELECT CASE
          WHEN nota < 3 THEN 'E'
          WHEN nota < 5 THEN 'D'
          WHEN nota < 7 THEN 'C'
          WHEN nota < 9 THEN 'B'
          ELSE 'A'
       END AS conceito,
       COUNT(*) AS quantidade,
       MIN(nota) AS menor,
       MAX(nota) AS maior,
       AVG(nota) AS media
FROM notas
GROUP BY CASE
            WHEN nota < 3 THEN 'E'
            WHEN nota < 5 THEN 'D'
            WHEN nota < 7 THEN 'C'
            WHEN nota < 9 THEN 'B'
            ELSE 'A'
         END
ORDER BY conceito;

A seguir estão mostrados os resultados obtidos:

 conceito | quantidade | menor | maior |         media
----------+------------+-------+-------+------------------------
 A        |          3 |  9.00 | 10.00 |     9.4000000000000000
 B        |          3 |  7.40 |  8.30 |     7.8000000000000000
 C        |          4 |  5.00 |  6.40 |     5.5750000000000000
 E        |          1 |  0.00 |  0.00 | 0.00000000000000000000
(4 linhas)

Exemplo 7-3. Utilização da expressão CASE em chamada de função

A expressão CASE pode ser usada como argumento de chamada de função. Este exemplo usa a expressão CASE como argumento da função COUNT, passando o valor 1 quando a nota corresponde ao conceito, e nulo quando não corresponde. Desta forma, a função COUNT conta a quantidade de notas presentes em cada conceito, uma vez que os valores nulos não são contados. Os dados são os mesmos do exemplo anterior. Abaixo está mostrada a consulta utilizada: [8] [9]

SELECT COUNT(CASE WHEN nota BETWEEN 9.00 AND 10.00 THEN 1 ELSE NULL END) AS A,
       COUNT(CASE WHEN nota BETWEEN 7.00 AND  8.99 THEN 1 ELSE NULL END) AS B,
       COUNT(CASE WHEN nota BETWEEN 5.00 AND  6.99 THEN 1 ELSE NULL END) AS C,
       COUNT(CASE WHEN nota BETWEEN 3.00 AND  4.99 THEN 1 ELSE NULL END) AS D,
       COUNT(CASE WHEN nota BETWEEN 0.00 AND  2.99 THEN 1 ELSE NULL END) AS E
FROM notas;

A seguir estão mostrados os resultados obtidos:

 a | b | c | d | e
---+---+---+---+---
 3 | 3 | 4 | 0 | 1
(1 linha)

Desta forma, foi mostrado em uma linha o mesmo resultado da coluna quantidade do exemplo anterior.

Exemplo 7-4. Combinação de informação agrupada e não agrupada

Os comandos SELECT que retornam apenas uma linha [10] podem ser utilizados para combinar informações agrupadas com informações não agrupadas na mesma consulta. Neste exemplo cada nota é mostrada junto com a menor nota, a maior nota, e a média de todas as notas. Os dados são os mesmos dos dois exemplos anteriores. Abaixo está mostrada a consulta utilizada: [11] [12]

SELECT nota,
       (SELECT MIN(nota) FROM notas) AS menor,
       (SELECT MAX(nota) FROM notas) AS maior,
       (SELECT AVG(nota) FROM notas) AS media
FROM notas;

A seguir estão mostrados os resultados obtidos:

 nota  | menor | maior |       media
-------+-------+-------+--------------------
 10.00 |  0.00 | 10.00 | 6.7181818181818182
  9.20 |  0.00 | 10.00 | 6.7181818181818182
  9.00 |  0.00 | 10.00 | 6.7181818181818182
  8.30 |  0.00 | 10.00 | 6.7181818181818182
  7.70 |  0.00 | 10.00 | 6.7181818181818182
  7.40 |  0.00 | 10.00 | 6.7181818181818182
  6.40 |  0.00 | 10.00 | 6.7181818181818182
  5.80 |  0.00 | 10.00 | 6.7181818181818182
  5.10 |  0.00 | 10.00 | 6.7181818181818182
  5.00 |  0.00 | 10.00 | 6.7181818181818182
  0.00 |  0.00 | 10.00 | 6.7181818181818182
(11 linhas)

Notas

[1]

Tabela escrita pelo tradutor, não fazendo parte do manual original.

[2]

SQL Serveraliás — Um nome alternativo para tabela ou coluna em expressões, geralmente utilizado para encurtar o nome para uma referência subseqüente no código, evitar possíveis referências ambíguas, ou fornecer um nome mais descritivo na saída do comando. Um aliás também pode ser um nome alternativo para o servidor. SQL Server 2005 Books Online — Glossary of Terms (N. do T.)

[3]

DB2aliás — Um nome alternativo utilizado para identificar uma tabela, visão, banco de dados ou apelido. Um aliás pode ser utilizado em instruções SQL para referir-se a uma tabela ou visão no mesmo sistema ou subsistema DB2 ou em um sistema ou subsistema DB2 remoto. Glossário do DB2 (N. do T.)

[4]

Exemplo escrito pelo tradutor, não fazendo parte do manual original.

[5]

Oracle — Este exemplo foi executado no Oracle 10g e produziu os mesmos resultados.

[6]

Exemplo escrito pelo tradutor, não fazendo parte do manual original, baseado no exemplo do livro DB2® Universal Database™ V8 for Linux, UNIX, and Windows Database Administration Certification Guide, 5th Edition, George Baklarz e Bill Wong, Series IBM Press, Prentice Hall Professional Technical Reference, 2003, pág. 375.

[7]

Este exemplo foi executado no SQL Server 2000, no Oracle 10g e no DB2 8.1 sem alterações, produzindo o mesmo resultado.

[8]

Exemplo escrito pelo tradutor, não fazendo parte do manual original, baseado em exemplo do mesmo livro do exemplo anterior, pág. 376.

[9]

Esta consulta foi executada no SQL Server 2000, no Oracle 10g e no DB2 8.1 sem alterações, produzindo o mesmo resultado.

[10]

Scalar-fullselect — É um comando SELECT completo entre parênteses, que retorna uma única linha contendo um único valor de coluna. Se não retornar nenhuma linha o resultado da expressão é o valor nulo, e se retornar mais de uma linha ocorre um erro.

[11]

Exemplo escrito pelo tradutor, não fazendo parte do manual original, baseado em exemplo do mesmo livro do exemplo anterior, pág. 316.

[12]

Esta consulta foi executada no SQL Server 2000, no Oracle 10g e no DB2 8.1 sem alterações, produzindo o mesmo resultado.

SourceForge.net Logo CSS válido!