PostgreSQL Prático/DML/Consultas Join
4.2 - Junções de Tabelas ou Consultas[editar | editar código-fonte]
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;