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

Tagged as

Go to top

MSSQL Join Statements

, 8 Jun 2011
Rate this:
Please Sign up or sign in to vote.
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.

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 than", "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 combines 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 yourself 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,
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)

Share

About the Author

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

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140921.1 | Last Updated 8 Jun 2011
Article Copyright 2011 by DevAffair
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid