Monday, February 14, 2011

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.

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

10 comments:

  1. Not bad... Some more articles of SQL here would be nice

    ReplyDelete
  2. What if you don't have or don't know what C.this and P.that is? What if you just have table names and column names and you just want to write an SQL real world example for any join type using unidetified undocumented C. P. and whatever else you arbitrarily put into your queries? How would THAT look as an example?

    RJB

    ReplyDelete
    Replies
    1. Hey RJB.
      I'm not sure if I know what you mean by C.this and P.that..
      If you mean the column names, Sql Server 2000 and above auto completes your queries with the right column name.

      A simple join I just used is:
      Select count(*) from Users U
      Inner join Accounts A
      on U.Id = A.UserId
      Where U.Id = @UserId
      Which will give me amount of accounts that belong to the same user.

      Delete
  3. Extremely useful and clean illustrate over Join statement...i arrived on your post due to my hobby is always try to collect new info on SQL from anywhere...Thanks

    ----------------
    IT Consulting Los Angeles

    ReplyDelete
  4. Precise! Good work!

    ReplyDelete
  5. Ι ԁon't even know how I ended up here, but I thought this post was good. I don't κnoω ωho you arе
    but сeгtainly you're going to a famous blogger if you aren't already
    ;) Cheeгs!

    Hеrе is my web ѕite how to buy and sell cars for profit at an auction

    ReplyDelete
  6. Ηello mates, its great paragraрh rеgaгding tеachіngand completely explainеd, keep
    іt up аll the time.

    my web-site ... ft worth seo company

    ReplyDelete
  7. First of all, some examples for the impatient. In MySQL, a join condition can be specified in two different ways. Take a look at the following statements:

    So also one live sample you can implement in to create application

    SELECT * FROM firsttable a INNER JOIN anothertable b USING(columnname)

    ________________________________
    Famtex.pl

    ReplyDelete