The SQL Server SELF JOIN is a regular one where a table (Table1) joins with itself. It uses the unique column within the same table to perform the join. If we have the primary key and foreign key in the same table, we can use a SELF JOIN to connect them.
The SQL SELF JOIN is very helpful when comparing the rows or records within the same table, something like a parent-child relationship. For instance, comparing rows to remove duplicates, retrieving the employee and department relationship, product categories, subcategories, and actual product relationships.
NOTE: In SQL Server, there is no specific SELF JOIN option; you can use either INNER or any of the OUTER, such as LEFT OUTER, RIGHT OUTER, to join a table with itself.
One of the general scenarios that explains the SQL SELF JOIN concept is an employee and a Manager or department. In general, every employee is connected to one of the Team Leads, and they are connected to managers in the organisation. Next, all managers are connected to the Directors, VP, or CEO based on the company structure. If there is a case where we have to find out the team lead of each employee, then use the SELF JOIN concept to establish a connection to the table itself.
SQL SELF JOIN Syntax
The most common and newer syntax version of the SELF JOIN to produce the hierarchical data is shown below.
SELECT t1.column(s), t2.column(s),
FROM Table1 AS t1 JOIN Table1 AS t2
ON t1.commonCol = t2.commonCol
In the above syntax, t1 and t2 are ALIAS names for the same table, i.e., Table1. Next, commonCol is the column to establish the connection or combine the table with itself.
The other option is to avoid the JOIN keyword and use the WHERE clause to apply the condition to perform the SELF JOIN.
SELECT t1.column(s), t2.column(s),
FROM Table1 AS t1, Table1 AS t2
WHERE t1.common = t2.common
NOTE: When performing a SELF JOIN, it is mandatory to provide ALIAS table names because we are using the same table twice.
To demonstrate the SQL Server SELF JOIN, we will create a simple Categories table to list the product categories within our database. As you can observe, we inserted eight different records, and each category belongs to a different ParentID.
CREATE TABLE Categories (
ID INT PRIMARY KEY,
Name NVARCHAR(100) NOT NULL,
ParentID INT NULL,
);
GO
INSERT INTO dbo.Categories (ID, Name, ParentID) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Desktops', 2),
(4, 'Laptops', 2),
(5, 'Mobiles', 1),
(6, 'Fashion', NULL),
(7, 'Clothing', 6),
(8, 'Footwear', 6);
GO
The above code creates a three-level deep product categories structure. Suppose we consider the electronics category: Electronics -> Computers -> Desktops/Laptops. The list below explains the data at different levels.
- Level 1: Electronics and Fashion.
- Level 2: Computers and Mobiles under Electronics, Clothing and Footwear under Fashion.
- Level 3: Desktops and Laptops under Computers.
TIP: Use the SELF JOIN when you have a PRIMARY and FOREIGN key in a single table.
SQL SELF JOIN Example
Once you execute the above CREATE TABLE and INSERT statement, you can see a simple table with eight records. From the table, you can observe that each product category is linked to its parent category using ParentID. For instance, Laptops are linked to Computers. To retrieve the product subcategories and their respective parent categories, we must use a SQL SELF JOIN.
In the following query, we are joining the Categories table to itself. Here, we used the ID and ParentID columns in the ON clause to establish the connection. Next, using the table ALIAS names, we assigned the same Name as Category and ParentCategory.
SELECT s.ID, s.Name AS Category,
p.Name AS ParentCategory
FROM Categories s
JOIN Categories p
ON s.ParentID = p.ID
If you observe the SERVER result set, it misses the 1 and 6 IDs because the ParentID values for Electronics and Fashion are NULL.
If you change the INNER to LEFT JOIN, it will display all the categories and their respective ParentCategory names. It is useful to see all the information in a table and its next-level information.
SELECT s.ID, s.Name AS Category,
p.Name AS ParentCategory
FROM Categories s
LEFT JOIN Categories p
ON s.ParentID = p.ID
ID Category ParentCategory
1 Electronics NULL
2 Computers Electronics
3 Desktops Computers
4 Laptops Computers
5 Mobiles Electronics
6 Fashion NULL
7 Clothing Fashion
8 Footwear FashionUsing SQL SELF JOIN with a WHERE clause
As we mentioned earlier, apart from using the JOINS, we can use the WHERE clause to perform a SELF JOIN on a table. The query below is the same as the first example, but instead of using the INNER JOIN keyword, we used the comma to separate the table names. Next, instead of the ON clause, use the WHERE clause to apply the condition to join the table to itself.
SELECT s.ID, s.Name AS Category,
p.Name AS ParentCategory
FROM Categories s, Categories p
WHERE s.ParentID = p.ID
ID Category ParentCategory
2 Computers Electronics
3 Desktops Computers
4 Laptops Computers
5 Mobiles Electronics
7 Clothing Fashion
8 Footwear FashionApart from the above example, you can apply multiple conditions using this approach. For instance, we used the AND operator to display the categories whose parent category is ‘Electronics’.
SELECT s.ID, s.Name AS Category,
p.Name AS ParentCategory
FROM Categories s, Categories p
WHERE s.ParentID = p.ID AND p.Name = 'Electronics'
ID Category ParentCategory
2 Computers Electronics
5 Mobiles ElectronicsNOTE: If you want to use the newer syntax of using the JOIN keyword, add “WHERE p.Name = ‘Electronics’” at the bottom of the first example. It gives the same result.
Using SQL SELF JOIN with ORDER BY
The result set returned by the SELF JOIN using the WHERE clause or the JOIN keyword always depends upon the data stored in the table. If you observe the result set of the first WHERE clause example, it returns the records based on the ID column in ascending order. However, you can use the ORDER BY clause along with the SELF JOIN to change the order. The query below sorts the results based on the ParentCategory and Category in ascending order.
SELECT s.ID, s.Name AS Category,
p.Name AS ParentCategory
FROM Categories s, Categories p
WHERE s.ParentID = p.ID
ORDER BY ParentCategory, Category
ID Category ParentCategory
3 Desktops Computers
4 Laptops Computers
2 Computers Electronics
5 Mobiles Electronics
7 Clothing Fashion
8 Footwear FashionSQL SELF JOIN with GROUP BY
When working with hierarchical data, aggregations play a vital role. For instance, if you want to find the total number of employees working under a particular manager.
The query below counts the total number of products available under each parent category. For this, we used the COUNT function and the GROUP BY clause.
SELECT p.Name AS Category,
COUNT(s.ID) AS Total
FROM Categories s
LEFT JOIN Categories p ON s.ParentID = p.ID
GROUP BY p.Name
ORDER BY Category
Category Total
NULL 2
Computers 2
Electronics 2
Fashion 2Multi-Level Hierarchical Data
To make it simple, this SQL Server SELF JOIN article uses only eight records, but in real-time, the data will be huge, and the hierarchical data levels may be more than two. In such a case, you must use the example query below. It joins the same table to itself multiple times using the LEFT JOIN to display the data up to three levels.
SELECT p.ID, p.Name AS Product,
sp.Name AS SubCategory,
c.Name AS Category
FROM Categories p
LEFT JOIN Categories sp ON p.ParentID = sp.ID
LEFT JOIN Categories c ON sp.ParentID = c.ID
Recursive SELF JOIN in SQL
The above example successfully joins the table to itself multiple times to display the multi-level hierarchical data. However, if the levels are increased, it might be difficult to identify the child, parent, and grandparent chain. In such a case, you can use the recursive SELF JOIN concept to display the levels as breadcrumbs or a full path. To achieve the same, use the Common Table Expression, CAST function, and UNION ALL operator. Within the CTE, the first SELECT statement uses IS NULL to display the products whose ParentID is a NULL value.
WITH CategoryLevels AS (
SELECT ID, Name, ParentID,0 AS Level,
CAST(Name AS NVARCHAR(250)) AS Hierarchy
FROM Categories WHERE ParentID IS NULL
UNION ALL
SELECT c.ID,c.Name,c.ParentID,cl.Level + 1,
CAST(cl.Hierarchy + ' -> ' + c.Name AS NVARCHAR(250))
FROM Categories c
JOIN CategoryLevels cl ON c.ParentID = cl.ID
)
SELECT * FROM CategoryLevels
--WHERE Level > 0
ORDER BY Hierarchy
TIP: If you remove the comment (–) from the above code, it avoids displaying the 0-level categories.
SQL SELF JOIN Common Mistakes and Best Practices
A SELF JOIN is the most important concept to combine any table with itself and compare rows inside a table against each other to query hierarchical data.
- When performing the SELF JOIN, most people confuse themselves and select the wrong type of join. For example, choosing the OUTER JOINS, such as LEFT, when an INNER JOIN is needed.
- Always use the table ALIAS names.
- It is essential to consider the table size because for a large number of records, the SELF JOIN uses high CPU, and it impacts the query performance.
- Use the subqueries to perform the complex calculations.
- If the table is large, replace the SELF JOIN with a CTE or subquery.
- Use the WHERE clause to filter the incoming data and restrict the result set.
- Combining the SELF JOIN with aggregate functions such as SUM, AVG, COUNT, etc, allows you to perform calculations within the same table.
Comments are closed.