The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
A *self* join isn't actually a specific kind of join, but it's a technique used to join a table to itself by defining two instances of the table, each with its own alias. This approach can be useful when a row in the table includes a foreign key field that references the primary key of the same table; for example in a table of employees where an employee's manager is also an employee, or a table of product categories where each category might be a subcategory of another category.
1. Replace the existing query with the following code, which includes a self join between two instances of the **SalesLT.ProductCategory** table (with aliases **cat** and **pcat**):
```
SELECT pcat.Name AS ParentCategory, cat.Name AS SubCategory
FROM SalesLT.ProductCategory AS cat
JOIN SalesLT.ProductCategory pcat
ON cat.ParentProductCategoryID = pcat.ProductCategoryID
ORDER BY ParentCategory, SubCategory;
```
2. Run the query and review the results, which reflect the hierarchy of parent and sub categories.
//////////////////////////////
-- SELF JOIN
-- Prepare the demo
-- There's no employee table, so we'll create one for this example
CREATE TABLE SalesLT.Employee
(EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(256),
ManagerID int);
GO
-- Get salesperson from Customer table and generate managers
INSERT INTO SalesLT.Employee (EmployeeName, ManagerID)
SELECT DISTINCT Salesperson, NULLIF(CAST(RIGHT(SalesPerson, 1) as INT), 0)
FROM SalesLT.Customer;
GO
UPDATE SalesLT.Employee
SET ManagerID = (SELECT MIN(EmployeeID) FROM SalesLT.Employee WHERE ManagerID IS NULL)
WHERE ManagerID IS NULL
AND EmployeeID > (SELECT MIN(EmployeeID) FROM SalesLT.Employee WHERE ManagerID IS NULL);
GO
-- Here's the actual self-join demo
SELECT e.EmployeeName, m.EmployeeName AS ManagerName
FROM SalesLT.Employee AS e
LEFT JOIN SalesLT.Employee AS m
ON e.ManagerID = m.EmployeeID
ORDER BY e.ManagerID;
///////////////////// PUB Database Examples ////////////////////////////
How to Use Self-Joins in SQL
A table can be joined to itself in a self-join. For example, you can use a self-join to find out the authors in Oakland, California who live in the same zip code area.
Because this query involves a join of the authors table with itself, the authors table appears in two roles. To distinguish these roles, you must give the authors table two different aliases (au1 and au2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:
USE pubs
SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
FROM authors au1 INNER JOIN authors au2
ON au1.zip = au2.zip
WHERE au1.city = 'Oakland'
ORDER BY au1.au_fname ASC, au1.au_lname ASC
Here is the result set:
au_fname au_lname au_fname au_lname
-------------------- ------------------- -------------------- ---------
Dean Straight Dean Straight
Dean Straight Dirk Stringer
Dean Straight Livia Karsen
Dirk Stringer Dean Straight
Dirk Stringer Dirk Stringer
Dirk Stringer Livia Karsen
Livia Karsen Dean Straight
Livia Karsen Dirk Stringer
Livia Karsen Livia Karsen
Marjorie Green Marjorie Green
Stearns MacFeather Stearns MacFeather
(11 row(s) affected)
///////////////////////Copy Code //////////////////
/////////////////////////////
To eliminate the rows in the results in which the authors match themselves and to eliminate rows that are identical, except the order of the authors is reversed, make this change to the Transact-SQL self-join query:
USE pubs
SELECT au1.au_fname, au1.au_lname, au2.au_fname, au2.au_lname
FROM authors au1 INNER JOIN authors au2
ON au1.zip = au2.zip
WHERE au1.city = 'Oakland'
AND au1.state = 'CA'
AND au1.au_id < au2.au_id
ORDER BY au1.au_lname ASC, au1.au_fname ASC
Here is the result set:
au_fname au_lname au_fname au_lname
------------ ----------------- -------------------- --------------------
Dean Straight Dirk Stringer
Dean Straight Livia Karsen
Dirk Stringer Livia Karsen
(3 row(s) affected)
It is now clear that Dean Straight, Dirk Stringer, and Livia Karsen all have the same zip code and live in Oakland, California.
Related Content
