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;

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:

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:

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:

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