SQL TUTORIAL - SELF JOINS Hierarchy Tables
Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.
You can now support me on patreon -
This SQL Tutorial is all about hierarchy tables and self joins.
We start off by looking at hierarchies that we are all aware of such as Parent and Child.
The video then describes how we can create a hierarchy table in SQL Server including a Primary Key and a Foreign Key that references the Primary Key in the same table.
If you are new to working with constraints in SQL Server please check out these videos:
SQL Tutorial - Primary Key Constraints:
SQL Tutorial - Create Table with Constraints:
If you would like to follow along with this SQL Tutorial then this is the create table statement used:
CREATE TABLE dbo.Staff
(
StaffId INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Staff_StaffId PRIMARY KEY (StaffId)
, StaffTitle VARCHAR(50) NOT NULL
, ManagerId INT NULL
CONSTRAINT FK_Staff_ManagerId FOREIGN KEY (ManagerId) REFERENCES dbo.Staff (StaffId)
)
We then insert some dummy data in to the Hierarchy table and have a look at the results when executing a SELECT statement.
On to discussing Self Joins, and the difference between using an INNER JOIN and a LEFT OUTER JOIN when joining a table to itself although the same applies if you were joining two separate underlying tables together.
Then we apply a Self Join and return results, as the table and columns are the same we need to apply aliases.
SELECT
A.StaffTitle
, B.StaffTitle AS ReportsTo
FROM dbo.Staff AS A
LEFT OUTER JOIN dbo.Staff AS B
ON A.ManagerId = B.StaffId
Watch video SQL TUTORIAL - SELF JOINS Hierarchy Tables online without registration, duration 14 minute 52 second in high hd quality. This video was added by user BeardedDev 24 October 2018, don't forget to share it with your friends and acquaintances, it has been viewed on our site 9 thousand once and liked it 11 people.