Las Preguntas:

Soy nuevo en SQL Server y quiero aprender sobre las distintas opciones para el uso del JOIN. ¿Cuáles son todas estas opciones de JOIN en SQL Server? ¿Cuál es el funcionamiento de cada una de ellas? Estoy un poco confundido sobre las diferencias y la sintaxis, ¿puede proporcionar algunos ejemplos y explicaciones? ¿Los JOIN son solo para declaraciones SELECT? A continuación respondemos estas preguntas que nos hacen habitualmente nuestros alumnos sobre el uso de los JOIN en Transact-SQL.

Las Respuestas:

Unir tablas para obtener los datos necesarios para una consulta, script o procedimiento almacenado es un concepto clave a medida que dr aprende sobre el desarrollo en SQL Server. En pocas palabras, las uniones se realizan normalmente en la cláusula FROM de una tabla o vista para las sentencias SELECT , INSERT … SELECT , SELECT … INTO , UPDATE y DELETE . En versiones anteriores de SQL Server, la lógica de unión también podría haberse incluido en la cláusula WHERE con la sintaxis = (INNER JOIN), * = (LEFT OUTER JOIN), = * (RIGHT OUTER JOIN), etc., pero el soporte ha sido reducido y la mejor práctica en SQL Server es utilizar la sintaxis descrita en los ejemplos a continuación.

Inicia 2 de Mayo

Antes de saltar al código, proporcionemos información básica sobre las opciones de los JOIN en SQL Server:

• INNER JOIN: permite coincidir las filas entre las dos tablas especificadas en la instrucción INNER JOIN en función de una o más columnas que tengan datos coincidentes. Preferiblemente, la unión se basa en la integridad referencial que impone la relación entre las tablas para garantizar la integridad de los datos.
  • Solo para agregar un pequeño comentario a las definiciones básicas anteriores, en general, la opción INNER JOIN se considera la combinación más común necesaria en aplicaciones y / o consultas. Aunque ese es el caso en algunos entornos, realmente depende del diseño de la base de datos, la integridad referencial y los datos necesarios para la aplicación. Como tal, tómese el tiempo para comprender los datos que se solicitan y luego seleccione la opción de unión adecuada.
  • Aunque la mayoría de la lógica de unión se basa en valores coincidentes entre las dos columnas especificadas, también es posible incluir lógica utilizando mayor que, menor que, no igual, etc.
• LEFT OUTER JOIN: según las dos tablas especificadas en la cláusula de unión, todos los datos se devuelven desde la tabla de la izquierda. En la tabla de la derecha, los datos coincidentes se devuelven además de los valores NULL cuando existe un registro en la tabla de la izquierda, pero no en la tabla de la derecha.
  • Otro elemento a tener en cuenta es que la lógica LEFT y RIGHT OUTER JOIN es opuesta entre sí. Por lo tanto, puede cambiar el orden de las tablas en la declaración de combinación específica o cambiar el JOIN de izquierda a derecha o viceversa y obtener los mismos resultados.
• RIGHT OUTER JOIN: según las dos tablas especificadas en la cláusula de unión, todos los datos se devuelven desde la tabla de la derecha. En la tabla de la izquierda, los datos coincidentes se devuelven además de los valores NULL cuando existe un registro en la tabla de la derecha pero no en la tabla de la izquierda.
• SELF JOIN: en esta circunstancia, la misma tabla se especifica dos veces con dos alias diferentes para hacer coincidir los datos dentro de la misma tabla.
• CROSS JOIN: Basado en las dos tablas especificadas en la cláusula de unión, se crea un producto cartesiano si una cláusula WHERE filtra las filas. El tamaño del producto cartesiano se basa en multiplicar el número de filas de la tabla de la izquierda por el número de filas de la tabla de la derecha.
• FULL JOIN: según las dos tablas especificadas en la cláusula de unión, todos los datos se devuelven de ambas tablas independientemente de los datos coincidentes.

Repasemos ejemplos de la base de datos de muestra AdventureWorks que está disponible en SQL Server para proporcionar ejemplos de cada tipo de JOIN y luego proporcionemos información sobre el uso y los conjuntos de resultados de muestra.

Inicia 2 de Mayo

Ejemplo de INNER JOIN en SQL Server

En este ejemplo, estamos uniendo las tablas Sales.SalesOrderDetail y Production.Product. Las tablas tienen el alias siguiente: SOD para Sales.SalesOrderDetail y P para Production.Product. La lógica JOIN se basa en registros coincidentes en las columnas SOD.ProductID y P.ProductID. Los registros se filtran devolviendo únicamente los registros con el SOD.UnitPrice mayor que 1000. Finalmente, el conjunto de resultados se devuelve en orden con el más caro primero según la cláusula ORDER BY y sólo los 100 productos más altos según la cláusula TOP.

SELECT  TOP 100 P.ProductID, 
 P.Name, 
 P.ListPrice, 
 P.Size, 
 P.ModifiedDate, 
 SOD.UnitPrice, 
 SOD.UnitPriceDiscount,
 SOD.OrderQty,
 SOD.LineTotal 
FROM Sales.SalesOrderDetail SOD 
INNER JOIN Production.Product P 
 ON SOD.ProductID = P.ProductID 
WHERE SOD.UnitPrice > 1000 
ORDER BY SOD.UnitPrice DESC

SELECT  C.ContactID,
 C.FirstName,
 C.LastName,
 SP.SalesPersonID,
 SP.CommissionPct,
 SP.SalesYTD,
 SP.SalesLastYear,
 SP.Bonus,
 ST.TerritoryID,
 ST.Name,
 ST.[Group],
 ST.SalesYTD
FROM Person.Contact C
INNER JOIN Sales.SalesPerson SP
 ON C.ContactID = SP.SalesPersonID
LEFT OUTER JOIN Sales.SalesTerritory ST 
 ON ST.TerritoryID = SP.TerritoryID
ORDER BY ST.TerritoryID, C.LastName

Ejemplo de RIGHT OUTER JOIN en SQL Server

En un esfuerzo por explicar cómo RIGHT OUTER JOIN y LEFT OUTER JOIN son lógicamente recíprocos entre sí, el siguiente código es una versión reescrita de LEFT OUTER JOIN anterior. Como puede ver, el orden de JOIN y las tablas son diferentes, pero el conjunto de resultados final coincide con la lógica LEFT OUTER JOIN. En el código de muestra a continuación, estamos recuperando los datos coincidentes entre las tablas Person.Contact y Sales.SalesPerson junto con todos los datos de la tabla Sales.SalesPerson y los datos coincidentes en la tabla Sales.SalesTerritory. Para los registros que existen en la tabla Sales.SalesPerson y no en la tabla Sales.SalesTerritory, se devuelven valores NULL para las columnas en Sales.SalesTerritory.

SELECT  C.ContactID, 
 C.FirstName, 
 C.LastName, 
 SP.SalesPersonID,
 SP.CommissionPct,
 SP.SalesYTD,
 SP.SalesLastYear,
 SP.Bonus,
 ST.TerritoryID,
 ST.Name, ST.[Group],
 ST.SalesYTD 
FROM Sales.SalesTerritory ST 
RIGHT OUTER JOIN Sales.SalesPerson SP 
 ON ST.TerritoryID = SP.TerritoryID 
INNER JOIN Person.Contact C 
 ON C.ContactID = SP.SalesPersonID 
ORDER BY ST.TerritoryID, C.LastName

Inicia 2 de Mayo

Ejemplo de SELF JOIN en SQL Server

En este ejemplo, en realidad nos estamos uniendo a la tabla HumanResources.Employee. Estamos haciendo esto para obtener la información sobre la relación Empleado y Gerente en la tabla Recursos humanos.Empleado. Junto con esa lógica de JOIN, también nos estamos uniendo a Person.Contact dos veces para capturar los datos del nombre y el cargo basados ​​en las relaciones originales de Empleado y Gerente. Además, otro concepto nuevo introducido en esta consulta es el alias de cada una de las columnas. Aunque podríamos haberlo hecho en los ejemplos anteriores, nos propusimos hacerlo en esta consulta para diferenciar entre los datos relacionados con Empleado y Gerente.

SELECT  M.ManagerID AS 'ManagerID',
 M1.ContactID AS 'ManagerContactID',
 M1.FirstName AS 'ManagerFirstName',
 M1.LastName AS 'ManagerLastName',
 M.Title AS 'ManagerTitle',
 E.EmployeeID AS 'EmployeeID',
 E1.ContactID AS 'EmployeeContactID',
 E1.FirstName AS 'EmployeeFirstName',
 E1.LastName AS 'EmployeeLastName',
 E.Title AS 'EmployeeTitle'
FROM HumanResources.Employee E 
INNER JOIN HumanResources.Employee M 
 ON E.ManagerID = M.EmployeeID 
INNER JOIN Person.Contact E1 
 ON E1.ContactID = E.ContactID 
INNER JOIN Person.Contact M1 
 ON M1.ContactID = M.ContactID
ORDER BY M1.LastName

Ejemplo CROSS JOIN en SQL Server

Como se indicó anteriormente, tenga cuidado al ejecutar o modificar esta consulta en cualquier entorno de base de datos de SQL Server. El conjunto de resultados está limitado intencionalmente por la cláusula TOP 100 y la cláusula WHERE para evitar un producto cartesiano, que es el resultado de cada una de las filas de la tabla de la izquierda multiplicado por el número de filas de la tabla de la derecha.

SELECT  TOP 100 P.ProductID, 
 P.Name, 
 P.ListPrice, 
 P.Size, 
 P.ModifiedDate, 
 SOD.UnitPrice, 
 SOD.UnitPriceDiscount,
 SOD.OrderQty,
 SOD.LineTotal 
FROM Sales.SalesOrderDetail SOD 
CROSS JOIN Production.Product P 
WHERE SOD.UnitPrice > 3500 
ORDER BY SOD.UnitPrice DESC

Ejemplo FULL OUTER JOIN en SQL Server

En nuestro último ejemplo, modificamos la lógica del ejemplo LEFT OUTER JOIN anterior y convertimos la sintaxis LEFT OUTER JOIN en FULL OUTER JOIN. En esta circunstancia, el conjunto de resultados es el mismo que LEFT OUTER JOIN donde devolvemos todos los datos entre ambas tablas y los datos no disponibles en Sales.SalesTerritory se devuelven como NULL.

SELECT  C.ContactID,
 C.FirstName,
 C.LastName,
 SP.SalesPersonID,
 SP.CommissionPct,
 SP.SalesYTD,
 SP.SalesLastYear,
 SP.Bonus,
 ST.TerritoryID,
 ST.Name,
 ST.[Group],
 ST.SalesYTD
FROM Person.Contact C
INNER JOIN Sales.SalesPerson SP
 ON C.ContactID = SP.SalesPersonID
FULL OUTER JOIN Sales.SalesTerritory ST 
 ON ST.TerritoryID = SP.TerritoryID
ORDER BY ST.TerritoryID, C.LastName

Fuente: mssqltips.com

Inicia 2 de Mayo