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

Hi folks,

You would have seen my previous article post on Inner Join, if you still not seen, then you can refer to that post through a link;

But this article is being intended basically for the Outer Join, so if you have any difficulties in your mind just don’t keep it by yourself, explore it!

Outer Join:

• Outer Joins extends the inner join by adding the non-matching data from the left, or right, or from both side data sets.
• Outer Joins solves a significant problem for many queries by including all the data regardless of a match.
• This not only includes from the two data sources with a match, but also unmatched rows from outside the intersection.
• So there are three types of Joins: Left Outer Join, Right Outer Join and Full Outer Join. Will discuss each of them separately:

Before going on to discuss Left, Right and full, it would be nice if you see the table’s that we are using in the examples below:

Left Outer Join:

• Left Outer Join may return extra rows from the first (left) table that do not satisfy the join condition. In those extra rows, columns from the second (right) table will be given null values.
• This will be more clear if you see an example:

USE TEST
SELECT * FROM dbo.Students1
LEFT OUTER JOIN dbo.Students2
ON Students1.SID=Students2.SID;

In this query, there are 4 rows in the first table(Students1) while there are 3 rows in the second table(Students2), so as per the definition of Left Outer Join, the Left Table which has 4 rows will be included and will add that extra row to the Right table with a value as null, in the result set. So you can see the results as:

You can also view the relations and can edit it through a query designer window:

Right Outer Join:

• Right Outer Join may return extra rows from the second (right) table that does not satisfy the join condition. In those extra rows, columns from the first (left) table will be given null values.
• There might also be the possibility that, if there are more rows in the left table than on the right table, then in that case the extra row(s) from the left table will automatically be removed in the result set.
• This will be more clear if you see an example:

USE TEST
SELECT * FROM dbo.Students1
RIGHT OUTER JOIN dbo.Students2
ON Students1.SID=Students2.SID;

In this query, there are 4 rows in the first table(Students1) while there are 3 rows in the second table(Students2), so as per the definition of Right Outer Join, the Right Table which has 3 rows will be included and that left table which has 4 rows, the row that will not satisfy the condition will be removed. So you can see the results as:

You can also view the relations and can edit it through a query designer window:

Full Outer Join:

• Full Outer Join returns all the data from both data sets regardless of the intersection.
• It includes all the rows from both the tables, regardless of whether or not the other table has a matching value.
• You can also say it like that, taking the results from a left outer join and the results from a right outer join, and Union them  together.
• This will be more clear if you see an example:

USE TEST
SELECT * FROM dbo.Students1
FULL OUTER JOIN dbo.Students2
ON Students1.SID=Students2.SID;

In this query, there are 4 rows in the first table (Students1) while there are 3 rows in the second table (Students2), so as per the definition of Full Outer Join, the result set will also include the extra row that is present from the Students1 table and will also include the row from the Students2 table with the Null values. So you can see the results as:

You can also view the relations and can edit it through a query designer window:

This was all about Outer Joins, hope you like it 🙂

In the next article post I would like you tell about Self-join and Exotic 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