What is SELF JOIN in SQL Server?

While I am working on the table, I need to compare the rows inside the same table. In these cases, SQL Server’s SELF JOIN will be helpful. I used SELF JOIN to compare the data to the one where I got the exact output that I wanted.

In this post, I will explain a self-join, how it performs, and when you should consider using it in your projects. Let’s get started to learn more.

SQL Server Self Join

In SQL Server, we can join a table to itself using the SELF JOIN clause. This is beneficial for comparing rows inside the same table or query data structures. A SELF JOIN clause uses only the INNER JOIN or LEFT JOIN clause.

The reason is that the self-join references to the same table and the table’s alias name are used to assign different names to the same table within the query. Remember that if you use the alias name of the table is used more than once in the same table then it will throw a syntax error.

SYNTAX:

SELECT SELECTED_COLUMNLIST
FROM TABLE_1 AS T1
[ LEFT | INNER ] JOIN TABLE_1 AS T2
ON JOIN_PREDICTION;

In the syntax explanation:

• In this query, TABLE_1 has been referenced twice. The table alias_names T1 and T2 have been assigned to TABLE_1 by different names in the query.

First, we have created a table called STAFF by the following query:

STAFF TABLE:

CREATE TABLE STAFF (
	ID INT PRIMARY KEY,
	FIRST_NAME VARCHAR(50),
	LAST_NAME VARCHAR(50),
	EMAIL VARCHAR(50),
	MANAGER_ID INT,
	FOREIGN KEY (MANAGER_ID) 
        REFERENCES STAFF (ID) 
        ON DELETE NO ACTION ON UPDATE NO ACTION
);

INSERT INTO STAFF (ID, FIRST_NAME, LAST_NAME, EMAIL, MANAGER_ID) 
VALUES (1, 'Joshuah', 'Broad', 'jbroad0@jugem.jp', null),
(2, 'Thorndike', 'Garstan', 'tgarstan1@phpbb.com', 1),
(3, 'Adham', 'Whitwell', 'awhitwell2@harvard.edu', 2),
(4, 'Meridel', 'Minci', 'mminci3@plala.or.jp', 2),
(5, 'Afton', 'Gooders', 'agooders4@patch.com', 5),
(6, 'Marcelline', 'Tythacott', 'mtythacott5@sciencedaily.com', 5),
(7, 'Milt', 'Pennacci', 'mpennacci6@prlog.org', 4),
(8, 'Peg', 'Chess', 'pchess7@time.com', 4),
(9, 'Sanson', 'Forster', 'sforster8@dagondesign.com', 4),
(10, 'Gayel', 'Di Nisco', 'gdinisco9@topsy.com', 9);

The STAFF table stores staff information such as ID, FIRST_NAME, LAST_NAME, and EMAIL columns. There is also one more column, MANAGER_ID, which acts as the direct manager. For example, a staff member named Thorndike reports the work details to the manager Joshuah. Joshuah has no manager, so the manager_id column is a NULL value.

Let’s see how reports to whom if we use the SELF JOIN clause in SQL Server by the following query.

SELECT
e.FIRST_NAME + ' ' + e.LAST_NAME EMPLOYEES,
m.FIRST_NAME + ' ' + m.LAST_NAME MANAGERS
FROM
 STAFF e
INNER JOIN STAFF m ON m.ID = e.MANAGER_ID
ORDER BY
 MANAGERS;

In this example query, we have made two references to the STAFF table: e for EMPLOYEES and m for MANAGERS. Based on the values in the m.ID and e.MANAGER_ID columns, the join prediction fits the relationship between EMPLOYEES and MANAGERS.

We hope that you have understood how to use the SELF JOIN clause in SQL Server for hierarchal data or compare rows between the same table.

SQL Server Self-Join Performance

The result of using a SELF JOIN is that it usually doubles the number in the result set. The extra output data could lead to poor performance, so when we use a window function to cut down on some extra bytes from the query that is generated by the query.

We hope that you have understood the performance of SELF JOIN in SQL Server.

SQL Server Self-Join Recursive

Fixed-point joins are another name for recursive joins. They are used to collect parent-child information. Recursive common table expressions are how recursive joins are implemented in SQL. Recursive common table expressions (CTEs) let you repeatedly refer to a query.

A WITH statement is used to define a CTE, and then a table expression is defined after that. The AS command can rename a column or table containing an alias. A UNION statement and recursion are requirements for a recursive CTE. Here is a sample of SQL Server SELF JOIN in Recursive CTE by the following query:

WITH CTE_ORGANIZATION AS (
SELECT 
ID,FIRST_NAME,LAST_NAME,MANAGER_ID
FROM STAFF
WHERE MANAGER_ID IS NULL 
UNION ALL
SELECT 
e.ID,e.FIRST_NAME,e.LAST_NAME,e.MANAGER_ID
FROM
 STAFF e
INNER JOIN CTE_ORGANIZATION o 
ON o.ID = e.MANAGER_ID)
SELECT * FROM CTE_ORGANIZATION;

In this example, the anchor member obtains the top manager, and subsequent iterations of the recursive query return subordinates of subsequent top managers, and so on.

We hope that you understand how to use the SQL Server SELF JOIN clause with RECURSIVE CTE for the hierarchical data.

SQL Server Self Join Previous Row

In SQL Server, the TOP clause filters the result set based on the number given in the query.

If we want to get the previous row from a table, we have used the TOP keyword in the query with the help of the SELF JOIN clause. Here is an example below:

SELECT TOP 1
e.FIRST_NAME + ' ' + e.LAST_NAME EMPLOYEES,
m.FIRST_NAME + ' ' + m.LAST_NAME MANAGERS
FROM
 STAFF e
INNER JOIN STAFF m ON m.ID = e.MANAGER_ID
ORDER BY
 MANAGERS;

To get the previous row from a table, we have used the TOP clause as TOP 1 to get the previous record from a table. And by the SELF JOIN clause, it gets the result set.

I hope you understand how to get the previous row from a table Server SELF JOIN clause.

SQL Server Self Join With Where Clause

Do we know the use of the WHERE clause in SQL Server? Here is the definition of the WHERE clause: It is used to extract only those records that fulfill its condition. Here is a sample example of SELF JOIN with WHERE clause in SQL Server by the following query.

SELECT
e.FIRST_NAME + ' ' + e.LAST_NAME EMPLOYEES,
m.FIRST_NAME + ' ' + m.LAST_NAME MANAGERS
FROM
 STAFF e
INNER JOIN STAFF m ON m.ID = e.MANAGER_ID
WHERE e.MANAGER_ID>2
ORDER BY
 MANAGERS;

So, with the help of the SELF JOIN clause, the SELECT statement retrieves all records from the STAFF table with the WHERE condition. In the WHERE condition, the MANAGER_ID is used with the GREATER THAN operator to find a value greater than 2 from the STAFF table.

Once the WHERE condition is true, the SELECT statement will retrieve all records from the STAFF table using the query’s SELF JOIN clause.

We hope that you have understood how to use SQL Server WHERE clause with the SELF JOIN clause on the table by the query.

Conclusion

After reading this tutorial, we now know about the post SELF JOIN in SQL Server.

Also, take a look at some more SQL Server tutorials.

• If Else In Trigger SQL Server
• SQL Server INNER JOIN Tutorial
• Update Table Using Join in SQL Server
• Recursive Trigger in SQL Server
• Debug an SQL Server Trigger