SQL SELF JOIN: A Simple Way to JOIN Data in SQL

Smith, a trainee DBA at ABC Database Services, has a challenge before him — he has to refer to data inside a database table he has generated with several columns and rows. In addition to that, Smith has to compare values in a column with other values in the same column of the same table. 

But he doesn’t know how to handle this issue. He knows how to refer to information inside of a database, but how does he compare it to itself?

Why don’t we help him to solve this problem?

What is a SQL SELF JOIN?

Most SQL developers will be familiar with the concept of joining two tables to connect their data. But some may not be aware that it’s possible to join a table to itself. While it may seem a little circuitous, it’s actually the best way to compare and analyze a table’s data against its own data.

In Smith’s case, a SQL SELF JOIN query will be the most efficient solution to his problem. SQL SELF JOIN is essentially a normal join, but it’s a query used to join a table to itself. 

You can visualize it this way – a SELF JOIN for a given table is a join that performs between two identical copies of that table. The same table joins another copy of itself rather than merging with a different table. 

Imagine printing out two copies of a set of data in front of you — two copies of spreadsheets — so you can compare data more efficiently.

Why do we use SELF JOIN?

Why would you ever join a table to itself?

Some practical uses of SELF JOIN are to obtain running counts and totals, identify which data under a particular column satisfy a certain set of conditions, and generate another table by extracting data from the original table.  

Some applications of the SELF JOIN relationship are as follows, but of course, there can be many other possibilities:

• Obtaining statistical information on a particular item. For example, a count or total of all records in the table having a certain field value.
• Listing all records that have the same Match field value as the current record.
• Identifying the first record having each match field value or identifying duplicate records.
• Displaying the Match field value only once for each group of records having that value.

And there are alternatives to SELF JOIN, too. Rather than using SELF JOIN, a developer like Smith might instead choose to use analytic functions. But analytic functions tend to be a little more robust and difficult to navigate than a SELF JOIN, especially for a relatively simple query.

When it comes to multiple SELF JOIN queries, the queries themselves can become quite resource intensive. This is always a concern with any type of SQL JOIN. Because of this, developers may want to look deeper into analytic functions and other alternatives if they are going to be joining large sets of data with any regularity.

However, for smaller applications, one-off queries, and less resource-intensive databases, the SELF JOIN is usually the most straightforward option.

In addition to the SELF JOIN queries themselves, it’s possible that a database may need a redesign. This is usually the case when using multiple SELF JOIN and JOIN queries, especially if the queries are intensive. Reconsider the initial design of a database if the data needs extensive reformatting on a regular basis, as this is a sign that the prior format of the database no longer suits the current needs of the database.

Examples and Format of SELF JOIN

A self-join can be an inner join or an outer join. A table joins to itself based upon a field or combination of fields that have duplicate data in different records. The data type of the interrelated columns must be of the same type or need to cast them in the same type.

To write the query, select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself. You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table.

Example 1:

Let us take an example where we use SELF JOIN to match an employee’s name against the Supervisor or Manager’s name. The employee table is below:

emp_id	emp_name	emp_supv
4081	Nancy Brown	NULL
4083	Peter Parker	4081
4055	Jacob Miller	4083
4058	Mary Ray	4083
4060	Jane Smith	4081
4061	Bob Hunter	4081

In this table, the manager attribute simply references the employee ID of another employee in the same table. For example, Nancy Brown is the CEO of the company, and Peter Parker reports to Nancy Brown, while Jacob Miller and Mary Ray report to Peter Parker.

Here’s the SQL statement that will retrieve the desired results:

Syntax:

SELECT a.emp_id AS “Emp_ID”,a.emp_name AS “Employee Name”,

b.emp_id AS “Supervisor ID”,b.emp_name AS “Supervisor Name”

FROM employees AS a LEFT OUTER JOIN employees AS b

ON a.emp_supv = b.emp_id

As you can see, this SELF JOIN syntax is fairly complicated, but the major issue is that it’s difficult to read. It’s extremely important to take care when creating and relying upon SELF JOIN statements for this reason, as they are not always intuitive or easy to read, and they can be difficult to maintain within a code base.

The corresponding output table will look like:

Emp_ID	Employee Name	Supervisor ID	Supervisor Name
4081	Nancy Brown	NULL	NULL
4083	Peter Parker	4081	Nancy Brown
4055	Jacob Miller	4083	Peter Parker
4058	Mary Ray	4083	Peter Parker
4060	Jane Smith	4081	Nancy Brown
4061	Bob Hunter	4081	Nancy Brown

It’s important to select the correct join type while writing a SELF JOIN statement. In this case, we used a LEFT OUTER JOIN to ensure we had output records corresponding to each employee. If we used an INNER JOIN, then Nancy Brown, the CEO, would be omitted as she does not have a supervisor.

Using the incorrect type of JOIN is one of the most common pitfalls when it comes to a SQL SELF JOIN, and consequently, care must be taken when deciding whether an INNER JOIN or an OUTER JOIN is going to be used.

Example 2:

The following example performs a self-join of the Sales.SalesPerson table to produce a list of the salespeople with their matching territories.

Syntax:

SELECT st.Name AS TerritoryName, sp.BusinessEntityID,

sp.SalesQuota, sp.SalesYTD

FROM Sales.SalesPerson AS sp

JOIN Sales.SalesTerritory AS st

ON sp.TerritoryID = st.TerritoryID

ORDER BY st.Name, sp.BusinessEntityID

Example 3:

Using SELF JOIN to find the products supplied by more than one vendor. This is one of the classic reasons that someone might use a SELF JOIN because it’s something that someone might not do typically (it may not be part of the initial database architecture) but it still might be something that someone might need to do occasionally.

Because this query involves a join of the ProductVendor table with itself, two different aliases (pv1 and pv2) are used in the FROM clause. These aliases are used to qualify the column names in the rest of the query.

Syntax:

SELECT DISTINCT pv1.ProductID, pv1.VendorID

FROM Purchasing.ProductVendor pv1

INNER JOIN Purchasing.ProductVendor pv2

ON pv1.ProductID = pv2.ProductID

AND pv1.VendorID <> pv2.VendorID

ORDER BY pv1.ProductID

This discussion can be extrapolated to include other types of JOIN queries in SQL such as:

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

JOIN statements can become quite complex. Advanced SQL developers can do very complicated things with SQL JOIN statements, but it should be noted that resource usage (CPU and RAM) both tend to go up the more complicated the JOIN statement is. Further, they can go up practically exponentially every time you add a new table. You can do a JOIN statement with multiple tables, but it’s not always the best method.

What are common mistakes with a SQL SELF JOIN?

Using the wrong type of JOIN (such as an inner join when an outer join is required) is one of the most common mistakes when using a SQL SELF JOIN. Another common mistake — which is more a resource usage issue or scalability issue — is using a SQL SELF JOIN on data sets that are prohibitively large. 

Complex JOIN queries are often to blame for increased CPU usage in applications. Sometimes, it’s possible that redesigning the table rather than implementing a complicated SELF JOIN may be better.

Other common SQL mistakes can become problematic during a SQL SELF JOIN, such as data type mismatches, predicate evaluation orders, and complex subqueries. Because the JOIN functionality can be complex and may not be easily readable or scannable, it’s easier to make syntax-oriented mistakes. You should thoroughly test SQL SELF JOIN queries to ensure that they deliver the necessary results.

What are the pros and cons of a SQL SELF JOIN?

As mentioned, there are reasons that Smith would use a SQL SELF JOIN. It is the most straightforward method of comparing a table to itself. It can help query hierarchical data and compare rows inside of a table. You can therefore use it for comparing data, analyzing data, and generating new data.

But there are cons to using a SQL SELF JOIN too. 

First, when using joins, it can be difficult to read the queries involved. A join can be confusing, especially during development, and it can be difficult to create a join query that produces the right results. Joins, overall, tend to be resource-intensive — depending on the amount of data that you plan to manipulate. 

And, of course, a SQL SELF JOIN has a very specific use case; you use it to join a table to itself. Developers who want to compare other tables with each other should explore different types of joins.

What about joining multiple tables?

A SQL SELF JOIN is not able to join multiple tables, but joining multiple tables is similar. For this, you can review our article SQL JOIN Multiple Tables. 

Most of the syntax involved when joining multiple tables will be similar to a SQL SELF JOIN. Two or more tables can join through SQL JOIN, and many of the advantages, benefits, and potential mistakes will be the same. Once a developer has mastered the “JOIN” concept, they should be able to join tables either way.

The primary difference between a SQL JOIN and a SQL SELF JOIN is that the tables that join together will not have the same architecture involved. This will make the queries more complex, as well as the end result.

How can you learn more about SQL SELF JOIN?

SQL SELF JOIN is a fairly straightforward tool, but its applications can be complex. Moreover, understanding SQL SELF JOIN and other more obscure SQL tools can help database developers become more advanced in their field. While most SQL operations will use only a handful of functions and keywords, a deeper or more in-depth understanding of SQL can always help professionals interested in improving their standing. Those who are interested can check out the Most Useful SQL Commands.

Those interested in learning more about SQL SELF JOIN should check out Udemy’s Oracle-based courses. Our SQL developer, Smith, could attend any of these classes to learn more about database management and Oracle — and he might even be able to improve upon his career through continuing education.

If you’re just breaking into SQL or need a refresher course, you might want to consider one of the many bootcamp courses offered on Udemy. In a bootcamp, you can go “from beginner to expert.” But even if you already have prior experience, a bootcamp can be an excellent refresher. Both Oracle and MySQL are SQL database technologies that are in common usage today, though one is proprietary and commercial, and the other is free and open-source.

Whether a SQL developer will need to know about advanced functionality such as SQL SELF JOIN has a lot to do with the type of development they will be doing. Some SQL programmers simply need to pull simple information from a database for analysis and business reports. Other SQL programmers need to be able to develop data-intensive packages from the ground up. But either way, an introductory course to SQL can help.

Summary

SQL JOIN statements are particularly robust tools. They make it possible to not only compare tables to themselves but also compare tables to other tales. But they’re fairly sophisticated tools, as well. Compared to other queries, their syntax can be complex. The results returned can also be complicated. And because SQL JOIN queries are particularly resource-intensive, care must be taken when creating them and utilizing them. 

As Smith climbs his career ladder, he can get more experienced and creative with JOIN statements, which will provide better results with less lag time. To become a pro in database programming, Smith can check out Udemy courses to enhance his SQL skills. Hope we helped Smith solve his problem. Wishing him all the best to achieve, accomplish, and prosper in this career as a DBA.