Introducción a las uniones externas

uniones externas

Las uniones externas se utilizan para hacer coincidir filas de dos tablas. Incluso si no hay filas de coincidencia se incluyen. Las filas de una de las tablas siempre se incluyen, para la otra, cuando no hay coincidencias, se incluyen valores nulos.

la serie comienza con el artículo Introduction to Database Joins. Todos los ejemplos de esta lección se basan en Microsoft SQL Server Management Studio y la base de datos AdventureWorks2012. Puede comenzar a usar estas herramientas gratuitas utilizando mi guía Introducción a SQL Server., En este artículo vamos a cubrir las uniones externas.

tipos de uniones externas

Hay tres tipos de uniones externas:

• unión externa izquierda: se incluyen todas las filas de la tabla izquierda, las filas no coincidentes de la derecha se reemplazan por valores nulos.
• unión externa derecha: se incluyen todas las filas de la tabla derecha, las filas no coincidentes de la izquierda se reemplazan por valores nulos.
• unión externa completa: se incluyen todas las filas de ambas tablas, los valores nulos llenan las filas no coincidentes.

vamos a profundizar y explorar la Unión Exterior Izquierda.,

LEFT Outer Join

echa un vistazo al siguiente modelo de datos. Esto es tomado de la base de datos AdventureWorks2012. En este modelo, hay 1 persona a 0 o 1 empleados.

para construir una lista de todos los nombres de personas, pero también mostrar JobTitle si la persona es un empleado, necesitamos una forma de unir las dos tablas e incluir filas de personas en el resultado, incluso si no coinciden con Employee.

este tipo de unión se denomina unión externa izquierda, ya que todas las filas de la tabla del lado izquierdo de la palabra clave Unión se incluyen independientemente de la coincidencia., La sintaxis básica para una unión externa izquierda es:

SELECT columnlistFROM tableLEFT OUTER JOIN othertable ON join condition

el SQL para la Unión en el diagrama anterior es:

SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

Aquí están los primeros resultados de la consulta

observe cómo hay valores NULL listados en la segunda fila para nationalidnumber y JobTitle. Esto se debe a que no hay empleados que coincidan con la empresa ID 293.

Unión exterior derecha

echemos otro vistazo al diagrama, pero esta vez estamos haciendo una unión exterior derecha., Como puede haber adivinado, no hay demasiada diferencia en la instrucción SQL entre una unión externa izquierda y una unión externa derecha. La sintaxis básica para una unión externa derecha es:

SELECT columnlistFROM tableRIGHT OUTER JOIN othertable ON join condition

a continuación se muestra nuestra consulta de ejemplo escrita como una unión externa derecha:

SELECT person.Person.BusinessEntityID, Person.Person.LastName, HumanResources.Employee.NationalIDNumber, HumanResources.Employee.JobTitleFROM person.PersonRIGHT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

la diferencia clave es que ahora vamos a devolver todos los registros de la tabla Employee, que es la tabla a la derecha de la palabra clave join. Si no se encuentra un registro de empleado coincidente, se devolverá NULL para BusinessEntityID y LastName.

Aquí están los resultados de la consulta.,

recorrí todos los resultados y me sorprendió no ver ningún valor nulo.

¿sabes por qué?

la respuesta está en el modelo de datos. Hay un 0..Relación 1 a 1 entre empleado y persona. Esto significa que por cada empleado hay una persona. Dado esto, para la combinación correcta no existirán filas que no coincidan. Con este tipo de relación también podrías haber usado una unión interior.

uniones externas izquierda versus derecha

no hay diferencia en la funcionalidad entre una unión externa izquierda y una unión externa derecha.,

la instrucción

SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM person.PersonLEFT OUTER JOIN HumanResources.Employee ON person.BusinessEntityID = Employee.BusinessEntityID

devuelve el mismo resultado que

SELECT person.Person.BusinessEntityID, HumanResources.Employee.NationalIDNumberFROM HumanResources.EmployeeRIGHT OUTER JOIN person.Person ON person.BusinessEntityID = Employee.BusinessEntityID

Por supuesto, este no sería el caso si solo hubiera cambiado la combinación de izquierda a derecha y no hubiera cambiado los nombres de las tablas.

normalmente uso las uniones externas izquierdas más que las uniones externas derechas. Creo que esto es porque cuando dibujo relaciones lo hago de izquierda a derecha. Además, atravieso mesas en mi cabeza de izquierda a derecha.

esto encaja bien con SQL ya que la tabla «izquierda» está en la instrucción FROM.

Tengo curiosidad por saber qué usas., Tengo mucha curiosidad por saber si una unión a la derecha te parece más intuitiva Si eres hablante nativo de árabe o de algún otro idioma «de derecha a izquierda».

Unión exterior completa

una unión exterior completa es la combinación de resultados de una unión Exterior Izquierda y derecha. Los resultados devueltos de este tipo de combinación incluyen todas las filas de ambas tablas. Cuando se producen coincidencias, los valores están relacionados. Si no coincide con ninguna de las tablas, se devuelve NULL en su lugar.,

la sintaxis básica para una unión externa completa es:

SELECT columnlistFROM tableFULL OUTER JOIN othertable ON join condition

echemos un vistazo a una parte diferente de la base de datos AdventureWork2012. Esta vez, nos vamos a centrar en las relaciones entre SalesOrderHeader y currency rate tablas.

El modelo se muestra a continuación:

Supongamos que queremos saber todas las monedas que podamos colocar órdenes y que las órdenes se coloca en esas monedas?

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

Aquí hay una parte de los resultados que muestran donde algunas ventas tienen coincidencia con una moneda y algunas que no., La razón por la que hay ventas que no coinciden es que se trata de ventas en USD.

más abajo en los resultados verá monedas sin ventas coincidentes. Esto refleja el hecho de que no se realizaron ventas en esas monedas.

nota: me sorprendió ver USD en la lista, ver fila 42463, ya que creo que la mayoría de las ventas serían en esta moneda. Mi pensamiento es que en lugar de reverenciar el tipo de cambio para estas transacciones, el SalesOrderHeader vale para CurrencyRateID se estableció en null para todas las transacciones en USD., Creo que esto es inconsistente, y no es la forma en que lo haría, pero no es mi base de datos

ejemplo avanzado

hasta ahora hemos mirado los tres tipos de uniones externas, pero no hemos explorado algunos conceptos más avanzados, como unir varias tablas y usar más de una condición en nuestras cláusulas de unión.

cubrimos estos conceptos cuando exploramos las uniones internas, por lo que lo que les mostraré no debería ser demasiado nuevo, pero creo que aún tiene sentido revisarlo, ya que en algunos casos mezclar uniones completas con uniones internas puede producir resultados inesperados o no deseados.,

centrémonos en el esquema de producción y exploremos Productos y categorías. Vamos a producir una lista de todas las categorías de productos y los modelos de productos contenidos dentro.

El producto tiene una relación de uno a muchos con ProductModel y ProductSubcategory. Dado que se encuentra entre estas dos tablas, hay una relación implícita de muchos a muchos entre ProductModel y ProductSubcategory. Debido a esto, es un buen candidato para uniones externas, ya que puede haber modelos de productos sin productos asignados y entradas de categorías de productos sin productos.,

para superar esta situación haremos una unión externa a la tabla ProductModel y ProductCategory.

Aquí está el 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

hay varios elementos a tener en cuenta:

• utilicé alias de tabla para hacer que el SQL sea más legible.
• Hay más de una cláusula de unión externa completa.,
• La tabla ProductCategory también es parte de una unión externa

originalmente cuando escribí el SQL para esta consulta tenía una unión interna entre ProductSubcategory y ProductCategory, pero no estaba viendo valores nulos para registros no coincidentes que esperaría.

Una vez que cambié la unión a una unión externa completa, vi los resultados que esperaba. La razón por la que esto ocurre es sutil.

después de comprobar los datos confirmé que todas las categorías son subcategorías asignadas., Dado esto, usted pensaría que una unión interna funcionaría; sin embargo, considere que como se ejecuta toda la instrucción y se devuelven las filas, el valor ProductSubcategoryID es NULL siempre que un producto no coincida con una subcategoría de producto.

los valores nulos, por definición, no son iguales entre sí, por lo que la unión interna falla. Dado esto, cuando estos valores se comparan con ProductCategory, no se incluyen en el resultado a menos que la unión a ProductCategory sea una unión externa.,

de hecho, la unión no tiene que ser una unión externa completa, una unión izquierda funciona igual de bien:

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

utiliza para las uniones externas

porque las uniones externas no solo combinan las filas coincidentes sino también las que no lo hacen, son una muy buena manera de encontrar las entradas faltantes en las tablas. Esto es genial cuando necesita hacer un diagnóstico en su base de datos para determinar si hay problemas de integridad de datos.

por ejemplo, supongamos que nos preocupa que podamos tener algunas entradas de ProductSubcategory que no coinciden con las categorías., Podríamos probar ejecutando el siguiente 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

la unión externa devuelve los valores de fila no coincidentes como valores nulos. La cláusula where filtra los valores que no son nulos, dejando solo nombres de subcategorías que no coincidan para que los revisemos.

Outer joins también se puede usar para hacer preguntas como:

«¿Qué vendedores nunca han hecho una venta?»

» ¿Qué productos no se asignan a un modelo de producto?»

» ¿Qué departamentos no tienen empleados asignados?»

» lista todos los territorios de Ventas no asignados a los vendedores.”

---