PostgreSQL Prático/DML/Consultas Join

4.2 - Junções de Tabelas ou Consultas

As junções SQL são utilizadas quando precisamos selecionar dados de duas ou mais tabelas.

Existem as junções com estilo non-ANSI ou theta (junção com WHERE).

E as junções ANSI join (com JOIN). As junções ANSI podem ser de dois tipos, as INNER JOINS e as OUTER JOINS. A padrão é a INNER JOIN. INNER JOIN pode ser escrito com apenas JOIN.

Exemplo ANSI:

SELECT p.siape, p.senha, l.lotacao FROM pessoal p CROSS JOIN lotacoes l;

Tipos de Junções

INNER JOIN - Onde todos os registros que satisfazem à condição serão retornados.

Exemplo:

SELECT p.siape, p.nome, l.lotacao
FROM pessoal p INNER JOIN lotacoes l
ON p.siape = l.siape ORDER BY p.siape;

Exemplo no estilo theta:

SELECT p.siape, p.nome, l.lotacao
FROM pessoal p, lotacoes l
WHERE p.siape = l.siape ORDER BY p.siape;

OUTER JOIN que se divide em LEFT OUTER JOIN e RIGHT OUTER JOIN

LEFT OUTER JOIN ou simplesmente LEFT JOIN - Somente os registros da tabela da esquerda (left) serão retornados, tendo ou não registros relacionados na tabela da direita.

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.

A tabela à esquerda do operador de junção exibirá cada um dos seus registros, enquanto que a da direita exibirá somente seus registros que tenham correspondentes aos da tabela da esquerda.

Para os registros da direita que não tenham correspondentes na esquerda serão colocados valores NULL.

Exemplo (voltar todos somente de pessoal):

SELECT p.siape, p.nome, l.lotacao
FROM pessoal p LEFT JOIN lotacoes l
ON p.siape = l.siape ORDER BY p.siape;

Veja que pessoal fica à esquerda em “FROM pessoal p LEFT JOIN lotacoes l”.

RIGHT OUTER JOIN

Inverso do LEFT, este retorna todos os registros somente da tabela da direita (right). 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.

Exemplo (retornar somente os registros de lotacoes):

SELECT p.siape, p.nome, l.lotacao
FROM pessoal p RIGHT JOIN lotacoes l
ON p.siape = l.siape ORDER BY p.nome;

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.

E também as:

CROSS JOIN e SELF JOIN (para si mesmo).

Vide item 7.2.1.1 do manualoficial para mais detalhes e exemplos.

LIMIT

LIMIT (limite) juntamente com OFFSET (deslocamento) permite dizer quantas linhas desejamos retornar da consulta. Podemos retornar desde apenas uma até todas.

Sintaxe:

SELECT lista_de_campos
   FROM expressão 
   [LIMIT { número | ALL }] [OFFSET inicio]

LIMIT ALL – mesmo que imitir LIMIT.

OFFSET inicio – orienta para que a consulta retorne somente a partir de inicio.

OFFSET 0 – mesmo que omitir OFFSET.

LIMIT 50 OFFSET 11 – Deverá trazer 50 registros do 11 até o 60, caso existam.

Obs.: Quando se utiliza LIMIT é importante utilizar a cláusula ORDER BY para estabelecer uma ordem única para as linhas do resultado. Caso contrário, será retornado um subconjunto imprevisível de linhas da consulta; pode-se desejar obter da décima a vigésima linha, mas da décima a vigésima de qual ordem? A ordem é desconhecida a não ser que seja especificado ORDER BY. Isto é uma conseqüência inerente ao fato do SQL não prometer retornar os resultados de uma consulta em qualquer ordem específica, a não ser que ORDER BY seja utilizado para impor esta ordem.

Exemplos:

SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET 1; 

Irá retornar os registros do 1 até o 20.

SELECT * FROM news_m LIMIT $inicio, $n_resultados

O comando "SELECT * FROM news_m LIMIT $n_resultados OFFSET $inicio"

irá pesquisar as noticias da tabela "news_m" começando do resultado "$inicio" e irá listar "$n_resultados".

Exemplo: "SELECT * FROM news_m LIMIT 3 OFFSET 2" irá exibir 3 notícias a partir da 2a. notícia da tabela, ou seja, irá exibir as notícias 2, 3 e 4 da nossa tabela "news_m".

INNER JOIN

Definição e exemplos no site db.apache.org e muitas outras boas informações sobre SQL:

http://db.apache.org/derby/docs/dev/pt_BR/ref/rrefsqlj35034.html

INNER JOIN (junção interna) é uma Operação JOIN que permite especificar uma cláusula de junção explícita.

Sintaxe

ExpressãoTabela [ INNER ] JOIN ExpressãoTabela { ON ExpressãoBooleana }

A cláusula de junção pode ser especificada utilizando ON com uma expressão booleana. O escopo das expressões na cláusula ON inclui as tabelas correntes, e as tabelas nos blocos de consulta externos ao SELECT corrente. No exemplo a seguir, a cláusula ON faz referência às tabelas correntes:

SELECT *
FROM SAMP.EMPREGADOS INNER JOIN SAMP.EQUIPES
ON EMPREGADOS.SAL�?RIO < EQUIPES.SAL�?RIO;

A cláusula ON pode fazer referência a tabelas que não estão sendo juntadas, e não é obrigada a fazer referência a nenhuma das tabelas sendo juntadas (embora tipicamente o faça).

-- Junção das tabelas ATIV_EMP e EMPREGADOS
-- selecionar todas as colunas da tabela ATIV_EMP e
-- adicionar o sobrenome do empregado (ÚLTIMO_NOME) da tabela
-- EMPREGADOS a todas as linhas do resultado

SELECT SAMP.ATIV_EMP.*, ÚLTIMO_NOME
     FROM SAMP.ATIV_EMP JOIN SAMP.EMPREGADO
     ON ATIV_EMP.NUM_EMP = EMPREGADOS.NUM_EMP;

-- Juntar as tabelas EMPREGADOS e DEPARTAMENTOS,
-- selecionar o número do empregado (NUM_EMP),
-- o sobrenome do empregado (ÚLTIMO_NOME),
-- o número do departamento (DEP_TRAB na tabela EMPREGADOS e
-- NUM_DEP na tabela DEPARTAMENTOS)
-- e o nome do departamento (NOME_DEP)
-- de todos os empregados nascidos (DATA_NASC) antes de 1930.

SELECT NUM_EMP, ÚLTIMO_NOME, DEP_TRAB, NOME_DEP
     FROM SAMP.EMPREGADOS JOIN SAMP.DEPARTAMENTOS
     ON DEP_TRAB = NUM_DEP
     AND YEAR(DATA_NASC) < 1930;

-- Outro exemplo de "gerar" novos valores de dado,
-- utilizando uma consulta que seleciona da cláusula VALUES
-- (que é uma forma alternativa de FULLSELECT).

SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS TABELA1_VALORES(C1, C2)
JOIN (VALUES (3, 2), (1, 2),(0, 3))
AS TABELA2_VALORES(C1, C2)
ON TABELA1_VALORES.C1 = TABELA2_VALORES.C1;

O que resulta em:

C1         |C2         |C1         |2
-----------------------------------------------
3          |4          |3          |2
1          |5          |1          |2

-- Listar todos os departamentos, juntamente com o
-- número do empregado e o último nome do gerente

SELECT NUM_DEP, NOME_DEP, NUM_EMP, ÚLTIMO_NOME
FROM DEPARTAMENTOS
     INNER JOIN EMPREGADOS
     ON NUM_GER = NUM_EMP;

-- Listar todos os números do empregado e último nome, juntamente
-- com o número do empregado e último nome de seus gerentes
SELECT E.NUM_EMP, E.ÚLTIMO_NOME, M.NUM_EMP, M.ÚLTIMO_NOME
        FROM EMPREGADOS E INNER JOIN
        DEPARTAMENTOS INNER JOIN EMPREGADOS M
        ON NUM_GER = M.NUM_EMP
        ON E.DEP_TRAB = NUM_DEP;

Operação JOIN

As operações de junção (JOIN), que estão entre as ExpressõesTabela possíveis na CláusulaFROM, realizam junções entre duas tabelas (Também pode ser realizada a junção entre duas tabelas utilizando um teste de igualdade explícito na cláusula WHERE, como "WHERE t1.col1 = t2.col2".) Sintaxe

Operação de junção

As operações de junção são:

• INNER JOIN: Especifica a junção entre duas tabelas com uma cláusula de junção explícita. Consulte INNER JOIN.
• LEFT OUTER JOIN: Especifica a junção entre duas tabelas com uma cláusula de junção explícita, preservando as linhas sem correspondência da primeira tabela. Consulte LEFT OUTER JOIN.
• RIGHT OUTER JOIN: Especifica a junção entre duas tabelas com uma cláusula de junção explícita, preservando as linhas sem correspondência da segunda tabela. Consulte RIGHT OUTER JOIN.

Em todos os casos podem ser especificadas restrições adicionais para uma ou mais tabelas sendo juntadas nas cláusulas de junção externa, ou na Cláusula WHERE

LEFT OUTER JOIN

LEFT OUTER JOIN é uma Operação JOIN que permite especificar a cláusula de junção. Preserva as linhas sem correspondência da primeira tabela (esquerda), juntando-as com uma linha nula na forma da segunda tabela (direita).

Sintaxe

ExpressãoTabela LEFT [ OUTER ] JOIN ExpressãoTabela
{
    ON ExpressãoBooleana
    }

O escopo das expressões na cláusula ON inclui as tabelas correntes, e as tabelas nos blocos de consulta externos ao SELECT corrente. A cláusula ON pode fazer referência a tabelas que não estão sendo juntadas, e não é obrigada a fazer referência a nenhuma das tabelas sendo juntadas (embora tipicamente o faça).

--correspondência entre cidades e países
SELECT CIDADES.PA�?S, REGIÃO
FROM PA�?SES
     LEFT OUTER JOIN CIDADES
     ON ID_CIDADE=ID_CIDADE
WHERE REGIÃO = '�?sia';

-- uso da sintaxe sinônimo, LEFT JOIN, para obter exatamente
-- os mesmos resultados da exemplo acima

SELECT CIDADES.PA�?S, REGIÃO
FROM PA�?SES
     LEFT JOIN CIDADES
     ON ID_CIDADE=ID_CIDADE
WHERE REGIÃO = '�?sia';

-- Junção das tabelas EMPREGADOS e DEPARTAMENTOS,
-- selecionar o número do empregado (NUM_EMP),
-- o sobrenome do empregado (ÚLTIMO_NOME),
-- o número do departamento (DEP_TRAB na tabela EMPREGADOS e
-- NUM_DEP na tabela DEPARTAMENTOS)
-- e o nome do departamento (NOME_DEP)
-- de todos os empregados nascidos (DATA_NASC) antes de 1930

SELECT NUM_EMP, ÚLTIMO_NOME, DEP_TRAB, NOME_DEP
FROM SAMP.EMPREGADOS
     LEFT OUTER JOIN SAMP.DEPARTAMENTOS
     ON DEP_TRAB = NUM_DEP
     AND YEAR(DATA_NASC) < 1930;

-- Listar todos os departamentos, juntamente com o
-- número do empregado e o último nome do gerente,
-- incluindo os departamentos sem gerente

SELECT NUM_DEP, NOME_DEP, NUM_EMP, ÚLTIMO_NOME
FROM DEPARTAMENTOS
     LEFT OUTER JOIN EMPREGADOS
     ON NUM_GER = NUM_EMP;

RIGHT OUTER JOIN

RIGHT OUTER JOIN é uma Operação JOIN que permite especificar a cláusula de junção. Preserva as linhas sem correspondência da segunda tabela (direita), juntando-as com uma linha nula na forma da primeira tabela (esquerda). (A LEFT OUTER JOIN B) é equivalente a (B RIGHT OUTER JOIN A), com as colunas em uma ordem diferente.

Sintaxe

ExpressãoTabela RIGHT [ OUTER ] JOIN ExpressãoTabela
{
    ON ExpressãoBooleana
    }

O escopo das expressões na cláusula ON inclui as tabelas correntes, e as tabelas nos blocos de consulta externos ao SELECT corrente. A cláusula ON pode fazer referência a tabelas que não estão sendo juntadas, e não é obrigada a fazer referência a nenhuma das tabelas sendo juntadas (embora tipicamente o faça).

-- obter todos os países e cidades correspondentes,
-- incluindo os países sem nenhuma cidade

SELECT NOME_CIDADE, CIDADES.PA�?S
FROM CIDADES RIGHT OUTER JOIN PA�?SES
     ON CIDADES.COD_ISO_PA�?S = PA�?SES.COD_ISO_PA�?S;

-- obter todos países da �?frica e as cidades correspondentes,
-- incluindo os países sem cidades

SELECT NOME_CIDADE, CIDADES.PA�?S
FROM CIDADES RIGHT OUTER JOIN PA�?SES
     ON CIDADES.COD_ISO_PA�?S = PA�?SES.COD_ISO_PA�?S;
WHERE PA�?SES.REGIÃO = '�?frica';

-- uso da sintaxe sinônimo, RIGHT JOIN, para obter exatamente
-- os mesmos resultados do exemplo acima

SELECT NOME_CIDADE, CIDADES.PA�?S
FROM CIDADES RIGHT JOIN PA�?SES
     ON CIDADES.COD_ISO_PA�?S = PA�?SES.COD_ISO_PA�?S
WHERE PA�?SES.REGIÃO = '�?frica';

-- a ExpressãoTabela pode ser uma OperaçãoJunção. Portanto,
-- pode haver várias operações de junção na cláusula FROM
-- Listar todos os números e último nome dos empregados,
-- juntamente com os números e último nome de seus gerentes

SELECT E.NUM_EMP, E.ÚLTIMO_NOME, M.NUM_EMP, M.ÚLTIMO_NOME
FROM EMPREGADOS E RIGHT OUTER JOIN
     DEPARTAMENTOS RIGHT OUTER JOIN EMPREGADOS M
     ON NUM_GER = M.NUM_EMP
     ON E.DEP_TRAB = NUM_DEP;