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

Hello folks,

You must have seen my previous article post on Inner Join and Outer Join; if not then you can just check out this link;

This article post is all about Self Join and Cross Join;

Self-Joins:

• A Self-Join is a join that refers to the same table. Use a self-join when you want to create a result set that joins records in a table with other records in the same table.
• So to list a table two times in the same query, you must provide a table alias for at least one of instance of the table name.
• This table alias helps the query processor determine whether columns should present data from the right or left version of the table.
• The key to constructing a self-join is to include a second reference to the table using a table alias. Once the table is available twice to the SELECT statement, the self-join functions much like any other join.
• This will be more clear if you see an example:

Let’s first see the table Persons;

Now, if we apply the Self-Join here:

SELECT C.P_Id, C.LastName, C.FirstName, C.Address, C.City, M.P_Id, C.M_Id
FROM Persons AS C
INNER JOIN Persons AS M
ON M.P_Id = C.M_Id

Here, you can see that we have used two aliases – ‘C’ and ‘M’. The aliases C and M is first being used to display the selected attributes and then INNER JOIN, M with C of the same table while satisfying that P_Id of M is equal to P_Id of C.

This will select only those rows from the table which satisfy the above condition.

The results of this combination are as follows:

And you want to be more specific at what your need is, then you can also put some condition with the help of WHERE clause. Like if I want to see details specifically, then we can put as:

SELECT C.P_Id, C.LastName, C.FirstName, C.Address, C.City, M.P_Id, C.M_Id
FROM Persons AS C
INNER JOIN Persons AS M
ON M.P_Id = C.M_Id
WHERE M.LastName = 'Tiwari'
AND M.FirstName = 'Prashant';

In this case, result is restricted for the condition given, will only show the first row in which the M.P_Id matches with the C.M_Id, i.e. P_Id for Prashant is 2 and so for the M_Id of Piyush. The result can be seen as:

Query Designer:

The design for the query can be seen, there relations and can be modified here:

Cross Joins:

• It is also known as Unrestricted Joins.
• The Cross Join is a pure relational algebra multiplication of the two source tables.
• It devoid of a WHERE clause produces the Cartesian product of the tables involved in the JOIN.
• The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
• That means if table A has 3 rows and table B has 2 rows, a CROSS JOIN will result in 6 rows.
• There is no such relationship’s established between the two tables – you literally just produce every possible combination.
• The Cross Join can be very useful, if you do lots of report writing in SQL, a CROSS JOIN can be your best friend.
• In code, this type of join is specified by the keywords CROSS JOIN and the lack of an ON condition.
• This will be clear after going through this example:

SELECT Students1.Name,Students1.City,Students2.Name,Students2.City
FROM Students1
CROSS JOIN Students2;

Since there is 4 rows in the Students1 table and 3 rows in the Students2, so the result will be consisting of 12 rows:

You can also see the query designer window; the two tables have no relation at all:

So, here my article post comes to an end of the sequel for “How to Merge Data with Joins”.

Hope you got it understood well 🙂

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