CODEWITHSUNDEEP
sqlsql-serverjoin
Chidi Okeh
Chidi Okeh

Reputation: 1557

My LEFT JOIN is not returning 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?

Upvotes: 0

Views: 2838

Answers (3)

Bohemian
Bohemian

Reputation: 424983

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

Upvotes: 2

Anthony Grist
Anthony Grist

Reputation: 38345

If Access_Level is a column in the Retired table then its value will be NULL for any rows in Employee that don't have a corresponding row in Retired. Those rows will then be filtered out by your WHERE clause as they don't have a Access_Level of 3. Moving that condition to the ON clause should be enough:

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

Upvotes: 3

AHiggins
AHiggins

Reputation: 7219

Using a field from the retired table in your WHERE clause is basically automatically performing an INNER JOIN, because only records that have a valid access_level value (i.e., NOT NULL) are being returned.

If you want to see all records from employee, you would need to modify your WHERE clause to account for employee records that do not have a match in retired, such as saying WHERE r.access_level = 3 OR r.employeeid IS NULL.

Upvotes: 2

Related Questions