MSSQL Join Statements

Join Statements in MS SQL Management Studio.

There are several different Join types in here I will discuss them:
Inner Join Statement
Left Outer Join Statement
Right Outer Join Statement
Self Join Statement

Joining tables is one of the most useful operations we have. Large data should be divided to several tables (see Normalization Rules) for maximum flexibility and minimum resources usage.

Inner Join Statement:

Inner Join is meant for combining rows between two tables, based on at least one column with the same data.
Although columns with the same data are the most common way to use the Inner Join, there is also a possibility to conduct it with "greater then", "not equal" etc.

Example:
    SELECT TOP 10 P.ProductID,

        P.Name,

        P.ListPrice,

        S.UnitPrice,

        S.OrderQty

    FROM Sales.SalesOrderDetail S

    INNER JOIN Production.Product P

        ON S.ProductID = P.ProductID

This is an example about a simple join which takes columns from two tables and combine it into one table when the mutual column is ProductID (on both tables).

Left Outer Join Statement:

In the Left Outer Join statement, all data is returned from the left table while on the right table, the matching data is returned as addition or as NULL where the record exists in the left table, but not in the right one.

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
    LEFT OUTER JOIN Sales.SalesTerritory ST
        ON ST.TerritoryID = SP.TerritoryID
    ORDER BY ST.TerritoryID, C.LastName

 This is an example about a little complex join.  This one involves both the Inner Join (which we saw earlier) and the Left Outer Join.
The syntax "Left Join" is also correct.
Also, I added an "Order By" command.

Right Outer Join Statement:

In the Right Outer Join statement, all data is returned from the right table while on the left table, the matching data is returned as addition or as NULL where the record exists in the left table, but not in the right one.
Basically, exactly the opposite of Left Outer Join.

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
    RIGHT OUTER JOIN Sales.SalesTerritory ST
        ON ST.TerritoryID = SP.TerritoryID
    ORDER BY ST.TerritoryID, C.LastName

In this example I made the same Select as in Left Outer Join, but with the Right Outer Join. The point is for you to see the result for your self and be able to understand the exact difference between the two.

Self Join Statement:

In the Self Join statement, the same table is specified and "used" twice with two different aliases in order to match the data within the same table.

Example:
    SELECT M.ManagerID AS 'ManagerID',
        M1.ContactID AS 'ManagerContactID',
        M1.FirstName AS 'ManagerFirstName',
        M.Title AS 'ManagerTitle',
        E.EmployeeID AS 'EmployeeID',
        E1.ContactID AS 'ContactID',
        E1.FirstName AS 'EmployeeFirstName',
        E.Title AS 'EmployeeTitle'
    FROM HumanResources.Employee E
    INNER JOIN HumanResources.Employee M
        ON E.ManagerID = M.ManagerID
    INNER JOIN Person.Contact E1
        ON E1.ContactID = E.ContactID
    INNER JOIN Person.Contact M1
        ON M1.ContactID = M.ContactID
    ORDER BY M1.LastName

Good luck,
Elad Shalom,
CTO at ITweetLive.com