Permite realizar consultas entre varias tablas relacionadas (JOIN).
La base de datos usada para los ejemplos es AdventureWorks2014 de Microsoft
JOIN (INNER JOIN):
Permite combinar dos tablas, se llama combinación interna y solo devuelve aquellas filas coincidentes en ambas tablas.
Las condiciones de las relaciones de las tablas se pueden establecer en el FROM o en el WHERE, se recomienda establecer en el bloque del FROM. (para evitar productos cartesianos involuntarios).
Los valores NULL se omiten y no forman parte del resultado.
Ejemplo:
Relacionamos el Employee ( 290 rows) con sus EmployeePayHistory (316)
|
1 2 3 4 |
SELECT he.NationalIDNumber, he.JobTitle,hp.ModifiedDate, hp.Rate FROM HumanResources.Employee he JOIN HumanResources.EmployeePayHistory hp ON he.BusinessEntityID=hp.BusinessEntityID |
El resultado son 316 líneas con los datos del Employee y sus EmployeePayHistory
Ejemplo (con Null)
Vamos a relacionar el Employee ( 290 rows) con los JobCandidate (13 rows)
|
1 2 3 |
SELECT he.NationalIDNumber, he.JobTitle, hj.JobCandidateID FROM HumanResources.Employee he JOIN HumanResources.JobCandidate hj ON he.BusinessEntityID=hj.BusinessEntityID |
Resultado: Obtenemos 2 filas debido a que en la tabla JobCandidate el BussinessEntityID es NULL excepto en dos casos y el JOIN omite los Nulls
OUTER JOIN (LEFT JOIN, RIGHT JOIN):
Relacionando mediante OUTER JOIN devuelve todas las filas de una de las tablas, la tabla será según la relación que hagamos LEFT O RIGHT
Ejemplo LEFT JOIN:
|
1 2 3 4 5 |
SELECT he.NationalIDNumber, he.JobTitle, hj.JobCandidateID FROM HumanResources.Employee he LEFT JOIN HumanResources.JobCandidate hj ON he.BusinessEntityID=hj.BusinessEntityID ORDER BY JobCandidateID DESC |
Resultado: Devuelve todos los Employee (290 rows) y el valor de JobCandidateID si tiene relación, sino un NULL
Ejemplo RIGHT JOIN:
|
1 2 3 |
SELECT he.NationalIDNumber, he.JobTitle, hj.JobCandidateID FROM HumanResources.Employee he RIGHT JOIN HumanResources.JobCandidate hj ON he.BusinessEntityID=hj.BusinessEntityID |
Resultado: Devuelve todas las filas de la tabla JobCandidate (13 rows) y los valores de Employee que coincide en la relación sino NULL
FULL JOIN:
Obtiene los resultados de ambas tablas ya cumplan la condición de la relación o no.
|
1 2 3 |
SELECT he.NationalIDNumber, he.JobTitle, hj.JobCandidateID FROM HumanResources.Employee he FULL JOIN HumanResources.JobCandidate hj ON he.BusinessEntityID=hj.BusinessEntityID |
Resultado: Obtenemos las 290 rows de clientes y las 13 rows de JobCandicate, como están relacionadas 2 rows, nos devuelve 301 rows,
SELF JOIN:
Relaciona la misma tabla, no es muy común su uso, en este caso la consulta por cada empleado de Marketing le obtiene el responsable, es necesario establecer alias en las tablas para diferenciar los campos.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT TOP 1000 E.[BusinessEntityID] ,E.[NationalIDNumber] ,E.[OrganizationNode] ,E.[OrganizationLevel] ,E.[JobTitle] ,E2.NationalIDNumber ,E2.JobTitle FROM [AdventureWorks2014].[HumanResources].[Employee] as E INNER JOIN [AdventureWorks2014].[HumanResources].[Employee] as E2 ON E2.OrganizationLevel=E.OrganizationLevel-1 WHERE E.JobTitle LIKE 'Marketing%' AND E.OrganizationLevel=2 AND E2.JobTitle like 'Marketing%' |
Resultado:
CROSS JOIN:
Combina cada fila de la primera tabla con la segunda tabla. Son productos cartesianos.
Ejemplo:
Por cada empleado de Production Technician – WC1 (17 rows) hago un CROSS JOIN con los posibles franjas horarias (3 rows).
|
1 2 3 4 5 6 7 |
SELECT S.StartTime,S.EndTime, [BusinessEntityID] ,[NationalIDNumber] ,[JobTitle] ,[Gender] FROM [AdventureWorks2014].[HumanResources].[Employee] AS E CROSS JOIN HumanResources.Shift as S WHERE E.JobTitle='Production Technician - WC10' |
Resultado: son 51 filas=17 empleados x 3 franjas horarias
Se podría usar por ejemplo en una empresa de servicios para unir un cliente con todos los servicios y luego ver que servicios a usado y cuales no.
Espero que os sea útil.