4.2. Expressão de tabela

Uma expressão de tabela produz uma tabela. A expressão de tabela contém uma cláusula FROM seguida, opcionalmente, pelas cláusulas WHERE, GROUP BY e HAVING. As expressões de tabela triviais simplesmente fazem referência às tabelas em disco, chamadas de tabelas base, mas expressões mais complexas podem ser utilizadas 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. Estas transformações produzem uma tabela virtual que fornece as linhas passadas para a lista de seleção, para então serem produzidas as linhas de saída da consulta.

4.2.1. A cláusula FROM

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

FROM referência_tabela [, referência_tabela [, ...]]

Uma referência de 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 mais de uma referência de tabela estiver presente na cláusula FROM é feita uma junção cruzada (cross-join) (veja abaixo) para produzir uma tabela virtual intermediária que, então, estará sujeita às transformações especificadas nas cláusulas WHERE, GROUP BY e HAVING, gerando o resultado final de toda a expressão de tabela.

Quando uma referência de tabela especifica uma tabela ancestral em uma hierarquia de herança de tabelas, a tabela referenciada não produz linhas de apenas uma 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 que aparecem na tabela especificada --- todas as colunas adicionadas às tabelas descendentes são ignoradas.

4.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 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 linha de T1 com 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 possuirem N e M linhas, respectivamente, a tabela juntada terá N * M linhas. Uma junção cruzada é equivalente a INNER JOIN ON TRUE.

Dica: FROM T1 CROSS JOIN T2 é equivalente a FROM T1, T2.

Junção qualificada
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 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 abaixo.

A cláusula ON é o tipo mais geral de condição de junção: recebe o valor de uma expressão 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 produz um resultado verdade para este par de linhas.

USING é uma notação abreviada: recebe uma lista separada por vírgulas contendo nomes de colunas que as tabelas juntadas possuem em comum, formando 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 de entrada, seguidas por todas as outras colunas de cada tabela. Portanto, USING (a, b, c) é equivalente a ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c), mas quando ON for utilizado vão existir duas colunas a, b e c no resultado, enquanto com USING existirá apenas uma de cada.

Finalizando, NATURAL é uma forma abreviada de USING: gera uma lista para o USING formada pelas colunas cujos nomes existem 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, uma junção interna é realizada. Depois, para cada linha de T1 que não satisfaz a condição de junção com nenhuma linha de T2, uma linha juntada é adicionada 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, uma junção interna é realizada. Depois, para cada linha de T2 que não satisfaz a condição de junção com nenhuma linha de T1, uma linha juntada é adicionada 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, uma junção interna é realizada. Depois, para cada linha de T1 que não satisfaz a condição de junção com nenhuma linha de T2, uma linha juntada é adicionada 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, uma linha juntada com valores nulos nas colunas de T1 é adicionada.

As junções de todos os tipos podem ser encadeadas ou aninhadas: tanto T1 como T2, ou ambas, podem ser tabelas juntadas. Parênteses podem colocados 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 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | nome | num | valor
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | nome | valor
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | nome | valor
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> 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 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | nome | valor
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> 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 rows)

=> 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 rows)

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 rows)

4.2.1.2. Aliás para tabela e coluna

Nomes temporários podem ser atribuídos a tabelas, e a referências de tabela complexas, para ser utilizado como referência à tabela derivada em processamentos posteriores. Isto é chamado de aliás de tabela.

Para criar um aliás de tabela deve ser escrito

FROM referência_tabela AS aliás

ou

FROM referência_tabela aliás

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

Uma aplicação típica de aliases de tabelas é atribuir identificadores curtos para nomes longos de tabelas, 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 de referência da tabela para a 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 implícita à tabela é adicionada à cláusula FROM. Portanto, a consulta é processada como se estivesse 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 provavelmente não é o desejado.

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 de tabela é uma subconsulta (veja a Seção 4.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, de forma diferente do exemplo anterior:

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

Uma outra forma de aliás para tabela também especifica nomes temporários para as colunas da tabela:

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

Se for especificado um número menor de aliases de coluna que o número de colunas da tabela, as demais colunas não serão renomeadas. Esta sintaxe é especialmente útil em auto-junções e subconsultas.

Quando um aliás é aplicado a saída de uma 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.

4.2.1.3. Subconsultas

Subconsultas especificando uma tabela derivada devem estar entre parênteses, e precisam possuir um nome de aliás de tabela (veja a Seção 4.2.1.2.). Por exemplo:

FROM (SELECT * FROM tabela1) AS nome_aliás

Este exemplo é equivalente 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.

4.2.2. A cláusula WHERE

A sintaxe da cláusula WHERE é

WHERE condição_pesquisa

onde a condição_pesquisa é qualquer expressão de valor, conforme definida na Seção 1.2, que retorna um valor do tipo booleano.

Após o processamento da cláusula FROM ter sido realizado, 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 é rejeitada. A condição de pesquisa tipicamente faz referência a pelo menos uma coluna da tabela gerada pela cláusula FROM; isto não é necessário, mas se não for assim a cláusula WHERE não terá utilidade.

Nota: Antes da implementação da sintaxe do JOIN era necessário colocar a condição de junção, de uma junção interna, na cláusula WHERE. Por exemplo, as duas expressões de tabela abaixo 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é

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 a mais portável para outros produtos de banco de dados SQL. Para as junções externas não existe escolha em nenhum caso: devem ser feitas na cláusula FROM. Uma cláusula ON/USING de uma junção externa não é equivalente a uma 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 da cláusula 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 correspondem à condição de pesquisa da cláusula WHERE são eliminadas de fdt. Observe 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. Observe como fdt é referida nas subconsultas. A qualificação de c1 como fdt.c1 somente é necessária se c1 também for o nome de uma coluna na tabela de entrada derivada da subconsulta. A qualificação do nome da coluna torna mais clara a consulta, mesmo quando não é necessária. Isto mostra como o escopo dos nomes das colunas de uma consulta externa se estende nas suas consultas internas.

4.2.3. As cláusulas GROUP BY e HAVING

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

SELECT lista_seleção
    FROM ...
    [WHERE ...]
    GROUP BY referência_coluna_agrupamento [, referência_coluna_agrupamento]...

A cláusula GROUP BY é utilizada para agrupar linhas de uma tabela que compartilham os mesmos valores em todas as colunas listadas. Em que ordem as colunas são listadas não faz diferença. A finalidade é reduzir cada grupo de linhas compartilhando valores comuns a uma única linha agrupada representando 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 rows)

=> SELECT x FROM teste1 GROUP BY x;
 x
---
 a
 b
 c
(3 rows)

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

De modo geral, se uma tabela é 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 rows)

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

Dica: Um agrupamento sem expressão de agregação na verdade computa o conjunto de linhas distintas de uma coluna. Também poderia ser obtido por meio da cláusula DISTINCT (veja a Seção 4.3.3).

Abaixo está mostrado um outro exemplo: sum(vendas) em uma tabela agrupada pelo código do produto fornece o valor total das vendas de cada produto, e não o total das vendas de todos os produtos.

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

Neste exemplo, as colunas id_produto, 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 id_produto, tornando os agrupamentos adicionais teoricamente desnecessários, mas isto ainda não está implementado). A coluna s.unidades não precisa estar na lista do GROUP BY, porque é usada apenas na expressão de agregação (sum()), que representa o grupo de vendas do produto. Para cada produto, uma linha é retornada contendo o total de 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 permitindo 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 há interesse em alguns grupos apenas, a cláusula HAVING pode ser utilizada, da mesma forma que a cláusula WHERE, para remover grupos da tabela agrupada. A sintaxe é:

SELECT lista_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 expressões 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 rows)

=> SELECT x, sum(y) FROM teste1 GROUP BY x HAVING x < 'c';
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

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

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

No exemplo acima, a cláusula WHERE está selecionando linhas por uma coluna que não é agrupada, enquanto a cláusula HAVING restringe a saída para os grupos com um total bruto de vendas acima de 5000. Observe que as expressões de agregação não precisam ser necessariamente as mesmas em todos os lugares.