How to make joins in SQL Server

In database joins are used to retrieve data from two or more table based on logical relationship between these tables. A typical join specifies a forgien key from one table and its associated primary key in the other table. To do this, it specifies the following,

• The column from each table to be used for the join
• A logical operator like =, or <>, used to compare values from the column

There are three types of joins,

1. Inner Join
2. Outer Join
3. Cross Join

We are going to use sample database Adenture Works. You can download sample database from the below link,

http://msftdbprodsamples.codeplex.com/releases/view/55926

1. Inner Join
   Inner joins combine the records from two tables, and add them to a query’s result only if the rows from both the tables match a common column. When you include the join keywords in a query, without mentioning the type, by default SQL Server assumes the join to be an inner join.
   In the example we are using AdventuresWorks database tables Product and ProductSubcategory. In Products table column ProductSubcategoryID is used as forigen key from ProductSubcategory table. To create an inner join we will use the below query,

   SELECT Production.Product.ProductID, Production.Product.Name,
   Production.Product.ProductNumber, Production.ProductSubcategory.Name AS SubCategoryName
   FROM   Production.Product INNER JOIN Production.ProductSubcategory 
   ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID

   An inner join returns rows only when at least one row from both the tables matches the join condition.

2. Outer Join
   There are three types of outer joins,
   1. Left Outer Join
      Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple.
      In the example below we are using Product and ProductSubcategory tables, and create Left Outer join on these tables. So it will show all the products irrespective of their subcategory exists or not.

      SELECT Production.Product.ProductID, Production.Product.ProductNumber, Production.Product.Name, Production.ProductSubcategory.ProductCategoryID,
      Production.ProductSubcategory.Name AS SubCategory
      FROM Production.Product LEFT OUTER JOIN
      Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID

   2. Right Outer Join
      The Right Outer Join is similar to the Left Outer Join, except that it returns all the records from the right table, and only the matching records from the left table.
      In the example below we join two tables CustomerAddress and AddressType. The below query result will show all the data from AddressType. As the table contains huge rows so we restrict use the keyword DISTINCT and TOP to restrict the data to 10 rows.

      SELECT DISTINCT TOP (10) Person.AddressType.AddressTypeID, Person.AddressType.Name, Sales.CustomerAddress.CustomerID, Sales.CustomerAddress.AddressID
      FROM Sales.CustomerAddress RIGHT OUTER JOIN Person.AddressType ON Sales.CustomerAddress.AddressTypeID = Person.AddressType.AddressTypeID

   3. Full Join
      The Full Outer Join lists all the records from both tables, regardless of whether there are matching records in the tables or not.

      SELECT DISTINCT TOP (10) Production.Product.ProductID, Production.Product.Name,
      Production.ProductSubcategory.ProductCategoryID, Production.ProductSubcategory.Name AS SubCat
      FROM Production.Product FULL OUTER JOIN
      Production.ProductSubcategory ON
      Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID

3. Self Join
   Sometimes you may need to find records in one table that are related to other records in the same table. In such cases use a Self Join which is a type of Inner Join. Self Join correlates the rows of a table with other rows in the same table.