MSSQL Advanced Join Statements
There are several different Join
types which I will discuss here:
Joining tables is one of the most useful operations we have. Large data should be divided into several tables (see Normalization Rules) for maximum flexibility and minimum resources usage.
Cross Join Statement
In the Cross Join
statement, based on the two tables within the Join
, a Cartesian product is created if a 'Where
' statement filters the rows. The size of the Cartesian product is based on multiplying the number of rows from the left table by the number of rows from the right one.
Be careful when using the Cross Join. It might cause more damage than good.
Example:
SELECT TOP 100 P.ProductID,
P.Name,
P.ListPrice,
P.Size,
SOD.UnitPrice,
SOD.UnitPriceDiscount,
SOD.LineTotal
FROM Sales.SalesOrderDetail SOD
CROSS JOIN Production.Product P
WHERE SOD.UnitPrice > 3500
ORDER BY SOD.UnitPrice DESC
Full Outer Join Statement
As you remember, the Left Join
and Right Join
statements were basically the same Select
with different Join
statements. In this example, the result is the same as the Left Join
, with mild changes.
Example:
SELECT C.ContactID,
C.FirstName,
C.LastName,
SP.SalesPersonID,
SP.Bonus,
ST.TerritoryID,
ST.Name
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
Good luck.
Elad,
Lead Developer at Sports Betting Tech
发表评论
YYLBhq I really liked your blog post.Really looking forward to read more. Really Great.