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:

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:

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:

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