Module 3: Self Join Explained

Previous Tutorial - Next Tutorial

A Self join is a join in which a table is joined with itself. It is not a different form of join, rather it is an inner join or outer join of a table to itself. The Self join can be viewed as a merging of two copies of the same table. This is the general syntax of the Self join. The syntax of the command for joining a table to itself is almost the same as that for joining two different tables.

In the example, we have used the inner join, but you can use the outer join also as per your requirement. Now to distinguish the column names from one another aliases for the actual table name are used since both the tables have the same name. The table name aliases are defined in the FROM clause of the SELECT statement. Here, table aliasing is mandatory and if you don't assign different aliases to the two instances of the same table, then you end up with an invalid result because we are referencing the same table name in the join. Now let's open the SQL server management studio to see this Self join in the action.

In the demo, I'm going to use the person table. My requirement is I want to list out those people whose first name and last name are the same and the primary key value, which is the business entity ID column, is not the same. Before we join the table, in the self join, I must have to use an alias for the table as I need to join the same table. What will happen if I do not specify the table alias? In this case, I need to write a query (INNER JOIN Person.Person ON Person.BusinessEntityID = person.BusinessEntityID)

Let's execute this query, as soon as I started joining this same table here, then SQL server management studio started giving me the error here, and still when I execute this query, then I'm getting this error. The object person in person in the FROM clause has exposed the same name. So to overcome this error, either I have to use the correlation names or alias to distinguish them. Now define the alias for each table.

SQL Self Joins

For the first table (FROM Person.Person AS T1) and for the second (INNER JOIN Person.Person AS T2 ON Person.BusinessEntityID = person.BusinessEntityID), now use the alias. Same alias I also have to use in the ON clause. I want to find out those people whose first name and last name are the same in this business entity ID column is not the same.

I need to write (INNER JOIN Person.Person AS T2 ON T1.BusinessEntityID <> T2.BusinessEntityID AND T1.FirstName = T2.FirstName AND T1.LastName = T2.LastName) and specify the column name from the second table. Let's execute this query. This time it's giving me the result whose first name and last name are the same in the table, but the business entity ID column is not the same.

Here note that we have used multiple columns in the join clause, sometimes you would need to use more than one joining column to support specific business rules and specific business requirements. The logic of a join expression is very similar to that of a WHERE clause multiple companies and can be combined using AND/OR operators. Here also note that I have used the not equal to operator for the business entity ID column. The same way you can use other comparison operators such as less than operator, greater than operator, less than or equal to operator, etc.

Now for learning purposes, we have used the two tables in every join, but in real life scenario, you may require to use multiple tables with different types of join in a single query. Let's put it all together. This time I need to list off all individual customers with their email and the details of their home or office address, which is resided at different tables.

Open a new query window, here I have listed out some of the queries to fulfill my requirement and I want to list out all the personal information of the individual customer. This customer table contains two types of data, vendor data, and individual customer data. If I filter this record with StoreId IS NULL then this query will give me the individual customer list and the name of the customer.

Self Joins

Some other detail is residing in the person table like first name, middle name, last name, etc. The email address of this customer table resides in this email address table. We will join this email address table on this Business Entity ID column. It may be possible that some of the customers may not have the email address. We will use the left outer join in this case.

Now, the address of this customer resides in this address table. It might be possible that some of the customers have multiple addresses like office address, residential address, etc. They have used the business entity address. This table is also called Junction Table. We join two tables, the address table with the address ID column and the person table with the business entity ID column.

Let's write the query to achieve our requirements. First, copy the query then we will join the person table on the personal business entity ID column after then we will join the email address column. Some of the customers may not have an email address, but we will use left outer join in this case. We will join this email address table on the Business entity ID column, then to get the address, we will use this business entity address and address table for that we will use Inner joint.

Self Join

Now define the individual column name instead of star (SELECT person.BusinessEntityID, person.FirstName, person.LastName, EmailAddress, AddressLine1, AddressLine2, City, PostalCode). Execute this query and this query is returning me the personal detail of the individual customer.

That's it from the types of join part in SQL Server. In the next slide, we will learn about the different types of SET operators, such as the UNION, UNION ALL, INTERSECT, and EXCEPT. We learn what is the difference between each operator and how they work.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials: