What's wrong with this self join query?

Question

I apologize for such a basic question but I'm new to SQL and can't figure this out somehow. I have a simple table with 3 columns: id, name, and manager_id. Sample entries are:

• 1, Mike, 3
• 2, Kate, 3
• 3, Joe, NULL

I want to return name and the person's manager, e.g. Mike - Joe, Kate - Joe, etc. I'm trying the following code:

SELECT
  uno.name AS employee,
  dos.name AS manager
FROM
    `dataset.workers` AS uno
JOIN `dataset.workers` AS dos 
  ON uno.id = dos.manager_id 

and im getting two sets of names but they don't match (for example, I get Mike - NULL, Kate - Mike). How should I tweak the query to get what I need?

Answer 1

Here's the solution. You want to use left join in case someone doesn't have a manager.

select t.name
      ,t2.name as manager
from   t left join t t2 on t2.id = t.manager_id

name	manager
Mike	Joe
Kate	Joe
Joe	null

Fiddle