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.
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.
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.
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.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
Self-Join-Table.zip
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.
Code Block
EmployeeTable.sqlce
Create the Employee table.CREATE TABLE Employee
(
EmployeeId int,
LastName nvarchar(30),
Country nvarchar(30),
DepartmentId int
)
If you’re ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
Click the execute button and make sure it completes successfully.
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.
Code Block
InsertEmployees.sqlce
Insert entries into the Employee table.
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
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
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.
Code Block
Self-Join.sqlce
Create the Self-Join query.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;
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect’s help, we were able to avoid any headaches!
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.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
Self-Join-Table.zip
