Understanding SQL Server SELF JOIN By Practical Examples

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.

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;

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

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;

FAQ

How many types of SQL JOINs are there?

In fact, there are four main types of JOINs in SQL Server: INNER JOIN, SELF JOIN, CROSS JOIN, and OUTER JOIN. However, experts sometimes talk about six types. That's because OUTER JOINS can further be divided into LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS.

What is the difference between SELF JOIN and CROSS JOIN?

While CROSS JOINs are used to join each and every row of the two tables, SELF JOINs allow joining the same table to itself. In other words, you can CROSS JOIN two or more tables, but in SELF JOIN, you query only one table.

What are SQL JOINs used for?

JOINs in SQL clause are used retrieve data from multiple tables, based on logical relationships between those tables. The type of the JOIN specifies how SQL Server should use data from the first table to select rows from the second table.

Understanding SQL Server SELF JOIN By Practical Examples

What is SELF JOIN and how does it work?

SELF JOIN syntax

How can SQL Complete help with SELF JOINs

SQL SELF JOIN Examples

Using SELF JOIN to query hierarchical data

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete

Using SELF JOIN to compare rows in the same table

Using SQL SELF JOIN with INNER JOIN

Using SQL SELF JOIN with LEFT JOIN

Using SQL SELF JOIN with WHERE clause

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete

FAQ

dbForge SQL Complete

Advanced solution for SQL database development, management,
and administration

Understanding SQL Server SELF JOIN By Practical Examples

What is SELF JOIN and how does it work?

SELF JOIN syntax

How can SQL Complete help with SELF JOINs

SQL SELF JOIN Examples

Using SELF JOIN to query hierarchical data

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete

Using SELF JOIN to compare rows in the same table

Using SQL SELF JOIN with INNER JOIN

Using SQL SELF JOIN with LEFT JOIN

Using SQL SELF JOIN with WHERE clause

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete

FAQ

dbForge SQL Complete

Advanced solution for SQL database development, management, and administration

Advanced solution for SQL database development, management,
and administration