Query that selects the names of all employees and the names of their bosses-CodePudding

I need to write a query that selects the names of all employees and the names of their bosses. If there is no boss for an employee, return NULL. Bosses are consider to be employees as well.

Table:

CREATE TABLE employees (
  id INTEGER NOT NULL PRIMARY KEY,
  bossId INTEGER,
  name VARCHAR(20) NOT NULL,
  FOREIGN KEY (bossId) REFERENCES employees(id)
  );
  
  INSERT INTO employees(id, bossId, name) VALUES(1, NULL, 'Fred');
  INSERT INTO employees(id, bossId, name) VALUES(2, 1, 'Jason');
  INSERT INTO employees(id, bossId, name) VALUES(3, 1, 'Mike');

Expected result:

name	boss
Fred
Jason	Fred
Mike	Fred

CodePudding user response：

You need left join, if the employee doesn't have a boss then show null in output

select emp.name,empBoss.name
from employees  emp 
left join employees empBoss
on emp.bossId = empBoss.id