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:

  • 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;

1_SQL_Server_How_to_Merge_Data_with_JOINS_PART3

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:

2_SQL_Server_How_to_Merge_Data_with_JOINS_PART3

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:

3_SQL_Server_How_to_Merge_Data_with_JOINS_PART3

Query Designer:

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

4_SQL_Server_How_to_Merge_Data_with_JOINS_PART3

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:

5_SQL_Server_How_to_Merge_Data_with_JOINS_PART3

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

6_SQL_Server_How_to_Merge_Data_with_JOINS_PART3

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

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 →

Leave a Reply

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