Introduction to Outer Joins
Outer Joins
Outer joins are used to match rows from two tables. Mesmo que não haja linhas de correspondência estão incluídas. Linhas de uma das tabelas são sempre incluídas, para a outra, quando não há correspondências, valores nulos são incluídos.
A série começa com o artigo Introdução às ligações à base de dados. Todos os exemplos para esta lição são baseados no Microsoft SQL Server Management Studio e na Base de dados AdventureWorks2012. Você pode começar a usar estas ferramentas gratuitas usando o meu guia começar a usar o servidor SQL., Neste artigo vamos cobrir as juntas exteriores.
tipos de juntas exteriores
Existem três tipos de juntas exteriores:
- junção externa esquerda – todas as linhas da tabela esquerda estão incluídas, as linhas não compensadas da direita são substituídas por valores nulos.
- conjunto exterior da direita-todas as linhas da tabela direita são incluídas, as linhas não compensadas da esquerda são substituídas por valores nulos.
- Todas as linhas de junção exteriores completas de ambas as tabelas estão incluídas; os valores nulos preenchem as linhas não compensadas.vamos cavar mais fundo e explorar a junção exterior esquerda.,
Junta exterior esquerda
confira o seguinte modelo de dados. Isto é tirado da base de dados AdventureWorks2012. Neste modelo, há 1 pessoa para 0 ou 1 funcionários.
Para criar uma lista de tudo que uma Pessoa LastNames, mas também mostrar JobTitle se a Pessoa é um Empregado, precisamos de uma maneira de juntar as duas tabelas, e incluir a Pessoa linhas no resultado, mesmo se elas não corresponderem ao Empregado.
Este tipo de junção é chamado de junção externa esquerda, uma vez que todas as linhas para a tabela do lado esquerdo da palavra-chave de junção são incluídas independentemente da correspondência., A sintaxe básica para um left outer join é:
SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition
O SQL para a associação no diagrama acima é:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
Aqui estão os primeiros resultados da consulta
Repare como existem valores NULOS listados na segunda linha para NationalIDNumber e JobTitle. Tal deve-se ao facto de não existirem trabalhadores que correspondam à idade de 293 anos.
junção externa direita
vamos dar outra olhada no diagrama, mas desta vez estamos fazendo uma junção externa direita., Como você deve ter adivinhado, não há muita diferença na declaração SQL entre uma junção externa esquerda e uma junção externa direita. A sintaxe básica para uma associação externa direita é:
SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition
Abaixo é o nosso exemplo de consulta por escrito, como uma associação externa à direita:
SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
A principal diferença é que agora estamos indo para retornar todos os registros da tabela de Funcionários, que é a tabela à direita da associação de palavras-chave. Se um registro de empregado correspondente não for encontrado, então NULL será devolvido para o BusinessEntityID e LastName.
Aqui estão os resultados da consulta.,
i scrolled through all the results and was surprised to not see any null values.sabes porquê?
A resposta está no modelo de dados. Há um 0..1 a 1 relação entre Empregado e pessoa. Isto significa que para cada empregado existe uma pessoa. Dado isto, para a junção direita não existirão nenhumas linhas não correspondentes. Com este tipo de Relacionamento Você também poderia ter usado uma junção interior.
Junta externa esquerda versus direita
não há diferença de funcionalidade entre uma junção externa esquerda e uma junção externa direita.,
a declaração
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID
Devolve o mesmo resultado que
SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID
claro que não seria o caso se eu só tivesse mudado a junção da esquerda para a direita e não tivesse mudado os nomes das tabelas.normalmente uso juntas exteriores à esquerda mais do que as juntas exteriores à direita. Acho que é porque quando desenho relações faço-o da esquerda para a direita. Além disso, atravesso mesas na minha cabeça da esquerda para a direita.
isto encaixa bem com SQL como a tabela “esquerda” está na declaração de FROM.estou curioso para saber o que usa., Estou realmente curioso para saber se uma junta direita parece mais intuitivo para você se você é falante nativo de árabe ou alguma outra língua “direita para esquerda”.
junção exterior completa
uma junção exterior completa é a combinação de resultados de uma junção externa esquerda e direita. Os resultados retornados deste tipo de junção incluem todas as linhas de ambas as tabelas. Quando ocorrem correspondências, os valores estão relacionados. Onde correspondido de qualquer mesa não, em seguida, NULL são devolvidos em vez disso.,
A sintaxe básica para uma junção exterior completa é:
SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition
vamos dar uma olhada em uma porção diferente da Base de dados AdventureWork2012. Desta vez, vamos concentrar-nos nas relações entre as tabelas vendedoras e as tabelas correntes.
O modelo é mostrado abaixo:
SELECT sales.SalesOrderHeader.AccountNumber, sales.SalesOrderHeader.OrderDate, sales.CurrencyRate.ToCurrencyCode, sales.CurrencyRate.AverageRateFROM sales.SalesOrderHeaderFULL OUTER JOIN sales.CurrencyRate ON sales.CurrencyRate.CurrencyRateID = sales.SalesOrderHeader.CurrencyRateID
Aqui está uma parte dos resultados mostrando onde algumas vendas têm correspondência com uma moeda e outras não., A razão pela qual há vendas que não coincidem é que estas são vendas em USD.
mais abaixo nos resultados você vê moedas sem vendas correspondentes. Tal reflecte o facto de não terem sido efectuadas vendas nessas moedas.
Nota: fiquei surpreso ao ver USD listado, consulte linha 42463, pois eu acho que a maioria das vendas seria nesta moeda. A minha ideia é que, em vez de reverenciar a taxa de câmbio para esta transacção, o Vale do Correnterroader para o CurrencyRateID foi definido como nulo para todas as transacções em USD., Eu acho que isso é inconsistente, e não é a maneira que eu faria isso, mas não é a minha base de dados…
exemplo avançado
até agora nós olhamos para os três tipos de junções externas, mas não explorou alguns conceitos mais avançados, tais como unir tabela múltipla e usar mais de uma condição em nossas cláusulas de adesão.
Nós cobrimos esses conceitos quando exploramos as juntas internas, então o que eu vou estar mostrando a vocês, não deve ser muito novo, mas eu acho que ainda faz sentido rever, uma vez que em alguns casos a mistura completa com juntas internas pode produzir resultados inesperados ou não intencionais.,vamos voltar nosso foco para o esquema de produção e explorar produtos e categorias. Vamos produzir uma lista de todas as categorias de produtos e os modelos de produtos contidos dentro.o produto tem uma relação estreita com o produto e a estratégia dos produtos. Uma vez que se situa entre estas duas tabelas, existe uma relação implícita entre o Produtodel e a subcategoria de produtos. Devido a isso, é um bom candidato para juntas externas, pois pode haver modelos de produtos sem produtos atribuídos e entradas de Subcategoria de produtos sem Produto.,
aqui está o SQL
SELECT PC.Name AS Category, PSC.Name AS Subcategory, PM.Name AS Model, P.Name AS ProductFROM Production.Product AS PFULL OUTER JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelIDFULL OUTER JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryIDINNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDORDER BY PC.Name, PSC.Name
Existem vários itens a notar:
- I usou os pseudónimos de tabela para tornar o SQL mais legível.existe mais do que uma cláusula de junção exterior completa.,
- O ProductCategory tabela é também parte de uma associação externa
Originalmente, quando eu escrevi o SQL para esta consulta, tive uma associação interna entre ProductSubcategory e ProductCategory, mas eu não estava vendo valores NULOS para registros não coincidentes eu seria de esperar.uma vez que mudei a junção para uma junção exterior completa, vi os resultados que esperava. A razão disso acontecer é sutil.
Depois de verificar os dados, confirmei que todas as categorias são classificadas como subcategorias., Dado isto, você pensaria que uma junção interna funcionaria; no entanto, considere que, como a declaração inteira é executada e as linhas são devolvidas, o valor ProductSubcategoryID é nulo sempre que um produto não corresponde a uma subcategoria de produto.
valores nulos, por definição, não são iguais um ao outro, por isso a junção interna falha. Dado isto, quando estes valores são então combinados com uma categoria de Produtos, não são incluídos no resultado, a menos que a junção à categoria de produtos seja uma junção externa.,
De fato, a associação não tem que ser um full outer join, left join funciona assim:
SELECT PC.Name AS Category,PSC.Name AS Subcategory,PM.Name AS Model,P.Name AS ProductFROM Production.Product AS PFULL OUTER JOINProduction.ProductModel AS PMON PM.ProductModelID = P.ProductModelIDFULL OUTER JOINProduction.ProductSubcategory AS PSCON PSC.ProductSubcategoryID = P.ProductSubcategoryIDLEFT OUTER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDORDER BY PC.Name, PSC.Name
Usa para Associações Externas
Devido a associações externas não somente as linhas correspondentes, mas também aqueles que não, eles são uma boa maneira de encontrar ausente entradas em tabelas. Isso é ótimo quando você precisa fazer o diagnóstico em seu banco de dados para determinar se existem problemas de integridade de dados.
por exemplo, suponha que estávamos preocupados que podemos ter algumas entradas de Subcategoria de produtos que não correspondem a categorias., Poderíamos testar executando o seguinte SQL
SELECT PSC.Name AS SubcategoryFROM Production.ProductCategory AS PSCLEFT OUTER JOIN Production.ProductSubcategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryIDWHERE PSC.ProductCategoryID is NULL
a junção externa devolve os valores da linha não compensada como valores nulos. A cláusula “where” filtra os valores não nulos, deixando apenas nomes de subcategorias não-Matching para nós revisarmos.
= ligações externas também pode ser usado para fazer perguntas como:
“que pessoas de vendas nunca fizeram uma venda?”
” Que produtos não são atribuídos a um modelo de produto?”
” Que departamentos não têm empregados destacados?”
” listar todos os territórios de vendas não atribuídos pessoas de vendas.”