Lesson 7 of 15 · 18 min
Self joins: a table joined to itself
By the end of this lesson you can join a table to itself with two aliases, follow a self-referencing foreign key in the right direction, keep top-level rows with a LEFT self join, and pair rows of the same table.
In the Masar Holding HR database, every employee row has a manager_id. A manager is also an employee, so manager_id is a foreign key that points back into the same table, at employees.employee_id. This is a self-referencing key.
To show an employee's name next to their manager's name, you need two rows of employees side by side: the employee's row and the manager's row. A self join does exactly that. There is nothing special about it: it is an ordinary join in which both tables happen to be the same table. The only requirement is that each copy gets its own alias, so every column reference says which role it plays.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons