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

• Table : Students2

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:

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 4^th 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:

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