Click here to Skip to main content
Click here to Skip to main content

MSSQL Advanced Join Statements

, 8 Jun 2011
Rate this:
Please Sign up or sign in to vote.
Advanced Join Statements in Microsoft SQL Management Studio

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

DevAffair
Team Leader Sports Betting Tech
Israel Israel
No Biography provided
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 2 Pinmemberoggenok12-Jun-11 4:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 8 Jun 2011
Article Copyright 2011 by DevAffair
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid