El operador JOIN (Unión o combinación) en SQL Server

Aplicado sobre BD Northwind y AdeventureWorks

Los operadores JOIN permiten retornar datos de columnas almacenadas en múltiples tablas relacionadas. Como las relaciones son creadas a partir de un PRIMARY KEY (Llave primaria) y un FOREIGN KEY (Llave foránea) esto quiere decir que existirán columnas repetidas en tablas relacionadas, otro aspecto que debemos tomar en cuenta es que podemos encontrar el mismo nombre de columna en varias tablas por lo que es muy aconsejable el uso de alias.

Cuando se ejecutan los JOIN, SQL Server compara los valores de las columnas especificadas fila por fila entonces usa los resultados de la comparación para combinar los valores que califican como nuevas filas.

Cuando definimos una condición JOIN necesitamos definir las columnas que uniremos o combinaremos, el tipo de combinación y una condición de unión. INNER JOIN es el tipo de unión por defecto cuando solo se especifica solo la palabra JOIN.

Definiendo Inner Joins (Combinaciones internas).

Los Inner Joins retornan solo las columnas que coincidan en la condición de unión. Aunque un Inner Join se puede especificar en cualquiera de la clausulas FROM o WHERE, es recomendable especificar el JOIN en la cláusula FROM.

A partir de aquí utilizaremos las bases de datos Northwind y AdventureWorks indistintamente, para diferenciar las consultas utilizaremos la clausula USE para especificar la base de datos con la que trabajaremos.

El siguiente ejemplo retorna el nombre de producto, cantidad por unidad, precio unitario de la tabla Productos, y el nombre de categoría y la descripción de la tabla Categorías en la base de datos Northwind.

Primero veamos el tipo de relación entre estas tablas, observamos que entre Productos y Categorías existe una relación uno a varios, (no olviden que la tabla con clave externa, foránea o FK es la que esta cerca al símbolo de ocho) y el campo con el que se relacionan es: Products.CategoryID (Foreign Key) con Categories.CategoryID (Primary Key).

Ahora para la consulta en la clausula SELECT agregamos las columnas que necesitamos, las tablas junto al operador INNER JOIN que es el tipo de unión y finalmente la condición

USE Northwind;SELECT ProductName,QuantityPerUnit, UnitPrice,CategoryName, DescriptionFROM dbo.Categories INNER JOIN dbo.ProductsON dbo.Categories.CategoryID = dbo.Products.CategoryID;

Con el uso de alias la misma consulta quedaría así:

Veamos otro ejemplo con la base de datos de AdventureWorks

El siguiente ejemplo retorna los nombres y apellidos de los empleados de la tabla Person.Contact y el titulo laboral, horas enfermo y horas de vacaciones de la tabla HumanResources.Employee

USE AdventureWorks;SELECT FirstName, LastName, HumanResources.Employee.Title,SickLeaveHours, VacationHours

FROM HumanResources.Employee INNER JOIN Person.ContactON HumanResources.Employee.ContactID = Person.Contact.ContactID;

En la consulta anterior tuvimos que especificar que la columna title que necesitamos está en la tabla Employee (HumanResources.Employee.Title) esto porque existe el mismo nombre de columna en la tabla Contact.

Como una consulta interna (INNER JOIN) fue definida, las columnas de la tabla HumanResources.Employee que no coinciden con las columnas en Person.Contact no fueron retornadas, si queremos que suceda lo contrario debemos definir una unión externa o OUTER JOIN

Outer Joins (Combinaciones externas)

Cuando realizamos una combinación interna los resultados son aquellos que coinciden en ambos campos de la condición, eliminado aquellas que no coincidan en ambas filas o

campos, en el ejemplo anterior en la tabla Person.Contact existen casi 20.000 filas o registros pero necesitábamos filtrar solo a los contactos que sean empleados, 290 registros en total.

Una combinación externa (Outer Join) devuelve una o todas las filas de una de las tablas mencionadas en la cláusula FROM, junto a esta clausula debemos utilizar, siempre, ciertos operadores para definir las filas devolverá la consulta, los operadores son LEFT, con este se recuperarán las filas de la tabla izquierda a la que se haya hecho referencia, con RIGHT las filas de la tabla derecha a la que se haya hecho referencia serán devueltos y con el operador FULL, se devuelven todas las filas.

Veamos el anterior ejemplo con una modificación utilizando una combinación externa

USE AdventureWorks;SELECT FirstName, LastName, HumanResources.Employee.Title,SickLeaveHours, VacationHoursFROM HumanResources.Employee RIGHT OUTER JOIN Person.ContactON HumanResources.Employee.ContactID = Person.Contact.ContactIDORDER BY FirstName;

El resultado de esta consulta será todos los contactos (tabla a la derecha) y para los que sean empleados obtendremos su titulo laboral, horas enfermo y horas de vacaciones, si cambiamos por ejemplo el operador RIGHT por LEFT obtendremos solo los nombres de los empleados, inténtalo.

Como se vio en el ejemplo la sintaxis de una combinación externa es operador OUTER JOIN pero también podemos escribir sin la palabra OUTER, solo LEFT JOIN, por ejemplo.

Trabajando con más de dos tablas

Podemos unir más de dos tablas para acceder las columnas requeridas para alguna consulta. Una recomendación general de rendimiento es limitar en lo posible el número de tablas que participan en el JOIN porque SQL Server se tomara más tiempo en resolver la consulta.

El tipo más común de JOIN para más de dos tablas es el INNER JOIN, el siguiente código devuelve el nombre de producto (tabla Products), el precio por producto (tabla Products), el nombre a la categoría que pertenece (tabla Categories), una descripción de esta categoría (tabla Categories), la compañía proveedora (tabla Suppliers) y la ciudad del proveedor (tabla Suppliers), de la base de datos Northwind.

Como vemos en el diagrama, las tablas están relacionadas de la siguiente forma:Products.CategoryID (FK) con Categories.CategoryID (PK)Products.SupplierID (FK) con Suppliers.SupplierID (PK)

Entonces la consulta vendría a ser:

USE Northwind;SELECT ProductName AS [Nombre del Producto],UnitPrice AS [Precio del producto],CategoryName AS [Categoria],"Description" AS [Descripcion de categoria],CompanyName AS [Proveedor],City AS [Ciudad del proveedor]FROM dbo.Products P INNER JOIN dbo.Categories CON P.CategoryID = C.CategoryIDINNER JOIN dbo.Suppliers S ON P.SupplierID = S.SupplierID;

Noten que cuando no utilizamos la palabra reservada AS tomará la siguiente palabra o cadena entre corchetes como el alias, también observen que en Description agregamos una comillas esto para evitar el error de Nombre de columna no válido, producido cuando el SQL no lo reconoce como campo.

Ahora veamos un ejemplo con la base de datos AdventureWorks, Ahora necesitamos retornar los nombres y apellidos de los empleados de la tabla Person.Contact, el titulo laboral, horas enfermo y horas de vacaciones de la tabla HumanResources.Employee y la dirección de los empleados de la tabla Person.Address, veamos el diagrama de relaciones:

Al ver el diagrama de tablas vemos que la dirección que necesitamos se encuentra en la tabla Person.Address la que no esta relacionada directamente con la tabla HumanResources.Employee, pero la tabla HumanResources.EmployeeAddress relaciona ambas, veamos como generar la consulta:

USE AdventureWorks;SELECT FirstName, LastName,HumanResources.Employee.Title, SickLeaveHours, VacationHours,AddressLine1 FROM HumanResources.Employee INNER JOIN Person.Contact ON HumanResources.Employee.ContactID = Person.Contact.ContactID INNER JOIN HumanResources.EmployeeAddressON HumanResources.Employee.EmployeeID = HumanResources.EmployeeAddress.EmployeeID INNER JOIN Person."Address"ON HumanResources.EmployeeAddress.AddressID = Person."Address".AddressID;

El resultado contendrán las filas deseadas utilizando tres combinaciones internas, también agregamos comentarios que como seguro ya lo notaron tiene la forma-- Comentario

Utilizando un Self-Join

Una self-join o auto-combinación es cuando se realiza una consulta a la tabla es referenciada, esto se consigue utilizando un alias diferente cada vez que referenciamos la tabla.

Veamos por ejemplo la tabla Employee,en la base de datos AdventureWorks, en esta tabla existe una columna llamada ManagerID, con esta columna podemos saber quien es el superior de cada empleado, ahora lo que queremos es saber quien es el Supervisor de cada empleado, para esto utilizaremos una auto-combinación, veamos:

USE AdventureWorksSELECT E.EmployeeID, E.Title, M.ManagerIDFROM HumanResources.Employee E INNER JOIN HumanResources.Employee MON E.ManagerID = M.EmployeeIDORDER BY ManagerID;

Ahora para que nuestra consulta sea más completa la combinaremos con la tabla Contact para obtener el nombre del empleado y el nombre del supervisor

USE AdventureWorksSELECT P.FirstName + ' ' + P.LastName AS 'Nombre del Empleado', E.Title AS 'Cargo del empleado', MP.FirstName + ' ' + MP.LastName AS 'Nombre de Supervisor', M.Title AS 'Cargo del Supervisor'FROM HumanResources.Employee E INNER JOIN HumanResources.Employee MON E.ManagerID = M.EmployeeID INNER JOIN Person.Contact PON E.ContactID = P.ContactIDINNER JOIN Person.Contact MPON M.ContactID = MP.ContactIDORDER BY [Cargo del Supervisor];

Alguien se preguntara porque solo tenemos 289 filas como resultado si es que tenemos 290 empleados, el empleado que falta es el Director ejecutivo (Chief executive officer) que como imaginaran no tiene supervisor por tanto en la columna ManagerID tiene como dato NULL y en la condición no especificamos que podría existir un valor NULL, para solucionar esto podríamos agregar a la condición del primer JOIN el operador lógico AND quedando:ON E.ManagerID = M.EmployeeID OR E.ManagerID = NULLCon lo que obtendremos todos los empleados y sus supervisores, si prueban esta modificación observaran que Ken Sánchez (Director ejecutivo) es supervisor de todos los empleados.

Para terminar esta entrega del curso hagamos unos ejercicios:

EJERCICIOS

1. Escriba y ejecute un código para retornar información de la tabla Productos donde el nombre de producto coincida con Queso, use la base de datos Northwind

USE Northwind;SELECT ProductID, CategoryID, ProductName AS [Nombre de Producto]FROM dbo.Products WHERE ProductName LIKE '%Queso%';

Note que en el resultado está incluido el ID de categoría pero no el nombre de la categoría, ahora en la consulta siguiente escriba y ejecute un código para unir la tabla Productos con la tabla Categorías para recuperar la información del nombre de la

categoría al que pertenece el producto. Puede utilizar una consulta INNER JOIN porque está buscando solo las filas que sean iguales en ambas tablas.

USE Northwind;SElECT ProductID, dbo.Categories.CategoryID, ProductName AS [Nombre de Producto], CategoryName [Categoria del Producto]FROM dbo.Products INNER JOIN dbo.CategoriesON dbo.Products.CategoryID = dbo.Categories.CategoryIDWHERE ProductName LIKE '%Queso%';

Nótese que en la cláusula SELECT al momento de indicar la columna CategoryID tuvimos que agregar la tabla a la que pertenece dbo.Categories.CategoryID, esto lo hacemos porque caso contrario SQL no sabrá de cuál de las tablas tomar la información si de Productos o de Categorías y lo interpretara como una ambiguedad.

2. De la base de datos AdventureWorks ejecute un comando que retorne información de la tabla ProductSubcategory donde los nombres de subcategorias contengan la palabra bike

USE AdventureWorks;SELECT ProductSubcategoryID, ProductCategoryID,Name AS 'Nombre de Subcategoria'FROM Production.ProductSubcategoryWHERE Name LIKE '%Bike%'ORDER BY [Nombre de Subcategoria];

En la consulta anterior logramos obtener ProductCategoryID pero no el nombre de la Categoría, ahora ejecutemos una consulta para combinar la tabla ProductCategory con la tabla ProductSubcategory, para obtener el nombre de categoría.

USE AdventureWorks;SELECT ProductSubcategoryID, Production.ProductCategory.ProductCategoryID,Production.ProductSubcategory.Name AS 'Nombre de Subcategoria',Production.ProductCategory.Name AS 'Nombre de Categoria'FROM Production.ProductSubcategory INNER JOIN Production.ProductCategoryON Production.ProductCategory.ProductCategoryID =Production.ProductSubcategory.ProductCategoryIDWHERE Production.ProductSubcategory.Name LIKE '%Bike%'ORDER BY [Nombre de Categoria];

Ahora añade la tabla Products para ver que productos existen en cada una de esas subcategorias

USE AdventureWorks;SELECT P.ProductID AS 'ID de Producto',PSC.ProductSubcategoryID AS 'ID de Subcategoria',PC.ProductCategoryID AS 'ID de Categoria',P.Name AS 'Nombre de Producto',PSC.Name AS 'Nombre de Subcategoria',PC.Name AS 'Nombre de Categoria'FROM Production.ProductSubcategory AS PSCINNER JOIN Production.ProductCategory AS PCON PC.ProductCategoryID = PSC.ProductCategoryIDINNER JOIN Production.Product AS PON P.ProductSubcategoryID = PSC.ProductSubcategoryIDWHERE PSC.Name LIKE '%Bike%'ORDER BY [Nombre de Subcategoria] ;

Una vez más, se utiliza una combinación interna, porque sólo las filas en común entre las tres tablas necesitan ser recuperados

Resumen

Cuando se utilizan JOIN se deben tomar en cuenta algunas consideraciones:

- Especificar las condiciones del JOIN en base a Primary Key (PK) y a Foreign Key (FK)- Si una tabla tiene una PK compuesta, se debe referenciar a la clave entera en la cláusula ON del JOIN- Las tablas comunes a las tablas deben ser del mismo tipo de dato- La cláusula JOIN permite recuperar columnas de tablas relacionadas- Los operadores JOIN pueden combinar más de una tabla. - JOIN puede incluir INNER, LEFT OUTER, RIGHT OUTER y FULL OUTER- Una tabla puede ser combinada consigo misma definiendo diferentes alias