Simple PostgreSQL Self Join

This is what my employee table looks like where manager_id is Foreign Key to the same table it references to employee_id and indicates which employee has manager and who is.

 employee_id | first_name |  last_name  | manager_id
-------------+------------+-------------+------------
           1 | Windy      | Hays        |
           2 | Ava        | Christensen |          1
           3 | Hassan     | Conner      |          1
           4 | Anna       | Reeves      |          2
           5 | Sau        | Norman      |          2
           6 | Kelsie     | Hays        |          3
           7 | Tory       | Goff        |          3
           8 | Salley     | Lester      |          3

I did self left join and the result was what was expected.

SELECT
  e.first_name || ' ' || e.last_name AS employee,
  m.first_name || ' ' || m.last_name AS manager
FROM
  employee e
  LEFT JOIN employee m ON m.employee_id = e.manager_id;

    employee     |     manager
-----------------+-----------------
 Windy Hays      |
 Ava Christensen | Windy Hays
 Hassan Conner   | Windy Hays
 Anna Reeves     | Ava Christensen
 Sau Norman      | Ava Christensen
 Kelsie Hays     | Hassan Conner
 Tory Goff       | Hassan Conner
 Salley Lester   | Hassan Conner
(8 rows)

But this query gives me

SELECT
  e.first_name || ' ' || e.last_name AS employee,
  m.first_name || ' ' || m.last_name AS manager
FROM
  employee e
  LEFT JOIN employee m ON m.employee_id = e.manager_id;
WHERE m.employee_id IS NULL;

  employee  | manager
------------+---------
 Windy Hays |
(1 row)

So I don’t understand why it returns something instead of nothing.employee_id will never be a NULL…

http://sqlfiddle.com/#!17/6e6af/9

Answer

employee_id will never be a NULL…

The null value comes from column e.manager_id (not column m.employee_id ) – that’s Windy Hays, the top employee in hierarchical tree.

For this particular row, the left join that attempts to retrieve the corresponding manager does not find a match – so now m.employee_id is null in the resultset.

Maybe the where clause you wanted to write is:

WHERE e.employee_id IS NULL

… Which indeed will return no rows, as one would expect.