Baixe o app para aproveitar ainda mais
Prévia do material em texto
MODELO FÍSICO PARA NOSSA AULA PLANO CARTESIANO – INNER JOIN – LEFT OUTER JOIN – RIGHT OUTER JOIN PLANO CARTESIANO COLOCANDO MAIS DE UMA TABELA NA INSTRUÇÃO SELECT SEM DEFINIR QUAL CAMPO ELAS TÊM EM COMUM, O RESULTADO SERÁ A MULTIPLICAÇÃO DAS COLUNAS DE UMA TABELA COM A OUTRA EM NOSSO CASO, 10 LINHA DA TABELA DE ESTADO COM 10 LINHAS DA TABELA DE CIDADE, TOTALIZANDO 100 LINHAS SELECT * FROM ESTADO,CIDADE ESTADOID ESTADO UF CIDADEID CIDADE ESTADOID ---------------------------------------------------------------------------- 1 SÃO PAULO SP 1 SÃO PAULO 1 1 SÃO PAULO SP 2 RIO DE JANEIRO 2 1 SÃO PAULO SP 3 CURITIBA 3 1 SÃO PAULO SP 4 FLORIANÓPOLIS 4 1 SÃO PAULO SP 5 PORTO ALEGRE 5 1 SÃO PAULO SP 6 BELO HORIZONTE 6 1 SÃO PAULO SP 7 SALVADOR 7 1 SÃO PAULO SP 8 FORTALEZA 8 1 SÃO PAULO SP 9 NATAL 9 1 SÃO PAULO SP 10 RECIFE 10 2 RIO DE JANEIRO RJ 1 SÃO PAULO 1 2 RIO DE JANEIRO RJ 2 RIO DE JANEIRO 2 2 RIO DE JANEIRO RJ 3 CURITIBA 3 2 RIO DE JANEIRO RJ 4 FLORIANÓPOLIS 4 2 RIO DE JANEIRO RJ 5 PORTO ALEGRE 5 2 RIO DE JANEIRO RJ 6 BELO HORIZONTE 6 2 RIO DE JANEIRO RJ 7 SALVADOR 7 2 RIO DE JANEIRO RJ 8 FORTALEZA 8 2 RIO DE JANEIRO RJ 9 NATAL 9 2 RIO DE JANEIRO RJ 10 RECIFE 10 E ISSO VAI ACONTCER PARA OS DEMAIS ESTADOS. PARA COLOCARMOS A RELAÇÃO ENTRE AS TABELAS VAMOS UNIR AS TABELAS COM A CHAVE PRIMÁRIA DE UMA (ESTADO.ESTADOID) COM A CHAVE ESTRANGEIRA DA OUTRA (CIDADE.ESTADOID) SELECT * FROM ESTADO,CIDADE WHERE ESTADO.ESTADOID = CIDADE.ESTADOID ESTADOID ESTADO UF CIDADEID CIDADE ESTADOID -------------------------------------------------------------------------------------- 1 SÃO PAULO SP 1 SÃO PAULO 1 2 RIO DE JANEIRO RJ 2 RIO DE JANEIRO 2 3 PARANÁ PR 3 CURITIBA 3 4 SANTA CATARINA SC 4 FLORIANÓPOLIS 4 5 RIO GRANDE DO SUL RG 5 PORTO ALEGRE 5 6 MINAS GERAIS MG 6 BELO HORIZONTE 6 7 BAHIA BA 7 SALVADOR 7 8 CEARÁ CE 8 FORTALEZA 8 9 RIO GRANDE DO NORTE RN 9 NATAL 9 10 PERNAMBUCO PE 10 RECIFE 10 (10 row(s) affected) TEMOS A OPÇÃO DE INNER JOIN PARA ESSA PESQUISA, CONFORME ABAIXO: SELECT * FROM CIDADE CID INNER JOIN ESTADO EST ON CID.ESTADOID=EST.ESTADOID ESTADOID ESTADO UF CIDADEID CIDADE ESTADOID -------------------------------------------------------------------------------------- 1 SÃO PAULO SP 1 SÃO PAULO 1 2 RIO DE JANEIRO RJ 2 RIO DE JANEIRO 2 3 PARANÁ PR 3 CURITIBA 3 4 SANTA CATARINA SC 4 FLORIANÓPOLIS 4 5 RIO GRANDE DO SUL RG 5 PORTO ALEGRE 5 6 MINAS GERAIS MG 6 BELO HORIZONTE 6 7 BAHIA BA 7 SALVADOR 7 8 CEARÁ CE 8 FORTALEZA 8 9 RIO GRANDE DO NORTE RN 9 NATAL 9 10 PERNAMBUCO PE 10 RECIFE 10 (10 row(s) affected) PERCEBA QUE USAMOS UM APELIDO PARA CADA TABELA, OU SEJA, EST PARA ESTADO E CID PARA CIDADE. SE HÁ REGISTRO EM UMA TABELA E NÃO NA OUTRA USAMOS A OPÇÃO LEFT OUTER JOIN ou RIGHT OUTER JOIN, OU SEJA, DA TABELA DA ESQUEDA PARA A DIREITA ou DA TABELA DA DIREITA PARA A ESQUERDA. SELECT * FROM ESTADO EST LEFT OUTER JOIN CIDADE CID ON CID.ESTADOID=EST.ESTADOID ESTADOID ESTADO UF CIDADEID CIDADE ESTADOID -------------------------------------------------------------------------------------- 1 SÃO PAULO SP 1 SÃO PAULO 1 2 RIO DE JANEIRO RJ 2 RIO DE JANEIRO 2 3 PARANÁ PR 3 CURITIBA 3 4 SANTA CATARINA SC 4 FLORIANÓPOLIS 4 5 RIO GRANDE DO SUL RG 5 PORTO ALEGRE 5 6 MINAS GERAIS MG 6 BELO HORIZONTE 6 7 BAHIA BA 7 SALVADOR 7 8 CEARÁ CE 8 FORTALEZA 8 9 RIO GRANDE DO NORTE RN 9 NATAL 9 10 PERNAMBUCO PE 10 RECIFE 10 11 ALAGOAS AL NULL NULL NULL 12 PIAUÍ PI NULL NULL NULL 13 ESPÍRITO SANTO ES NULL NULL NULL 14 AMAZONAS AM NULL NULL NULL 15 PARÁ PA NULL NULL NULL 16 BRASÍLIA DF NULL NULL NULL (16 row(s) affected) PERCEBA QUE SÃO EXIBIDOS OS ESTADOS QUE NÃO TEM CIDADE(S) CADASTRADA(S). SE INVERTEMOS A ORDEM DAS TABELAS PODEMOS USAR O RIGHT OUTER JOIN, CONFORME ABAIXO: SELECT * FROM CIDADE CID RIGHT OUTER JOIN ESTADO EST ON CID.ESTADOID=EST.ESTADOID ESTADOID ESTADO UF CIDADEID CIDADE ESTADOID -------------------------------------------------------------------------------------- 1 SÃO PAULO SP 1 SÃO PAULO 1 2 RIO DE JANEIRO RJ 2 RIO DE JANEIRO 2 3 PARANÁ PR 3 CURITIBA 3 4 SANTA CATARINA SC 4 FLORIANÓPOLIS 4 5 RIO GRANDE DO SUL RG 5 PORTO ALEGRE 5 6 MINAS GERAIS MG 6 BELO HORIZONTE 6 7 BAHIA BA 7 SALVADOR 7 8 CEARÁ CE 8 FORTALEZA 8 9 RIO GRANDE DO NORTE RN 9 NATAL 9 10 PERNAMBUCO PE 10 RECIFE 10 11 ALAGOAS AL NULL NULL NULL 12 PIAUÍ PI NULL NULL NULL 13 ESPÍRITO SANTO ES NULL NULL NULL 14 AMAZONAS AM NULL NULL NULL 15 PARÁ PA NULL NULL NULL 16 BRASÍLIA DF NULL NULL NULL (16 row(s) affected) ASSIM PODEMOS PESQUISA OS DADOS COM CAMPOS NULL, PARA ISSO USAMOS A OPÇÃO WHERE COM A CONDIÇÃO IS NULL ou IS NOT NULL. SELECT * FROM ESTADO EST LEFT OUTER JOIN CIDADE CID ON CID.ESTADOID=EST.ESTADOID WHERE CID.CIDADEID IS NULLESTADOID ESTADO UF CIDADEID CIDADE ESTADOID -------------------------------------------------------------------------------------- 11 ALAGOAS AL NULL NULL NULL 12 PIAUÍ PI NULL NULL NULL 13 ESPÍRITO SANTO ES NULL NULL NULL 14 AMAZONAS AM NULL NULL NULL 15 PARÁ PA NULL NULL NULL 16 BRASÍLIA DF NULL NULL NULL (6 row(s) affected) IDEM PARA O RIGHT OUTER JOIN. OUTROS EXEMPLOS DE INNER JOIN, UNIDO AS TABELAS. PARA USAR ESSA CLÁUSULA SEMPRE TEMOS QUE TER SABER EM QUE TABELAS ESTÃO AS CHAVES PRIMÁRIA E ESTRANGEIRA, NO NOSSO EXEMPLO TEMOS ESTADOID NA TABELA ESTADO, COMO CHAVE PRIMÁRIA, E ESTADOID NA TABELA CIDADE, COMO CHAVE ESTRANGEIRA, CONFORME ABAIXO: SELECT * FROM CIDADE CIDADEID CIDADE ESTADOID ----------- ------------------------------------------------------------ ----------- 1 SÃO PAULO 1 2 RIO DE JANEIRO 2 3 CURITIBA 3 4 FLORIANÓPOLIS 4 5 PORTO ALEGRE 5 6 BELO HORIZONTE 6 7 SALVADOR 7 8 FORTALEZA 8 9 NATAL 9 10 RECIFE 10 (10 row(s) affected) SELECT * FROM ESTADO ESTADOID ESTADO UF ----------- -------------------------------------------------- ---- 1 SÃO PAULO SP 2 RIO DE JANEIRO RJ 3 PARANÁ PR 4 SANTA CATARINA SC 5 RIO GRANDE DO SUL RS 6 MINAS GERAIS MG 7 BAHIA BA 8 CEARÁ CE 9 RIO GRANDE DO NORTE RN 10 PERNAMBUCO PE 11 ALAGOAS AL 12 PIAUÍ PI 13 ESPÍRITO SANTO ES 14 AMAZONAS AM 15 PARÁ PA 16 BRASÍLIA DF (16 row(s) affected) ASSIM PARA UNIRMOS AS TABELAS USAMOS O COMANDO ABAIXO: SELECT * FROM CIDADE CID INNER JOIN ESTADO EST ON EST.ESTADOID = CID.ESTADOID CIDADEID CIDADE ESTADOID ESTADOID ESTADO UF -------- ------------------ ---------- ------------ ------------------ ---- 1 SÃO PAULO 1 1 SÃO PAULO SP 2 RIO DE JANEIRO 2 2 RIO DE JANEIRO RJ 3 CURITIBA 3 3 PARANÁ PR 4 FLORIANÓPOLIS 4 4 SANTA CATARINA SC 5 PORTO ALEGRE 5 5 RIO GRANDE DO SUL RS 6 BELO HORIZONTE 6 6 MINAS GERAIS MG 7 SALVADOR 7 7 BAHIA BA 8 FORTALEZA 8 8 CEARÁ CE 9 NATAL 9 9 RIO GRANDE DO NORTE RN 10 RECIFE 10 10 PERNAMBUCO PE (10 row(s) affected) ESSE PROCESSO FAZEMOS PARA UNIR AS TABELAS NECESSÁRIAS PARA NOSTA CONSULTA, POR EXEMPLO PARA UNIRMOS AS TABELAS DE FUNCIONÁRIO E DEPARTAMENTO, REPETIMOS A MESMA ESTRUTURA, MAS VERFICANDO QUAL DAS TABELAS TÊM MAIS CHAVES ESTRANGEIRAS, NO NOSSO CASO A TABELA FUNCIONÁRIO, CONFORME ABAIXO: SELECT * FROM FUNCIONARIO FUN INNER JOIN DEPARTAMENTO DEP ON FUN.DEPARTAMENTOID = DEP.DEPARTAMENTOID INNER JOIN CIDADE CID ON FUN.CIDADEID = CID.CIDADEID INNER JOIN ESTADO EST ON EST.ESTADOID = CID.ESTADOID PERCEBA QUE SEMPRE TEMOS A UNIÃO DAS TABELAS COM AS CHAVES PRIMÁRIAS E ESTRANGEIRAS APÓS A MONTAGEM DISTO AINDA PODEMOS COLOCAR OUTRAS CLÁUSULAS QUE FOREM NECESSÁRIAS À NOSSA PESQUISA, COMO WHERE E ORDER BY. SELECT * FROM FUNCIONARIO FUN INNER JOIN DEPARTAMENTO DEP ON FUN.DEPARTAMENTOID = DEP.DEPARTAMENTOID INNER JOIN CIDADE CID ON FUN.CIDADEID = CID.CIDADEID INNER JOIN ESTADO EST ON EST.ESTADOID = CID.ESTADOID WHERE FUN.SALARIO > 2000 ORDER BY FUN.FUNCIONARIO ÁLGEBRA RELACIONAL: CONCEITOS DE UNIÃO, INTERSECÇÃO E EXCLUSÃO (MENOS), ASSIM TEMOS COMO ABAIXO EXEMPLOS: UNIÃO DE DADOS DAS TABLEAS SELECT TELEFONEID FROM TELEFONE UNION ALL SELECT TELEFONEID FROM TELEFONE_FUNCIONARIO ORDER BY TELEFONEID INTERSECÇÃO DE DADOS ENTRE AS TABELAS SELECT TELEFONEID FROM TELEFONE INTERSECT SELECT TELEFONEID FROM TELEFONE_FUNCIONARIO ORDER BY TELEFONEID EXCLUSÃO DE DADOS ENTRE AS TABELAS SELECT ESTADOID FROM ESTADO EXCEPT SELECT ESTADOID FROM CIDADE ORDER BY TELEFONEID TEXTO DE APOIO: O INNER JOIN exigirá que os dados estejam nas duas tabelas enquanto que o OUTER exigirá que os dados estejam em apenas uma das tabelas (se for LEFT será a tabela à esquerda, se for RIGHT será a tabela à direita). Suponha as seguintes tabelas: Clientes (IDCliente, NomeCliente) Pedidos (IDPedido, IDCliente, TotalPedido) Se usarmos o INNER JOIN só conseguiremos retornar os clientes que tem pedidos e os pedidos que tem clientes. Ex: SELECT NomeCliente, TotalPedido FROM Clientes INNER JOIN Pedidos ON Clientes.IDCliente = Pedidos.IDCliente Se usarmos o LEFT OUTER JOIN, teremos todos os clientes (inclusive os que não fizeram pedidos). Para os clientes que não fizeram pedidos, as colunas de pedidos retornam nulo. Isso ocorre porque a tabela Clientes está à esquerda na comparação e porque usamos o operador LEFT SELECT NomeCliente, TotalPedido FROM Clientes LEFT OUTER JOIN Pedidos ON Clientes.IDCliente = Pedidos.IDCliente Se usarmos o RIGHT OUTER JOIN, teremos todos os pedidos (inclusive os sem cliente se a regra de negócio permitir). Para os pedidos sem clientes, as colunas de cliente retornam nulo. Isso ocorre porque a tabela Pedidos está à direita na comparação e porque usamos o operador RIGHT. SELECT NomeCliente, TotalPedido FROM Clientes RIGHT OUTER JOIN Pedidos ON Clientes.IDCliente = Pedidos.IDCliente Há diferença sim de desempenho, mas não sei se isso é relevante, uma vez que há também diferença de resultado. Em todo caso, você deve ser coerente com sua regra de negócio. Observe que na segunda consulta utilizamos um RIGHT JOIN para retornar pedidos sem clientes. Se sua regra diz que isso é impossível e a coluna de IDCliente em Pedidos não pode ser nula, usar o RIGHT ou o INNER irá levar ao mesmo resultado e nesse caso o INNER deve ser utilizado. REFERÊNCIAS ELMASRI, Ramez; NAVATHE, Shamkant. B. Sistemas de Banco de Dados. 6ª Edição. São Paulo: Pearson, 2011. GUIMARÃES, Célio Cardoso. Fundamentos de Banco de Dados: Modelagem, Projeto e SQL. 1ª Edição. Campinas SP:Unicamp, 2008. <http://social.msdn.microsoft.com/forums/pt-BR/transactsqlpt/thread/fbd9b43b-f3fc-45af-83aa-da90259a7787> acessado em 27 set 2012. Left join e inner join: junção em consultas SQL Tutorial que mostra passo a passo como funcionam os operadores de join: left join e inner join, usados para fazer junção de dados de duas ou mais tabelas em SQL. Left join é uma operação em consultas SQL para retornar todos os dados da coluna esquerda, independente de haver ou não dados na coluna da direita. Imagine que você deseja listar todos os clientes de uma empresa, os valores de compras dentro do mês. Se você deseja que apareça todos os clientes, independente de terem feito compras ou não, então o Left join é a junção ideal para esta consulta SQL. A junção Inner join é um pouco diferente do left join, se aplicarmos ao exemplo anterior veremos que ela só retornaria os clientes que tiveram compras no mês. Alguns exemplos de left join e inner join. Primeiro vamos criar três tabelas no banco de dados: empregados, pagamentos e descontos. create table empregados( codigo_empregado int, nome varchar(50) ) create table pagamentos( codigo_pagto int, codigo_empregado int, valor decimal(10,2) ) create table descontos( codigo_desconto int, codigo_empregado int, valor decimal(10,2) ) Agora vamos inserir alguns dados nas tabelas criadas a fim de podermos reproduzir nossos exemplos. insert into empregados(codigo_empregado,nome) values(1,'Luis') insert into empregados(codigo_empregado,nome) values(2,'Marina') insert into empregados(codigo_empregado,nome) values(3,'Letícia') insert into empregados(codigo_empregado,nome) values(4,'Gustavo') insert into empregados(codigo_empregado,nome) values(5,'Mateus') insert into pagamentos(codigo_empregado,valor) values(1,100) insert into pagamentos(codigo_empregado,valor) values(1,200) insert into pagamentos(codigo_empregado,valor) values(3,300) insert into pagamentos(codigo_empregado,valor) values(5,400) insert into pagamentos(codigo_empregado,valor) values(5,500) insert into descontos(codigo_empregado,valor) values(1,50) insert into descontos(codigo_empregado,valor) values(2,20) insert into descontos(codigo_empregado,valor) values(5,30) Agora que já temos os dados populados na tabela, vamos ver um exemplo de Inner Join. select e.nome, p.valor as pagamento from empregados as e INNER JOIN pagamentos as p ON e.codigo_empregado = p.codigo_empregado Apesar de termos cinco empregados na tabela, ele mostrou apenas três, o motivo é que apenas estes três tem pagamentos. Veja que o inner join fez uma junção entre empregados e pagamentos e desconsiderou os empregado sem pagamentos. Vamos a outro exemplo de Inner join select e.nome, p.valor as pagamento, d.valor as desconto from empregados as e INNER JOIN pagamentos as p ON e.codigo_empregado = p.codigo_empregado INNER JOIN descontos as d ON e.codigo_empregado = d.codigo_empregado Neste caso apenas dois empregados foram mostrados já que incluímos na consulta os descontos, ou seja, a leitura que esta consulta fez é: mostrar empregados quem tem pagamentos e descontos. Agora veremos os exemplos de Left join select e.nome, p.valor as pagamento from empregados as e LEFT JOIN pagamentos as p ON e.codigo_empregado = p.codigo_empregado Os empregados 3 e 5 não tem pagamentos, mas ainda assim eles apareceram na consulta, já que a função Left Join considera apenas a coluna da esquerda e retorna Null (nulo) quando a coluna da direita não tiver um valor correspondente. Vamos incluir os descontos para entender melhor o left join. select e.nome, p.valor as pagamento, d.valor as desconto from empregados as e LEFT JOIN pagamentos as p ON e.codigo_empregado = p.codigo_empregado LEFT JOIN descontos as d ON e.codigo_empregado = d.codigo_empregado O que fizemos foi uma espécie de left join em cascata e é útil quando queremos partir de uma base (empregados) e listar todas as correspondências ou não das tabelas (pagamentos e descontos) a ela relacionadas. Bom, espero que estes pequenos e simples exemplos de consultas SQL possa ter ajudado na compreensão do Left join e Inner join, já que tenho percebido que muita gente tem dificuldade de compreender as diferenças entre eles. Join ou Junções SQL | Inner, left, right e full outer join Tutorial passo-a-passo de join em SQL. Aprenda as diferenças entre as junções cross, inner, left, right e full outer join. Join ou junção em SQL é um assunto que provoca muitas dúvidas, afinal, sempre que juntamos duas ou mais tabelas, poderemos ter resultados bem diferentes dependendo do tipo de junção que estamos fazendo. Escrevi um post sobre o left join e inner join e pelo visto o pessoal gosto da abordagem, agora, quero ir além e vou incluir além do left e inner, o right join, cross join e o full outer join. Primeiro vou criar duas tabelas simples no banco de dados: Marcas e Carros. create table carros( marca varchar(100), modelo varchar(100), ano int, cor varchar(100) ) create table marcas( marca varchar(50), nome varchar(50) ) Com as tabelas criadas, vou inserir alguns dados nelas: insert into marcas values('VW','Volkswagem') insert into marcas values('Ford','Ford') insert into marcas values('GM','General Motors') insert into marcas values('Fiat','Fiat') insert into marcas values('Renault','Renault') insert into marcas values('MB','Mercedes Bens') insert into carros values('VW','Fox',2005,'preto'); insert into carros values('VW','Fox',2008,'preto'); insert into carros values('Ford','Ecosport',2009,'verde'); insert into carros values('Ford','KA',2008,'prata'); insert into carros values('Fiat','Punto',2008,'branco'); insert into carros values('Fiat','Uno',2007,'preto'); insert into carros values('Fiat','Stilo',200,'4prata'); insert into carros values('Fiat','Uno',2005,'prata'); insert into carros values('Fiat','Stilo',2008,'verde'); insert into carros values('Fiat','Uno',2009,'branco'); insert into carros values('Peugeot','207',2010,'prata'); insert into carros values('Peugeot','207',2010,'prata'); insert into carros values('Peugeot','207',2007,'azul'); insert into carros values('Chrysler','300 C',2008,'verde'); Vamos analisar como ficaram as duas tabelas: select * from marcas select * from carros Perceba que temos 6 registros na tabela Marcas e 14 registros na tabela Carros. Agora com os dados inseridos, vamos aos exemplos de cross join, inner join, left join, right join e full outer join. Cross Join A junção cross join irá juntar todos os registros da tabela marcas com todos os registros da tabela carros, formando um produto cartesiano. Veja o exemplo abaixo: select m.nome, c.modelo from marcas as m cross join carros as c Inner Join A junção inner join irá juntar os registros da tabela marca que tiver um correspondente na tabela carros. Essa correspondência é feita pelos campos marca que está presente nas duas tabelas. Embora não esteja explícito, mas o campo marca seria a chave primária (na tabela marcas) e chave estrangeira (na tabela carros). Veja o exemplo: select m.nome, c.modelo from marcas as m inner join carros as c on c.marca = m.marca Apenas 10 registros satisfazem o inner join. Podemos dizer que 10 carros estão associados a alguma marca, enquanto que os demais não. Left Join O left join irá fazer a junção das duas tabelas “dando preferência” aos registros da tabela marcas. Assim, todos os registros da tabela marcas serão mostrados, independente de haver correspondência na tabela carros. Quando não houver correspondência na tabela carros, será mostrado o valor NULL ou nulo. Exemplo: select m.nome, c.modelo from marcas as m left join carros as c on c.marca = m.marca As marcas General Motors, Renault e Mercedes Bens não tem nenhum carro cadastrado, mesmo assim elas apareceram no resultado. Right Join A junção right join funciona de forma inversa ao left join. Aplica-se o mesmo conceito, porém, de forma invertida. Com o right join será mostradotodos os carros, mesmo aqueles que não estejam associados a nenhum registro da tabela marcas. Exemplo: select m.nome, c.modelo from marcas as m right join carros as c on c.marca = m.marca 207 e 300 C são modelos que estão cadastrados em carros, mas não estão associados a nenhuma marca. Full Outer Join A junção full outer join seria o mesmo que left join e right join juntas, ou seja, ela irá mostrar todos as marcas e todos os carros, independente de existir valores correspondente na tabela oposta. Veja um exemplo: select m.nome, c.modelo from marcas as m full outer join carros as c on c.marca = m.marca Os exemplos mostrados foram feitos no SQL Server 2008 versão Express. REFERÊNCIA http://www.luis.blog.br/left-join-e-inner-join-juncao-em-consultas-sql.aspx acessado em 27 set 2012.
Compartilhar