BBampaa

Lessons › SQL › Chapter 8: Text, dates and safe maths

SQL lesson

Comparing rows in one table: self join

Sometimes the row you need to compare with is in the same table. In employees, each person's manager is also an employee. A self join uses the table twice, under two short names, like e for the employee and m for the manager.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

A table joined to itself

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id

Result:

employeemanager
Maya TanGrace Lee
Liam OkaforGrace Lee
Priya ShahLiam Okafor
Hugo DiazLiam Okafor
Naomi SatoMaya Tan

Step by step

  1. The first copy, called e: the employee.
    SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.employee_id
  2. The same table again, called m. Each employee is matched to their manager's row.
    SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.employee_id
  3. The short name says which copy each column comes from.
    SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.employee_id

Practice

Try each one in your head first, then open the answer.

1. Using employees, show each employee's name with their manager's name. Name them employee and manager.
SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.employee_id
2. Using employees, show every employee's name with their manager's name, keeping Grace Lee, who has no manager. Name them employee and manager.
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id
3. Using employees, show the name and salary of each employee who earns more than their manager.
SELECT e.name, e.salary FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.salary > m.salary
4. Using products, show each product's name with the name of the product it replaces. Name them product and replaces.
SELECT p.name AS product, o.name AS replaces FROM products p JOIN products o ON p.replaces_id = o.product_id
5. Using employees, show the name of each employee whose manager works in a different city.
SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.employee_id WHERE e.city <> m.city

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed