My LEFT JOIN does not return the correct results. What am I doing wrong?

When I run my query as INNER JOIN between two tables, I get the correct result - 182 in all.

However, when I run the query as LEFT JOIN, I get only 8 records back. Am I performing the join incorrectly?

First the code:

select e.username,
       e.password,
       coalesce(r.access_level, 0) as orgid
  from employees e
       left join retired r
           on e.employeeid = r.employeeid
 where access_level=3

The Retired table has only 182 records. Both tables are related by EmployeeId.

The 82 records in Retired table also exist in Employees but Employees table has over 7 thousand records.

One of the fieldnames Retired is called Access_Level with a value of 3.

Any ideas why LEFT JOIN isn't giving me an accurate result?

---

The trick with outer joins and conditions is to move the condition from the where clause into the join condtions clause:

select
    e.username,
    e.password,
    coalesce(r.access_level, 0) as orgid
from employees e
left join retired r
    on e.employeeid = r.employeeid
    and access_level=3

The reason you must do this is that the where clause is a filter on the rowset,which executes after the joins are made. By having a condition on the outer joined table in the where clause you effectively make the outer join into an inner join, because missed outer joins have null values in the columns of the joined table, but a condition in the where clause will insist there is a value there.

Conditions in the join clause are executed as the join is made, so by moving the condition out of the where clause, you allow the join to miss while still imposing the condition, but return an all-null joined row if no suitable row is found in the joined table.

---

After some more info from comments, it seems this is what you wanted:

select distinct
    e.username,
    e.password,
    coalesce(r.access_level, 0) as orgid
from employees e
join retired r
    on e.employeeid = r.employeeid
    and access_level=3