Uma expressão de tabela calcula uma tabela.
A expressão de tabela contém uma cláusula FROM,
opcionalmente seguida pelas cláusulas WHERE,
GROUP BY e HAVING.
Expressões de tabela triviais apenas se referem a uma tabela
no disco, a chamada tabela base, mas expressões mais complexas podem
ser usadas para modificar ou combinar tabelas-base de várias maneiras.
As cláusulas opcionais WHERE,
GROUP BY e HAVING
na expressão de tabela especificam uma sequência
(pipeline) de transformações
sucessivas realizadas na tabela derivada da cláusula
FROM. Todas estas transformações produzem a
tabela virtual que fornece as linhas passadas para a lista
de seleção para computar as linhas de saída da consulta.
FROM #
A cláusula FROM
deriva uma tabela a partir de uma ou mais outras tabelas fornecidas
em uma lista de referência de tabela separada por vírgula.
FROMreferência_de_tabela[,referência_de_tabela[, ...]]
Uma referência de tabela pode ser um nome de tabela (possivelmente
qualificado pelo esquema) ou uma tabela derivada, como uma
subconsulta, uma construção JOIN, ou combinações
complexas desses. Se mais de uma referência de tabela estiver
listada na cláusula FROM, as tabelas são unidas
(ou seja, o produto cartesiano de suas linhas é formado; veja abaixo).
O resultado da lista do FROM é uma tabela virtual
intermediária, que pode estar sujeita a transformações por
WHERE, GROUP BY e
HAVING, sendo finalmente o resultado da expressão
geral de tabela.
Quando uma referência de tabela designa uma tabela que é mãe de uma
hierarquia de herança de tabela, a referência de tabela produz linhas
não apenas dessa tabela, mas de todas as suas tabelas descendentes,
a menos que a palavra-chave ONLY preceda o nome
da tabela. Entretanto, a referência produz apenas as colunas que
aparecem na tabela designada — quaisquer colunas acrescentadas
às sub-tabelas são ignoradas.
Em vez de escrever ONLY antes do nome da tabela,
pode ser escrito * depois do nome da tabela para
especificar, explicitamente, que as tabelas descendentes devem ser
incluídas. Não há mais motivo real para usar esta sintaxe, porque
procurar nas tabelas descendentes agora é o comportamento padrão.
No entanto, é aceito para manter a compatibilidade com versões
mais antigas.
Uma tabela juntada é uma tabela derivada de duas outras tabelas (reais ou derivadas), segundo as regras específicas do tipo de junção. Estão disponíveis junções internas, externas e cruzadas. A sintaxe geral de uma tabela juntada é:
T1tipo_de_junçãoT2[condição_de_junção]
Junções de todos os tipos podem ser encadeadas ou aninhadas: uma, ou
ambas, T1 e T2
podem ser tabelas juntadas. Podem ser usados 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 aninham da esquerda para a direita.
Tipos de junção
T1CROSS JOINT2
Para toda combinação possível de linhas de
T1 e T2
(ou seja, o produto cartesiano), a tabela juntada conterá uma
linha que consiste em todas as colunas de
T1 seguida por todas as colunas de
T2.
Se as tabelas tiverem N e M linhas respectivamente,
a tabela juntada terá N * M linhas.
FROM equivale a
T1 CROSS JOIN
T2FROM (veja abaixo).
Também equivale a T1 INNER JOIN
T2 ON TRUEFROM .
T1,
T2
Esta última equivalência não se mantém exatamente assim quando estão
presentes mais de duas tabelas, porque JOIN
liga mais firmemente do que a vírgula. Por exemplo
FROM ,
não é a igual a
T1 CROSS JOIN
T2 INNER JOIN T3
ON condiçãoFROM ,
porque a T1,
T2 INNER JOIN
T3 ON
condiçãocondiçãoT1
T1{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2ONexpressão_booleanaT1{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2USING (lista_de_colunas_de_junção)T1NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2
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 em detalhes abaixo.
Os tipos possíveis de junção qualificada são:
INNER JOINPara cada linha R1 de T1, a tabela juntada tem uma linha para cada linha em T2 que satisfaz a condição de junção com R1.
LEFT OUTER JOIN
Primeiro, a junção interna é executada. Então, para cada linha em T1 que não satisfaça a condição de junção com nenhuma linha em T2, uma linha juntada é adicionada com valores nulos nas colunas de T2. Assim, a tabela unida sempre tem pelo menos uma linha para cada linha em T1.
RIGHT OUTER JOIN
Primeiro, a junção interna é executada. Então, para cada linha em T2 que não satisfaça a condição de junção com nenhuma linha em T1, uma linha juntada é adicionada com valores nulos nas colunas de T1. Este é o inverso da junção esquerda: a tabela de resultados sempre terá uma linha para cada linha em T2.
FULL OUTER JOINPrimeiro, a junção interna é executada. Então, para cada linha em T1 que não satisfaça a condição de junção com nenhuma linha em T2, uma linha juntada é adicionada com valores nulos nas colunas de T2. Além disso, para cada linha de T2 que não satisfaça a condição de junção com qualquer linha em T1, uma linha juntada com valores nulos nas colunas de T1 é adicionada.
A cláusula ON é o tipo mais geral de condição
de junção: ela recebe uma expressão de valor booleano do mesmo
tipo usado em uma cláusula WHERE.
Um par de linhas de T1 e
T2 corresponde se a expressão
ON for avaliada como verdadeira.
A cláusula USING é uma forma abreviada
que permite aproveitar a situação específica onde os dois lados
da junção usam o mesmo nome para a(s) coluna(s) da junção.
Ela recebe uma lista separada por vírgulas dos nomes das colunas
compartilhadas e forma uma condição de junção que inclui uma
comparação de igualdade para cada uma delas.
Por exemplo, juntar T1 e
T2 com USING (a, b)
produz a condição de junção
ON .
T1.a =
T2.a AND
T1.b =
T2.b
Além disso, a saída de JOIN USING suprime
colunas redundantes: não há necessidade de exibir as duas colunas
correspondentes, porque elas devem ter valores iguais.
Enquanto JOIN ON produz todas as colunas de
T1 seguidas por todas as colunas de
T2, JOIN USING
produz uma coluna de saída para cada um dos pares de colunas
listados (na ordem listada), seguido por quaisquer colunas
restantes de T1, seguidas por
quaisquer colunas restantes de T2.
Por fim, NATURAL é uma forma abreviada de
USING: forma uma lista USING
consistindo em todos os nomes de coluna que aparecem igualmente nas
duas tabelas de entrada. Assim como em USING,
estas colunas aparecem apenas uma vez na tabela de saída.
Se não houver nomes de coluna em comun,
NATURAL JOIN se comporta como
CROSS JOIN.
USING é razoavelmente seguro com relação a
alterações de coluna nas relações juntadas, porque apenas as
colunas listadas são combinadas. NATURAL
é consideravelmente mais arriscado, porque qualquer alteração
de esquema em qualquer relação que cause a presença de um novo
nome de coluna correspondente fará com que a junção combine
esta nova coluna também.
Para reunir tudo isto, supondo que temos as tabelas t1:
num | name -----+------ 1 | a 2 | b 3 | c
e t2:
num | value -----+------- 1 | xxx 3 | yyy 5 | zzz
então obteremos os seguintes resultados para as várias junções:
=>SELECT * FROM t1 CROSS JOIN t2;num | name | num | value -----+------+-----+------- 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 | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy (2 linhas)=>SELECT * FROM t1 INNER JOIN t2 USING (num);num | name | value -----+------+------- 1 | a | xxx 3 | c | yyy (2 linhas)=>SELECT * FROM t1 NATURAL INNER JOIN t2;num | name | value -----+------+------- 1 | a | xxx 3 | c | yyy (2 linhas)=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy (3 linhas)=>SELECT * FROM t1 LEFT JOIN t2 USING (num);num | name | value -----+------+------- 1 | a | xxx 2 | b | 3 | c | yyy (3 linhas)=>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy | | 5 | zzz (3 linhas)=>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy | | 5 | zzz (4 linhas)
A condição de junção especificada com ON também
pode conter condições que não se relacionam diretamente com a junção.
Isto pode ser útil para algumas consultas, mas precisa ser pensado
com cuidado. Por exemplo:
=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | | (3 linhas)
Repare que colocar a restrição na cláusula
WHERE produz um resultado diferente:
=>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx (1 linha)
Isto acontece, porque uma restrição colocada na cláusula
ON é processada antes
da junção, enquanto uma restrição colocada na cláusula
WHERE é processada depois
da junção. Isto não faz diferença com junções internas,
mas faz muita diferença com junções externas.
Tabelas e referências complexas de tabela podem receber um nome temporário, que é então usado para referenciar a tabela derivada no resto da consulta. Isto é chamado de alias de tabela (pseudônimo).
Para criar um alias de tabela se escreve
FROMreferência_de_tabelaASalias
ou
FROMreferência_de_tabelaalias
A palavra-chave AS é um ruído opcional.
alias pode ser qualquer identificador.
Um uso típico de aliases de tabela é para atribuir identificadores curtos a nomes de tabela longos para manter as cláusulas de junção legíveis. Por exemplo:
SELECT * FROM um_nome_de_tabela_muito_longo u
JOIN outro_nome_de_tabela_muito_longo o ON u.id = o.num;
O alias se torna o novo nome de referência da tabela no que diz respeito à consulta corrente — não é mais permitido se referir à tabela pelo nome original em outro lugar da consulta. Portanto, isto não é válido:
SELECT * FROM minha_tabela AS m WHERE minha_tabela.a > 5; -- errado
Os aliases de tabela são usados principalmente para conveniência notacional, sendo necessário usá-los para juntar uma tabela com ela mesma (autorreferência), por exemplo:
SELECT * FROM pessoas AS pais JOIN pessoas AS filhos ON pais.id = filhos.pais_id;
São usados parênteses para resolver ambiguidades. No exemplo a
seguir, a primeira declaração atribui o alias b
para a segunda instância de minha_tabela, mas a
segunda declaração atribui o alias ao resultado da junção:
SELECT * FROM minha_tabela AS a CROSS JOIN minha_tabela AS b ... SELECT * FROM (minha_tabela AS a CROSS JOIN minha_tabela) AS b ...
Uma outra forma de alias de tabela fornece nomes temporários às colunas da tabela, assim como à própria tabela:
FROMreferência_de_tabela[AS]alias(coluna1[,coluna2[, ...]] )
Se forem especificados menos aliases de coluna do que a tabela real possui colunas, as colunas restantes não são renomeadas. Esta sintaxe é notadamente útil para auto-junções ou subconsultas.
Quando um alias é aplicado à saída da cláusula
JOIN, o alias esconde os nomes originais dentro
da cláusula 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 alias de tabela a não é visível
fora do alias c.
Subconsultas que especificam uma tabela derivada devem ser colocadas entre parênteses. Podem ser atribuídos a elas um nome de alias de tabela e, opcionalmente, nomes de alias de coluna. (como na Seção 7.2.1.2). Por exemplo:
FROM (SELECT * FROM tabela1) AS nome_alias
Este exemplo equivale a
FROM tabela1 AS nome_alias.
Casos mais interessantes, que não podem ser reduzidos a uma união
direta, surgem quando a subconsulta envolve agrupamento ou agregação.
Uma subconsulta também pode ser uma lista VALUES:
FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
AS nomes(nome, sobrenome)
Novamente, o alias da tabela é opcional. Atribuir nomes de alias
às colunas da lista VALUES é opcional, mas é uma
boa prática. Para obter mais informações
veja a Seção 7.7.
De acordo com o padrão SQL, deve ser fornecido
um nome de alias de tabela para uma subconsulta.
O PostgreSQL permite ser colocado
AS e o alias a ser omitido, mas escrever um
alias é uma boa prática em código SQL que pode s
er portado para outro sistema.
As funções de tabela são funções que produzem um conjunto de linhas,
compostas por tipos de dados base (tipos escalares), ou tipos de
dado compostos (linhas de tabela). São usadas como tabela, visão
ou subconsulta na cláusula FROM da consulta.
As colunas retornadas por funções de tabela podem ser incluídas nas
cláusulas SELECT, JOIN, ou
WHERE, da mesma maneira que as colunas de uma
tabela, visão, ou subconsulta.
As funções de tabela também podem ser combinadas usando a sintaxe
ROWS FROM, com os resultados retornados em
colunas paralelas; neste caso, o número de linhas do resultado
é o do maior resultado da função, com os resultados menores
completados com valores nulos para ficarem correspondendo.
chamada_de_função↵ [WITH ORDINALITY] [[AS]alias_de_tabela[(alias_de_coluna[, ... ])]] ROWS FROM(chamada_de_função[, ... ] ) ↵ [WITH ORDINALITY] [[AS]alias_de_tabela[(alias_de_coluna[, ... ])]]
Se for especificada a cláusula WITH ORDINALITY,
uma coluna adicional do tipo bigint será adicionada às
colunas de resultado da função. Esta coluna numera as linhas do
conjunto de resultado da função a partir de 1.
(Esta é uma generalização da sintaxe padrão do SQL
para UNNEST ... WITH ORDINALITY.) Por padrão,
a coluna ordinal é chamada de ordinality,
mas um nome diferente de coluna pode ser atribuído a ela usando a
cláusula AS.
A função de tabela especial UNNEST pode ser
chamada com qualquer número de parâmetros de matriz
(array), e retorna um número correspondente de
colunas, como se UNNEST
(veja a Seção 9.19) tivesse sido chamada em
cada parâmetro separadamente, e combinada usando a construção
ROWS FROM.
UNNEST(expressão_de_matriz[, ... ] ) ↵ [WITH ORDINALITY] [[AS]alias_de_tabela[(alias_de_coluna[, ... ])]]
Se o alias_de_tabela não for especificado,
o nome da função é usado como nome da tabela; no caso da construção
ROWS FROM(), é usado o nome da primeira função.
Se não forem fornecidos aliases de colunas para uma função que retorna um tipo de dados base, então o nome da coluna também é o mesmo nome da função. Para uma função que retorna um tipo composto, as colunas de resultado recebem os nomes dos atributos individuais do tipo.
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 podem retornar
diferentes conjuntos de colunas, dependendo de como elas são chamadas.
Para permitir isto, a função da tabela pode ser declarada como
retornando o pseudo-tipo record sem parâmetros
OUT. Quando esta função é usada em
uma consulta, a estrutura da linha esperada deve ser especificada
na própria consulta, para que o sistema possa saber como analisar
e planejar a consulta. Esta sintaxe parece:
chamada_de_função[AS]alias(definição_de_coluna[, ... ])chamada_de_funçãoAS [alias] (definição_de_coluna[, ... ]) ROWS FROM( ...chamada_de_funçãoAS (definição_de_coluna[, ... ]) [, ... ] )
Quando não é usada a sintaxe ROWS FROM(),
a lista de definição_de_coluna substitui
a lista de aliases de coluna que poderia ser anexada à cláusula
FROM;
os nomes nas definições de coluna servem como aliases de coluna.
Ao se usar a sintaxe ROWS FROM(), a lista de
definição_de_coluna pode ser anexada a
cada função membro em separado;
ou, se houver apenas uma função membro e nenhuma cláusula
WITH ORDINALITY, a lista de
definição_de_coluna pode ser escrita no
lugar da lista de aliases de coluna seguindo
ROWS FROM().
Considere o seguinte exemplo:
SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
A função dblink (parte do módulo
dblink) executa uma consulta remota.
É declarada retornando record, já que pode ser usada
para qualquer tipo de consulta. O conjunto real de colunas deve ser
especificado na consulta de chamada para que o analisador saiba,
por exemplo, como expandir o *.
Este exemplo usa ROWS FROM:
SELECT *
FROM ROWS FROM
(
json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
AS (a INTEGER, b TEXT),
generate_series(1, 3)
) AS x (p, q, s)
ORDER BY p;
p | q | s
-----+-----+---
40 | foo | 1
100 | bar | 2
| | 3
(3 linhas)
São unidas duas funções em um único FROM alvo.
json_to_recordset() é instruído a retornar
duas colunas, a primeira do tipo integer e a segunda
do tipo text. O resultado de
generate_series() é usado diretamente.
A cláusula ORDER BY ordena os valores das
colunas como números inteiros.
LATERAL #
As subconsultas que aparecem na cláusula FROM
podem ser precedidas pela palavra-chave LATERAL.
Isto lhes permite fazer referência a colunas fornecidas por itens
anteriores na cláusula FROM.
(Sem LATERAL, cada subconsulta é avaliada
independentemente, portanto não pode fazer referência cruzada
a qualquer outro item da cláusula FROM).
As funções de tabela que aparecem na cláusula FROM
também podem ser precedidas pela palavra-chave LATERAL,
mas para as funções a palavra-chave é opcional; os argumentos da
função podem conter referências a colunas fornecidas pelos itens
anteriores da cláusula FROM em qualquer caso.
O elemento LATERAL pode aparecer no nível
superior na lista da cláusula FROM, ou dentro
de uma árvore JOIN.
Neste último caso, também pode se referir a qualquer elemento que
esteja à esquerda do JOIN enquanto
posicionado à sua direita.
Quando um elemento da cláusula FROM contém
referências cruzadas LATERAL, a avaliação
procede da seguinte maneira: para cada linha de um elemento do
FROM fornecendo a(s) coluna(s) de
referência cruzada(s), ou para o conjunto de linhas de vários elementos
do FROM fornecendo as colunas, o elemento
LATERAL é avaliado usando os valores das colunas
dessa linha ou do conjunto de linhas. A(s) linha(s) resultante(s)
é(são) unida(s) como de costume com as linhas a partir das quais
foram computadas. Isto é repetido para cada linha ou conjunto de
linhas da(s) tabela(s) de origem da(s) coluna(s).
Um exemplo trivial de LATERAL é:
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
Isto não é particularmente útil, porque produz exatamente o mesmo resultado da forma mais convencional:
SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
LATERAL é útil, principalmente, quando a coluna
de referência cruzada é necessária para calcular a(s) linha(s) a
ser(em) juntadas(s). Um uso comum é fornecer o valor do argumento
para uma função que retorna um conjunto de linhas.
Por exemplo, supondo que a função vertices(polygon)
retorna o conjunto de vértices de um polígono, poderíamos identificar
os vértices próximos de um conjunto de polígonos armazenados em uma
tabela com:
SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
LATERAL vertices(p1.poly) v1,
LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
Esta consulta também pode ser escrita como
SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
ou em várias outras formulações equivalentes. (Como já foi
mencionado anteriormente, a palavra-chave LATERAL
é desnecessária neste exemplo, mas foi usada para maior clareza.)
Muitas vezes é útil usar LEFT JOIN em uma
subconsulta LATERAL, para que as linhas de
origem apareçam no resultado, mesmo que a subconsulta
LATERAL não produza nenhuma linha para elas.
Por exemplo, se get_product_names() retorna os
nomes dos produtos fabricados por um fabricante, mas alguns
fabricantes na tabela não fabricam nenhum produto atualmente,
podemos encontrá-los com esta consulta:
SELECT m.name FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true WHERE pname IS NULL;
WHERE #
A sintaxe da cláusula
WHERE é
WHERE condição_de_procura
onde condição_de_procura é qualquer
valor de expressão (veja a Seção 4.2)
que retorna um valor do tipo boolean.
Após o processamento da cláusula FROM, cada linha
da tabela virtual derivada é verificada em relação à condição de
procura. Se o resultado da condição for verdadeiro, a linha é
mantida na tabela de saída, caso contrário (ou seja, se o resultado
for falso ou nulo) ela é descartada. A condição de procura
normalmente se refere a pelo menos uma coluna da tabela gerada na
cláusula FROM; isto não é necessário, mas caso
contrário a cláusula WHERE será bem inútil.
A condição de junção de uma junção interna pode ser escrita na
cláusula WHERE ou na cláusula
JOIN.
Por exemplo, estas 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 mesmo
FROM a NATURAL JOIN b WHERE b.val > 5
Qual será usado é principalmente uma questão de estilo.
A sintaxe da cláusula JOIN na cláusula
FROM provavelmente não é tão portável para outros
sistemas de gerenciamento de banco de dados SQL,
mesmo que esteja no padrão SQL.
Para junções externas não há escolha: elas devem ser feitas na
cláusula FROM.
A cláusula ON ou USING de uma
junção externa não equivale a uma condição
WHERE, porque resulta na adição de linhas
(para linhas de entrada sem correspondência), assim como na remoção
de linhas no resultado final.
Aqui estão alguns exemplos de 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)
fdt é a tabela derivada na cláusula
FROM. As linhas que não atendem à condição de
procura da cláusula WHERE são eliminadas de
fdt. Repare no uso de subconsultas escalares como
expressões de valor. Assim como qualquer outra consulta, as
subconsultas podem empregar expressões complexas de tabela.
Repare, também, como fdt é referenciado nas
subconsultas. Qualificar c1 como
fdt.c1 só é necessário se c1
também for o nome de uma coluna na tabela de entrada derivada da
subconsulta. Mas qualificar o nome da coluna acrescenta clareza,
mesmo quando não é necessário. Este exemplo mostra como o escopo
do nome da coluna de uma consulta externa se estende a suas
consultas internas.
GROUP BY e HAVING #
Após passar pelo filtro WHERE, a tabela de entrada
derivada pode estar sujeita a agrupamento, usando a cláusula
GROUP BY, e eliminação de linhas de grupo usando
a cláusula HAVING.
SELECTlista_de_seleçãoFROM ... [WHERE ...] GROUP BYreferência_de_coluna_de_agrupamento[,referência_de_coluna_de_agrupamento]...
A cláusula GROUP BY
é usada para agrupar aquelas linhas da tabela que têm os mesmos
valores em todas as colunas listadas.
A ordem pela qual as colunas são listadas não importa.
O efeito é combinar cada conjunto de linhas com valores comuns em
uma linha de grupo representando todas as linhas do grupo.
Isto é feito para eliminar a redundância na saída e/ou computar
agregações que se aplicam a esses grupos. Por exemplo:
=>SELECT * FROM test1;x | y ---+--- a | 3 c | 2 b | 5 a | 1 (4 linhas)=>SELECT x FROM test1 GROUP BY x;x --- a b c (3 linhas)
Na segunda consulta, não poderíamos ter escrito
SELECT * FROM test1 GROUP BY x,
por não haver um valor único para a coluna y
que possa ser associado a todos os outros grupos.
As colunas de agrupamento podem ser referenciadas na lista de
seleção, porque possuem um valor único em cada grupo.
Em geral, se uma tabela for agrupada, as colunas que não estiverem
listadas na cláusula GROUP BY não poderão ser
referenciadas, exceto em expressões de agregação.
Um exemplo com expressão de agregação é:
=>SELECT x, sum(y) FROM test1 GROUP BY x;x | sum ---+----- a | 4 b | 5 c | 2 (3 linhas)
Aqui sum é uma função de agregação que calcula um
único valor para todo o grupo. Mais informações sobre as funções
de agregação disponíveis podem ser encontradas na
Seção 9.21.
O agrupamento sem expressão de agregação calcula de fato o
conjunto de valores distintos em uma coluna. Isto também pode ser
obtido usando a cláusula DISTINCT (veja a
Seção 7.3.3).
Aqui está outro exemplo: é calculado o total de vendas de cada produto (em vez do total de vendas de todos os produtos):
SELECT id_produto, p.nome, (sum(s.unidades) * p.preco) AS vendas
FROM produtos p LEFT JOIN sales s USING (id_produto)
GROUP BY id_produto, p.nome, p.preco;
Nesse exemplo, as colunas id_produto,
p.nome e p.preco
devem estar na cláusula GROUP BY, uma vez que
estão referenciadas na lista de seleção da consulta (mas veja abaixo).
A coluna s.unidades não precisa estar na lista
GROUP BY, uma vez que é utilizada apenas em uma
expressão de agregação (sum(...)), representando
a venda de um produto. Para cada produto, a consulta retorna uma
linha de resumo sobre todas as vendas do produto.
Se a tabela de produtos estiver configurada de modo que, digamos,
id_produto seja a chave primária, então seria
suficiente agrupar por id_produto no exemplo acima,
uma vez que o nome e o preço seriam
funcionalmente dependentes da identificação
do produto, e assim não haveria ambiguidade sobre qual nome e valor
de preço retornar para cada grupo de identificação do produto.
No SQL estrito, GROUP BY só
pode agrupar por colunas da tabela de origem, mas o
PostgreSQL estende esta possibilidade
permitindo que GROUP BY também agrupe
colunas da lista de seleção. O agrupamento por expressões de valor,
em vez de apenas nomes de colunas, também é permitido.
Se uma tabela foi agrupada usando GROUP BY, mas
somente certos grupos são de interesse, pode ser usada a cláusula
HAVING, muito parecida com uma cláusula
WHERE, para eliminar grupos do resultado.
A sintaxe é:
SELECTlista_de_seleçãoFROM ... [WHERE ...] GROUP BY ... HAVINGexpressão_booleana
As expressões na cláusula HAVING podem se referir
tanto a expressões agrupadas quanto a expressões não agrupadas
(que necessariamente envolvem uma função de agregação).
Exemplo:
=>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;x | sum ---+----- a | 4 b | 5 (2 linhas)=>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';x | sum ---+----- a | 4 b | 5 (2 linhas)
Novamente, um exemplo mais realista:
SELECT id_produto, p.nome, (sum(s.unidades) * (p.preco - p.custo)) AS lucro
FROM produtos p LEFT JOIN vendas v USING (id_produto)
WHERE v.data > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY id_produto, p.nome, p.preco, p.custo
HAVING sum(p.preco * v.unidades) > 5000;
No exemplo acima, a cláusula WHERE está
selecionando as linhas por uma coluna que não está agrupada
(a expressão é verdadeira apenas para as vendas ocorridas durante
as últimas quatro semanas), enquanto a cláusula HAVING
restringe a saída a grupos com vendas brutas totais acima de 5.000.
Repare que as expressões de agregação não precisam
ser necessariamente as mesmas em todas as partes da consulta.
Se uma consulta tiver chamadas para função de agregação, mas não
tiver nenhuma cláusula GROUP BY, o agrupamento
ainda ocorre: o resultado será uma única linha de grupo (ou, talvez,
nenhuma linha, se a única linha for eliminada pela cláusula
HAVING). O mesmo é verdadeiro se tiver uma
cláusula HAVING, mesmo sem nenhuma chamada de
função de agregação ou cláusula GROUP BY.
GROUPING SETS, CUBE, e ROLLUP #
Operações de agrupamento mais complexas do que as descritas acima
são possíveis usando o conceito de
conjuntos de agrupamento.
Os dados selecionados pelas cláusulas FROM e
WHERE são agrupados em separado por cada
conjunto de agrupamento especificado, agregações computadas para
cada grupo, assim como para as cláusulas GROUP BY
simples e, então, os resultados são retornados. Por exemplo:
=>SELECT * FROM itens_vendidos;marca | tamanho | vendas -------+---------+-------- Foo | L | 10 Foo | M | 20 Bar | M | 15 Bar | L | 5 (4 linhas)=>SELECT marca, tamanho, sum(vendas)FROM itens_vendidosGROUP BY GROUPING SETS ((marca), (tamanho), ());marca | tamanho | sum -------+---------+----- | | 50 Foo | | 30 Bar | | 20 | L | 15 | M | 35 (5 linhas)
Cada sub-lista de GROUPING SETS pode especificar
zero ou mais colunas ou expressões, sendo interpretada da mesma forma
como se estivesse diretamente na cláusula GROUP BY.
Um conjunto de agrupamento vazio significa que todas as linhas estão
agregadas a um único grupo (que é a saída, mesmo que nenhuma linha
de entrada esteja presente), como descrito acima para o caso de
funções de agregação sem a cláusula GROUP BY.
As referências às colunas ou expressões de agrupamento são substituídas por valores nulos nas linhas de resultado para conjuntos de agrupamento nos quais estas colunas não aparecem. Para distinguir de qual agrupamento resultou uma determinada linha de saída, veja a Tabela 9.66.
Uma notação abreviada é fornecida para especificar dois tipos comuns de conjunto de agrupamento. Uma cláusula com a forma
ROLLUP (e1,e2,e3, ... )
representa a lista de expressões fornecida e todos os prefixos da lista, incluindo a lista vazia; assim equivale a
GROUPING SETS (
( e1, e2, e3, ... ),
...
( e1, e2 ),
( e1 ),
( )
)
Isto é comumente usado para análise de dados hierárquicos; por exemplo, salário total por departamento, divisão e total de toda a empresa.
Uma cláusula com a forma
CUBE (e1,e2, ... )
representa a lista fornecida e todos os seus subconjuntos possíveis (ou seja, o conjunto de potência). Dessa forma
CUBE ( a, b, c )
equivale a
GROUPING SETS (
( a, b, c ),
( a, b ),
( a, c ),
( a ),
( b, c ),
( b ),
( c ),
( )
)
Os elementos individuais de uma cláusula CUBE ou
ROLLUP podem ser expressões individuais, ou
sub-listas de elementos entre parênteses. Nesse último caso, as
sub-listas são tratadas como unidades individualmente para fins de
geração dos conjuntos de agrupamentos individuais. Por exemplo:
CUBE ( (a, b), (c, d) )
equivale a
GROUPING SETS (
( a, b, c, d ),
( a, b ),
( c, d ),
( )
)
e
ROLLUP ( a, (b, c), d )
equivale a
GROUPING SETS (
( a, b, c, d ),
( a, b, c ),
( a ),
( )
)
As construções CUBE e ROLLUP
podem ser usadas diretamente na cláusula GROUP BY,
ou aninhadas dentro da cláusula GROUPING SETS.
Se uma cláusula GROUPING SETS for aninhada dentro
de outra, o efeito é o mesmo como se todos os elementos da cláusula
interna tivessem sido escritos diretamente na cláusula externa.
Se vários itens de agrupamento forem especificados em uma única
cláusula GROUP BY, então a lista final de
conjuntos de agrupamento é o produto Cartesiano dos itens individuais.
Por exemplo, a lista final de conjuntos de agrupamentos é o produto
cruzado dos itens individuais:
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
equivale a
GROUP BY GROUPING SETS (
(a, b, c, d), (a, b, c, e),
(a, b, d), (a, b, e),
(a, c, d), (a, c, e),
(a, d), (a, e)
)
Ao especificar vários itens de agrupamento juntos, o conjunto final de conjuntos de agrupamento poderá conter repetições. Por exemplo:
GROUP BY ROLLUP (a, b), ROLLUP (a, c)
equivale a
GROUP BY GROUPING SETS (
(a, b, c),
(a, b),
(a, b),
(a, c),
(a),
(a),
(a, c),
(a),
()
)
Se estas repetições forem indesejáveis, poderão ser removidas
usando a cláusula DISTINCT diretamente no
GROUP BY. Portanto:
GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)
equivale a
GROUP BY GROUPING SETS (
(a, b, c),
(a, b),
(a, c),
(a),
()
)
Isto não é idêntico a usar SELECT DISTINCT,
porque as linhas de saída ainda podem conter duplicidades.
Se alguma das colunas não agrupadas contiver NULL,
será indistinguível do NULL usado quando esta
mesma coluna for agrupada.
A construção (a, b) é normalmente reconhecida em
expressões como
construtora de linha.
Dentro da cláusula GROUP BY, isto não se aplica
nos níveis superiores das expressões, e (a, b) é
analisada como uma lista de expressões conforme descrito acima.
Se por algum motivo for necessário um construtor
de linha em uma expressão de agrupamento, deve ser usado
ROW(a, b).
Se a consulta tiver alguma função de janela,
estas funções são avaliadas após qualquer agrupamento, agregação e
filtragem HAVING ser realizada.
Ou seja, se a consulta usar quaisquer agregações,
GROUP BY ou HAVING, então as
linhas vistas pelas funções de janela são as linhas do grupo em vez
das linhas da tabela original de
FROM / WHERE.
Veja a Seção 3.5 (Tutorial), a
Seção 9.22 (Funções de Janela) e a
Seção 4.2.8 (Sintaxe do SQL).
Quando são usadas várias funções de janela, todas as funções de
janela que possuem cláusulas PARTITION BY e
ORDER BY equivalentes em suas definições de
janela têm a garantia de ver a mesma ordem das linhas de entrada,
mesmo que a cláusula ORDER BY não determine
a ordem de forma única.
Entretanto, não há garantias quanto à avaliação de funções que
possuam especificações de PARTITION BY ou
ORDER BY diferentes.
(Nestes casos, geralmente é necessária uma etapa de classificação
entre as passagens de avaliações da função de janela, e não há
garantia de que a classificação preserve a ordem das linhas que
o ORDER BY considera equivalentes.)
No momento, as funções de janela sempre requerem dados pré-ordenados,
e assim a saída da consulta será ordenada segundo uma ou outra
cláusula das funções de janela
PARTITION BY/ORDER BY.
Entretanto, não é recomendável confiar nisto.
Use uma cláusula explícita de nível superior ORDER BY
se quiser ter certeza de que os resultados estão ordenados de uma
determinada maneira.