Save Big on Cyber Monday! Up to 40% Off
ends in   {{days}}
Days
{{timeFormat.hours}}
:
{{timeFormat.minutes}}
:
{{timeFormat.seconds}}

Understanding SQL Server SELF JOIN By Practical Examples

A SQL SELF JOIN is a powerful technique that allows you to join a table to itself. This is useful for comparing rows within the same table, such as finding relationships between employees and their managers, or comparing records that share common attributes. While the process may seem complex, tools like dbForge SQL Complete can significantly simplify working with SELF JOINs by providing code completion, syntax checking, and formatting features. In this article, we will explore practical examples of SQL SELF JOINs and show how dbForge SQL Complete can help you streamline your SQL development and improve efficiency.

TL;DR
A SQL SELF JOIN allows you to join a table to itself to compare rows within the same table. It can be performed using INNER or LEFT JOINs, or through the WHERE clause. Common use cases include finding relationships like employees and their managers or comparing similar data. dbForge SQL Complete helps streamline this process by offering features like code completion, syntax validation, and query formatting to improve efficiency and reduce errors.

What is SELF JOIN and how does it work?

The SELF JOIN in SQL, as its name implies, is used to join a table to itself. This means that each row in a table is joined to itself and every other row in that table. However, referencing the same table more than once within a single query will result in an error. To avoid this, SQL SELF JOIN aliases are used.

What is SELF JOIN in SQL

SELF JOIN syntax

To perform a SELF JOIN in SQL, the LEFT or INNER JOIN is usually used. 

SELECT
    column_names
FROM
    Table1 t1
[INNER | LEFT]  JOIN Table1 t2 ON
    join_predicate;

Note: t1 and t2 are different table aliases for the same table.

You can also create the SELF JOIN with the help of the WHERE clause. 

SELECT 
    column_names
FROM 
    Table1 t1, Table1 t2
WHERE 
    condition;

How can SQL Complete help with SELF JOINs


SELF JOINs might be quite difficult to master and use. You need to clearly understand which columns and how to join to get the desired result. That's where SQL Complete can give a helping hand.

  • With SQL Complete, you don't need to memorize long lists of table columns — just select the required columns from the suggestion list.
  • Thanks to the extensive code-completion functionality that the tool provides, you can avoid tedious and time-consuming typing.
  • Built-in code snippets significantly reduce coding time — in case, you work a lot with SELF JOINs, just create custom snippets and then use them when needed.
  • SQL Complete's mighty syntax checker won't let a single typo go through and a robust Debugger will make sure that your code is working properly.
  • And finally you can instantly adjust your scripts to your company standards with the help of SQL Complete's Formatter.

As part of our SQL SELF JOIN tutorial, we will explain SELF JOINs with the examples presented in the dbForge SQL Complete tool.

SQL SELF JOIN Examples

Using SELF JOIN to query hierarchical data

Let's start studying SELF JOINs in SQL with a simple example. Suppose we have the Employee table that stores information about company employees and consists of the following columns: EmployeeId, FirstName, LastName, Email, AddressLine, City, and ManagerID. The ManagerID value shows whom the given employee reports to. In case an employee doesn't have a direct manager, the ManagerID field has a NULL.

In our example, Elwood Coleman doesn't have direct managers above him, and Lindsey Fife and Sonny Matox report to him. We want to get a result table showing an employee and their direct manager. For this, we will use SELF JOIN. 

SELECT
  e.FirstName + ' ' + e.LastName Employee
  ,m.FirstName + ' ' + m.LastName Manager
FROM Employee e
INNER JOIN Employee m
	ON m.EmployeeID = e.ManagerID
ORDER BY Manager;
SELF JOIN employee manager example

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete

Download

Using SELF JOIN to compare rows in the same table

Let's consider another example. Suppose that now we need to find employees that reside in the same city. For this, we run the following statement:

SELECT
  e1.City
  ,e1.FirstName + ' ' + e1.LastName AS employee_1
  ,e2.LastName + ' ' + e2.FirstName AS employee_2
FROM Employee e1
INNER JOIN Employee e2
	ON e1.EmployeeID > e2.EmployeeID
	AND e1.City = e2.City
ORDER BY e1.City,
employee_1,
employee_2;

We add the e1.EmployeeID > e2.EmployeeID condition to avoid the duplication of employee names in the first and second columns.

And the e1.City = e2.City condition makes sure that the city matches the two employees.

SQL JOIN same table twice on same column

Using SQL SELF JOIN with INNER JOIN

As we have already mentioned, there are several ways to join the same table twice in SQL. One of them is by using INNER JOIN.

Let's query our customerdemo database again. Suppose, now we want to find out pairs of cars that have been sold in the same model but different colors.

SELECT
   (SELECT
       crs.Car_model
     FROM Cars crs
     WHERE crs.CarID = 
   Car_model
   ,c.Color_name Color_name1
   ,(SELECT
       cls.Color_name
     FROM Colors cls
     WHERE cls.ColorID = cs1.ColorID)
   Color_name2
FROM CarsSales cs
INNER JOIN CarsSales cs1
   ON cs.CarID = cs1.CarID
     AND cs.ColorID <> cs1.ColorID
INNER JOIN Colors c
   ON cs.ColorID = c.ColorID
ORDER BY Car_model
SQL JOIN same table twice on same column

Using SQL SELF JOIN with LEFT JOIN

You can query the same table twice with the help of the LEFT JOIN too. And again don't forget to use aliases for each instance of the table or SQL Server will throw an error.

In the SELF JOIN query example below, we want to get a result table displaying pairs of orders made by the customer.

SELECT
   (SELECT
       c.Name
     FROM Customers c
     WHERE c.CustomerID = o1.CustomerID)
   CustomerName
   ,o1.OrderDate Date1
   ,o.OrderDate Date2
FROM Orders o
LEFT JOIN Orders o1
   ON o.CustomerID = o1.CustomerID
     AND o.OrderID <> o1.OrderID
ORDER BY CustomerName DESC, o.OrderDate
Create SQL SELF JOIN subquery with LEFT JOIN

Using SQL SELF JOIN with WHERE clause

There is no SELF JOIN operator in SQL, SELF JOIN, in fact, is a collective term for those queries that join the same table twice within one statement. Another popular way to do this is using the WHERE clause.

Let's return to our Employees table in the customerdemo database and try to get the result table showing employees living in the same city. We have already done that above but with the help of the INNER JOIN. 

SELECT
   A.LastName AS Employee1
   ,B.LastName AS Employee2
   ,A.City
FROM Employee A
   ,Employee B
WHERE A.LastName < B.LastName
AND A.City = B.City
ORDER BY A.City;
SQL SELF JOIN subquery using WHERE

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete

Download

FAQ

dbForge SQL Complete

Advanced solution for SQL database development, management,
and administration

Download