Participo de algumas listas de discussão sobre PHP e percebi que muitos desenvolvedores têm dúvidas a respeito de comandos SQL, não sabem ou não utilizam de forma correta os outer joins.
Por isso, decidi fazer um post sobre esse assunto explicando como utilizar o left outer join, right outer join, natural join, cross join ou inner join (mais utilizado).
Para que servem os outer joins?
Servem para fazer as junções entre duas ou mais tabelas, cruzando informações, combinando registros, testando desigualdades.
Por que cruzar dados?
Quando pensamos em armazenamento de dados, não podemos sair criando tabelas sem uma avaliação e sem a criação de um modelo ER (Entidade Relacionamento), e uma das vantagens de pensar no modelo é evitar a repetição de dados. E como fazer isso? Distribuindo-os em mais de uma tabela de forma que possamos categorizar as repetições.
Se você olhar o modelo abaixo, existe uma tabela de programadores e uma de empresas, note que as duas estão relacionadas. Guardo na tabela programadores somente um código que identifica a empresa em que o programador trabalha. Fiz isso em virtude da possibilidade de ter vários programadores relacionados a mesma empresa, assim evito ter que escrever várias vezes o nome da empresa (texto ocupa mais espaço que número) e evito que a mesma empresa seja escrita de maneiras diferentes, dificultando assim, por exemplo, uma busca. Porém, agora para recuperar esses dados será necessário utilizar junções.
 |
 |
 |
 |
Cruzando Dados
1 – Produto Cartesiano
Esse não é bem um tipo de junção, é conhecido como conexão cruzada, ou seja, resulta no cruzamento de cada linha de uma tabela com todas as linhas de outra tabela.
SELECT p.nome, l.nome FROM programadores p CROSS JOIN linguagens l;
O resultado desta consulta gera 36 linhas de combinações. Obs.: Esse tipo de junção não é muito utilizado.
Para obter o mesmo resultado podemos utilizar outras duas sintaxes: retirar o CROSS JOIN e inserir uma vírgula (,) ou usar o INNER JOIN sem a condição de comparação (ON). O resultado será o mesmo.
SELECT p.nome, l.nome FROM programadores p, linguagens l;
SELECT p.nome, l.nome FROM programadores p INNER JOIN linguagens l;
35 Comentários
Paulinha,
Seja bem-vinda e parabéns pelo conteúdo, muito interessante.
Abraço
Jackson,
Obrigada. Espero poder contribuir com mais conteúdos.
Abraços
Belo post! Sempre é bom saber mais sobre SQL…
Parabéns.
Oi Klaus!!!
Obrigada. Em breve falarei mais sobre o assunto.
Abraços
Parabéns pelo post. Trabalho com desenvolvimento PHP utilizando o framework Cake. Muitas vezes acabamos não prestando a devida atenção nesses detalhes de banco que interferem diretamente no desempenho da aplicação.
Parabéns!
Oi Pinter…
Obrigada!!!
É verdade, é que uma vez que você passa usar Frameworks acaba na maioria das vezes não precisando gerar consultas na mão.
Abraços
Gostaria de saber como o produto cartesiano pode ajudar a tabela verdade.
Boa tarde Celso,
Eu não sei se entendi muito bem a sua pergunta, pode me explicar melhor sua dúvida?
É que o produto cartesiano simplesmente é uma forma de relacionar todos os dados entre duas tabelas para obter a relação entre elas.
Também recomendo que você de uma lida na continuação desse post em: http://www.profissionaisti.com.br/author/paulinha/
Até mais.
Paulinha
O que você usa pra fazer o modelo das tabelas?
Preciso de uma tabela juntando duas outras, sendo que uma é um cadastro de códigos e outra das ocorrências destes códigos em uma determinada condição. Só que preciso de uma tabela contendo todos os códigos, mesmo os códigos que não existam na segunda tabela, sendo que estes virão com valores zerados. O que me sugerem?
SELECT TABELA1.*
FROM TABELA1
RIGHT JOIN TABELA_CODIGOS
ON TABELA1.ID_CODIGO = TABELA_CODIGOS.ID
— WHERE CONDIÇÃO ESPECIFICADA.
Daniel Tibúrcio, Passa as estrutras das tabelas e oq vc precisa retornar.
Obrigado, Fabrício, acho que estamos no caminho certo.
Adaptei seu código para:
SELECT ACUMUL.*
FROM ACUMUL
RIGHT JOIN RENDESC
ON ACUMUL.COD_CODIGO = RENDESC.COD_CODIGO
WHERE ACUMUL.EMP_CODIGO = ‘006’ AND
ACUMUL.CTE_MESPRO = ‘201105’
Só que não deu certo, pois existem códigos na tabela RENDESC que não foram mostrados no resultado.
Estrutura de ACUMUL:
EMP_CODIGO (empresa), CTE_MESPRO (ano/mês), FUN_MATRIC(matrrícula), COD_CODIGO (código),CAL_VALOR
RENDESC: COD_CODIGO, COD_DESCRI
O que eu preciso é que para cada matrícula fosse mostrado o valor de cada código, inclusive dos códigos que não tem no ACUMUL. Preciso disto porque preciso comparar valores de meses diferentes. A idéia seria criar uma tabela temporária para cada mês e uma outra query para gerar uma com os dois valores e apresentar no relatório.
Se precisar de alguma ajuda, coloco-me à disposição.
Se entendi bem, você precisa trazer todos os registros das duas tabelas, se entendi bem faça o seguinte, troque RIGHT JOIN para FULL JOIN, isso irá trazer os dados das duas tabelas.
Se sua ideia é usar esse resultado para efetuar mais manipulações nesses dados, use SubQuery ou Common Table Expression (se for SQL Server ñ sei se funciona com outros bancos) http://msdn.microsoft.com/en-us/library/ms190766.aspx
Não deu certo, Fabrício, o resultado foi o mesmo.
Vamos a um exemplo: digamos que em RENDESC eu tenha códigos de 001 a 100, mas que nos registros de ACUMUL só tenho 20 códigos gravados. Preciso de uma tabela que mostre o valor de todos os 100 códigos de RENDESC, sendo zero para os 80 inexistentes em ACUMUL.
Obrigado pela atenção
tenta isso aqui.. vamos ver se da certo:
http://pastebin.com/pa6Ty4F9
Não aceitou ISNULL, uso Firebird 2.0
SELECT ISNULL(RENDESC.ID_CODIGO, ‘Nenhuma Ocorrencia’)
COD_DESCRI, sum_
FROM RENDESC
LEFT JOIN ACUMUL
ON RENDESC.COD_CODIGO = ACUMUL.COD_CODIGO
WHERE ACUMUL.CTE_MESPRO = ‘201105’ AND ACUMUL.EMP_CODIGO = ‘005’
Na verdade eu não preciso da descrição, isto eu pego de outro jeito, preciso do código (COD_CODIGO) e a soma total do valor (CAL_VALOR em ACUMUL) de cada código existente em RENDESC (os códigos não se repetem)
Deu certo se eu não tiver a cláusula where, os 125 registros da tabela RENDESC foram mostrados com valor zero para códigos que não tinham correspondente no ACUMUL.
No entanto, se eu coloco a cláusula WHERE, como mostrado abaixo, só mostra 24 registros, apenas os que haveriam se verificar os códigos distintos em ACUMUL naquela condição.
SELECT RENDESC.COD_CODIGO, SUM(ACUMUL.CAL_VALOR) AS CAL_VALOR
FROM RENDESC
LEFT JOIN ACUMUL
ON RENDESC.COD_CODIGO = ACUMUL.COD_CODIGO
WHERE ACUMUL.EMP_CODIGO = ‘027’ AND ACUMUL.CTE_MESPRO = ‘201107’
GROUP BY RENDESC.COD_CODIGO
tira o LEFT e coloca INNER, talvez da certo..
ou coloca o WHERE antes do GROUP BY
INNER = mesma coisa
RIGHT = mesma coisa
WHERE antes dá erro de SQL
Complicado mesmo
vamos desistir?
Desistimos mesmo? Não deu para entender ter funcionado sem a cláusula WHERE (mostrou todos os códigos como eu queria) e não funcionar com ela (mostrou só os códigos existentes no arquivo, não todos)
Deu certo, bastou trocar o WHERE por AND, assim:
SELECT RENDESC.COD_CODIGO, SUM(ACUMUL.CAL_VALOR) AS CAL_VALOR
FROM RENDESC
LEFT JOIN ACUMUL
ON RENDESC.COD_CODIGO = ACUMUL.COD_CODIGO
AND ACUMUL.EMP_CODIGO = ’027? AND ACUMUL.CTE_MESPRO = ’201107?
GROUP BY RENDESC.COD_CODIGO
Deu certo com os totais gerais, mas eu precisava fazer o mesmo por matrícula, algo assim:
SELECT RENDESC.COD_CODIGO, ACUMUL.FUN_MATRIC, SUM(ACUMUL.CAL_VALOR) AS CAL_VALOR
FROM RENDESC
LEFT JOIN ACUMUL
ON RENDESC.COD_CODIGO = ACUMUL.COD_CODIGO
AND ACUMUL.EMP_CODIGO = ’027′ AND ACUMUL.CTE_MESPRO = ’201107′
GROUP BY RENDESC.COD_CODIGO, ACUMUL.FUN_MATRIC
mas assim volta ao problema anterior
Daniel, me manda os scripts de criação de suas tabelas e oq vc precisa retornar..
Eu monto aqui e te passou, fica meio abstrato tentar ajudar só imaginando as tabelas.
Me passa elas e os resultados que você precisa retornar delas, ai te mando.
Se preferir mandar no email é : [email protected].
Valeu!
CREATE TABLE RENDESC
(
COD_CODIGO CHAR(4) ,
COD_DESCRI VARCHAR(30)
);
CREATE TABLE EMPREGAD
(
CLI_CODIGO SMALLINT,
EMP_CODIGO CHAR(3) ,
FUN_MATRIC VARCHAR(6) ,
FUN_NOME VARCHAR(60)
);
CREATE TABLE ACUMUL
(
EMP_CODIGO CHAR(3) ,
FUN_MATRIC VARCHAR(6) ,
CTE_MESPRO VARCHAR(6) ,
COD_CODIGO CHAR(4) ,
CAL_VALOR FLOAT
);
Preciso, para uma empresa definida (campo EMP_CODIGO de EMPREGAD e de ACUMUL), de todos os códigos existentes (COD_CODIGO em RENDESC e ACUMUL, sendo que neste não existem todos existentes em RENDESC) para todos os funcionários (FUN_MATRIC em EMPREGAD e ACUMUL) em um mês definido (CTE_MESPRO em ACUMUL).
Preciso da matrícula (FUN_MATRIC), código (COD_CODIGO) e a soma dos valores (CAL_VALOR em ACUMUL, sendo que os códigos que não existem aqui deve vir zerados).
Como vou fazer comparação entre dois meses, a intenção é executar o mesmo SQL para cada mês informado pelo usuário e compará-los, mas esta parte é tranquila.
Se não apresentarem todos os códigos a comparação ficará errada, pois um código pode acontecer num mês e não acontecer em outro e o contrário pode ocorrer com ourtro código.
Um grande abraço e excelente 2012!!!
Conseguiu algo, Fabrício?
Acho que o seu select deveria ser assim:
SELECT RENDESC.COD_CODIGO, ACUMUL.FUN_MATRIC, SUM(ACUMUL.CAL_VALOR) AS CAL_VALOR
FROM RENDESC
LEFT JOIN ACUMUL ON
(RENDESC.COD_CODIGO = ACUMUL.COD_CODIGO)
where ACUMUL.EMP_CODIGO = ’027?
AND ACUMUL.CTE_MESPRO = ’201107?
GROUP BY RENDESC.COD_CODIGO, ACUMUL.FUN_MATRIC
Bem superficial o seu post. Qualquer um escreve isso. Se quer realmente ser útil seja mais rica em detalhes e exemplos. A internet precisa de bom conteúdo e não artigos for dummies. abs!
Bom dia!
Atualmente estou usando SQL Server 2012 e tenho uma tabela de pessoa e uma tabela de endereços que possui a chave pessoa, possibilitando uma pessoa ter vários endereços, preciso fazer um insert para criar um endereço padrão para as pessoas que estão sem endereço, primeiramente preciso fazer um select, para pegar as pessoas que não tem endereço, então criar esse endereço para cada uma dessas pessoas. alguém pode me ajudar a fazer isso da melhor forma possível?