SQL Server SELF JOIN – Detailed Guide

Do you have any knowledge about SQL Server’s SELF JOIN? Do you have any experience with SQL Server’s SELF JOIN? Don’t worry; we’ll go over its definition and its usage in SQL Server. The following is a list of the subtopics that will be covered:

• What is SQL Server SELF JOIN?
• What is the syntax of SELF JOIN in SQL Server?
• What is the performance of the SQL Server SELF JOIN clause?
• How to use RECURSIVE in SQL Server SELF JOIN clause?
• How to get the previous row from a table by using the SQL Server SELF JOIN clause?
• How to use SQL Server SELF JOIN clause with the WHERE condition?

SQL Server Self Join

We can join a table to itself in SQL Server using the SELF JOIN clause. It is beneficial to compare rows inside the same table or query data structures. A SELF JOIN clause uses only INNER JOIN or LEFT JOIN clause.

The reason is that the self-join references to the same table and the alias name of the table 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.

Here is the image of SELF JOIN in SQL Server:

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_name as T1 and T2 has been assigned to the TABLE_1 by different names in the query.

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

STAFF TABLE:

USE SQLSERVERGUIDES;

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 the staff information such as ID, FIRST_NAME, LAST_NAME, and EMAIL columns. There is also one more column as MANAGER_ID which works as the direct manager. For example, a staff member name Thorndike reports the work detail 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:

USE SQLSERVERGUIDES;

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.

Read: SQL Server LEFT JOIN Tutorial

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 which 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. A column or table containing an alias can be renamed using the AS command. 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:

USE SQLSERVERGUIDES;

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 illustration, the top manager is obtained by the anchor member, and subsequent iterations of the recursive query return subordinates of subsequent top managers, and so on.

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

Read: What is SQL Server Cross Join?

SQL Server Self Join Previous Row

In SQL Server, the TOP clause is used to filter the result set based on the number given in the query.

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

USE SQLSERVERGUIDES;

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.

We hope that you have understood how to get the previous row from a table by using the SQL Server SELF JOIN clause.

Read: SQL Server Outer Join With Count

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 which fulfill its condition. Here is a sample example of SELF JOIN with WHERE clause in SQL Server by the following query:

USE SQLSERVERGUIDES;

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 retrieve 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 turns out to be TRUE then the SELECT statement will retrieve all records from the STAFF table by using the SELF JOIN clause by the query.

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

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
• Execute Trigger in SQL Server

We now know about the post “SELF JOIN in SQL Server” after reading this tutorial. Here is a list of each subtopic we covered to help you understand the concept.

• What is SELF JOIN in SQL Server?
• What is the syntax of SELF JOIN in SQL Server?
• What is the performance of the SQL Server SELF JOIN clause?
• How to use RECURSIVE in SQL Server SELF JOIN clause?
• How to get the previous row from a table by using the SQL Server SELF JOIN clause?
• How to use SQL Server SELF JOIN clause with the WHERE condition?