7.2. Expressões de tabela

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.

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]

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 *.