- 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
- 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;