Overview

• By using joins, you can retrieve data from two or more tables based on logical relationships between the tables.
• Joins indicate how SQL Server should use data from one table to select the rows in another table.

CROSS JOIN

• It returns the cross product of the two tables, which is a list of all possible combinations of rows that are returned from them.

SELECT e.BusinessEntityID, d.Name AS [Department]
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d;

FULL [OUTER] JOIN

SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
FULL JOIN Sales.SalesOrderDetail AS sod
	ON p.ProductID = sod.ProductID

LEFT [OUTER] JOIN

SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
LEFT JOIN Sales.SalesOrderDetail AS sod
	ON p.ProductID = sod.ProductID

RIGHT [OUTER] JOIN

SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory AS st
RIGHT JOIN Sales.SalesPerson AS sp
	ON st.TerritoryID = sp.TerritoryID;

[INNER] JOIN

SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
JOIN Sales.SalesOrderDetail AS sod
	ON p.ProductID = sod.ProductID

APPLY

• It allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression.
  • The left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set.
  • The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.
• The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression.
  • In other words, the right table expression returns rows for left table expression match only.
• The OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression.
  • For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression.
• CROSS APPLY works better on queries that have complex JOIN condition.

SELECT [c].CustomerID, 
	AVG([y].OrderWeight) AS [AverageOrderWeight]
FROM Sales.Customer AS [c]
INNER JOIN Sales.SalesOrderHeader AS [soh]
	ON [soh].CustomerID = c.CustomerID
CROSS APPLY
(
	SELECT SUM([sod].[OrderQty] * [p].[Weight]) AS [OrderWeight]
	FROM Sales.SalesOrderDetail AS [sod]
	INNER JOIN Production.Product AS [p]
		ON [sod].ProductID = [p].ProductID
	WHERE [sod].SalesOrderID = [soh].SalesOrderID
	GROUP BY sod.SalesOrderID
) AS [y]
GROUP BY [c].CustomerID
ORDER BY [c].CustomerID;