There are several different Join types which I will discuss here:

• 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 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