How to Self-Join a Table in SQL Server

This tutorial will show you how to self-join a table in SQL Server. By self-joining a table we are able to manipulate SQL Server into thinking that a self-joined table is split in two tables, thus allowing us to run queries that otherwise wouldn’t be possible. A self-join runs just like any other join with the only difference being that aliasing tables is required because without table aliases, all column names in the result of the join would be uncertain.

Setting Up

In this tutorial we will use an employee table with columns of ‘EmployeesId’, ‘LastName’, ‘Country’, and ‘DepartmentId’. The EmployeeId and DepartmentId columns will be of data type ‘int’ while the LastName and Country columns will be of data type nvarchar.

CREATE TABLE Employee ( EmployeeId int, LastName nvarchar(30), Country nvarchar(30), DepartmentId i <br>

CREATE TABLE Employee (     EmployeeId int,     LastName nvarchar(30),     Country nvarchar(30),     DepartmentId int )

Click the execute button and make sure it completes successfully.

Step One

Now that we have the outline of our table, we need to insert data into it. We will use the INSERT INTO statement to do this. By combining the SELECT and UNION ALL statements we are able to insert multiple values into the table in a single query. The SELECT statement simply defines the values being entered while the UNION ALL statement allows for duplicates to be entered.
INSERT INTO Employee(EmployeeId, LastName, Country, DepartmentId) SELECT 123, 'Penn', 'Australia', 31 UNION ALL SELECT 124, 'Fitch', 'Australia', 33 UNION ALL SELECT 145, 'Jackson', 'Australia', 33 UNION ALL SELECT 201, 'Shields', 'United States', 34 UNION ALL SELECT 305, 'Pierre', 'Germany', 34 UNION ALL SELECT 306, 'Velasquez', 'Germany', 

INSERT INTO Employee(EmployeeId, LastName, Country, DepartmentId) SELECT 123, 'Penn', 'Australia', 31 UNION ALL SELECT 124, 'Fitch', 'Australia', 33 UNION ALL SELECT 145, 'Jackson', 'Australia', 33 UNION ALL SELECT 201, 'Shields', 'United States', 34 UNION ALL SELECT 305, 'Pierre', 'Germany', 34 UNION ALL SELECT 306, 'Velasquez', 'Germany', NULL

Click the execute button and make sure it completes successfully.

Step Two

Now that we have entered data into the Employee table we can create the query that will self-join the table. This is where detail plays a factor. It is important to know what is happening in the query so that you won’t be surprised with the outcome.
First thing we will do is select the EmployeeId, LastName, and Country columns from the Employee table but with a slight twist. Since we are self-joining the table we have to hypothetically split the table into two, this is possible by simply giving them aliases. We use the same columns twice but with different aliases, in this case A and B, thus creating two tables with one.
Second thing we do is determine what table to select from. Since we declared two different tables in the SELECT statement we are eligible to select from those tables, in this case from Employee A and Employee B.
The third and final thing we do is determine exactly what data to extract from the table, or tables depending how you look at it. Since we want to create a table with all possible outcomes of employees in the same country, we use the WHERE clause to select employees in the same country and different employees by comparing the EmployeeId of both aliases.

SELECT A.EmployeeId, A.LastName, B.EmployeeId, B.LastName, A.Country FROM Employee A, Employee B WHERE A.Country = B.Country AND A.EmployeeId < B.EmployeeId;

SELECT A.EmployeeId, A.LastName, B.EmployeeId, B.LastName, A.Country FROM Employee A, Employee B WHERE A.Country = B.Country AND A.EmployeeId < B.EmployeeId;

Execute the query and make sure it completes successfully.

Output

When the query is executed you should see unique results in each row of the table. You will see three results of Australia with different combinations of LastName in each row, and since there were only two instances of Germany we have only one possible result.