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;
- 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:
The design for the query can be seen, there relations and can be modified here:
- 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!!
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