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,

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

Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *

*