SQL Server – How to Merge Data with JOINS? – PART 1

Hi Folks,

You might have used this many times. But still if you find any difficulties using Joins in SQL Server, then you don’t have to worry at all. I am going to give you some heads up:

Join is basically a multiplication of two data sets followed by a restriction of the result so that only the intersection of the two data sets is returned.

The main function of Join is to horizontally merge two data sets and produce a new result set from the combination by matching rows in one data source to rows in the another data source.

The various types of Joins are as follows:

  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self-Join
  • Cross Join

We will deal with each one of them briefly. So let’s start with the Inner Join:

Inner Join:

  • This is also known as Common Join, as well as Natural Join.
  • It returns only those rows that represent the match between the two data sets.
  • It extracts only data from the inner portion of the intersection of the two overlapping data sets.
  • The order of the data sources is not important.
  • It’s better to avoid ANSI SQL 89 styles and to use ANSI SQL 92 which is much clean, easy to read, and easy to debug, which enhances data integrity and decreases maintenance costs.

Creating Inner Joins-

While working with the T-SQL code, joins are specified within the FROM clause of the SELECT statement. The keyword JOIN identifies the second table, while the ON clause is the common base between the two tables. The default type of join is inner join, so keyword INNER is optional; bit it’s good to include this for clarity.

Before creating inner joins, I would like you to see the two tables individually;

  • Table : Students1

1_SQL_Server_How_to_Merge_Data_with_JOINS_PART1

  • Table : Students2

2_SQL_Server_How_to_Merge_Data_with_JOINS_PART1

Now as you can see the common attribute between the two tables here is SID, Name and City. Since SID has many values in common and also it is the primary key, so we will opt for it in the ON clause. And here’s the query goes for this-

USE TEST
SELECT Students1.SID, Students1.Name,Students1.City, Students2.SID, Students2.Name, Students2.City 
FROM dbo.Students1
INNER JOIN dbo.Students2
ON Students1.SID=Students2.SID;

The combination can be displayed as in the result set as follows:

3_SQL_Server_How_to_Merge_Data_with_JOINS_PART1

As you can observer that Students1 has 4 attributes while Students2 has 3, but as early said in the definition itself that inner join gives the result for the common attributes having values in common. So that was reason for the elimination of 4th row from the result set.

Query Designer:

It becomes easy for the user to construct inner joins using the Query Designer UI in the Management Studio itself. As soon as both the tables are being placed in the Diagram pane, then either using the Add Table function or by dragging the tables from the table list, the joins automatically creates the required common joins based on common fields.

Any of the unwanted joins can be easily removed by selecting the join and pressing Delete. To create a new join between the two tables, drag the join column from the first table to the second table. The type of join can be changed by right-clicking on the join type symbol.

The Query Designer uses different symbols for each type of join. ‘Join diamond’ is a symbol of inner join.

So you can see the Query Designer Windows for the same query written in the above examples:

4_SQL_Server_How_to_Merge_Data_with_JOINS_PART1

Well this was all about Inner Join for this post. Hope you liked it 🙂

In the next aritcle post I would like to deal with Left, Right and Full Outer Joins.

So keep tuned!

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

3 Comments on “SQL Server – How to Merge Data with JOINS? – PART 1”

  1. Hi Kiran

    Well thanx for asking me this question, which has forced me to post blog on this.

    So i have released “SQL Server – Building CrossTab Queries – PART 1 “, which deals with a PIVOT method which could solve your problem and i am also looking forward to release its PART 2 and PART 3, which will also discuss several other methods for the transformation of rows to column.

    So check this link:

    https://www.sqlservergeeks.com/blogs/piyush.bajaj.2007/sql-server-bi/171/sql-server-building-crosstab-queries-%E2%80%93-part-1

  2. Clean Post!

    But it’s still the same content found all over the internet, I would like to see more content
    like what hppen if the ON clause columns are swaped, Can we have more than two inner join, limit to that.
    Can we have two column’s in ON clause, does NULL value results are shown etc

    I hope you can adorn this article more 🙂

Leave a Reply

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