LEFT OUTER JOIN PostgreSQL works as INNER JOIN?

The following query works properly

SELECT "employee"."id", "employee"."created", (SELECT SUM(minutes) FROM jobs_job AS job WHERE job.employee_id = employee.id AND job.job_date BETWEEN '2018-10-21' AND '2018-10-30') FROM users_employee AS employee INNER JOIN users_user AS users ON (employee.user_id = users.id) WHERE NOT ("users"."status" = 'F') GROUP BY employee.id 

And I get proper data (NULL for people without records in JOB table

However if I reconstruct the same query into LEFT OUTER JOIN

SELECT "employee"."id", "employee"."created", SUM(job.minutes) AS job_minutes FROM users_employee AS employee INNER JOIN users_user AS users ON (employee.user_id = users.id) LEFT OUTER JOIN jobs_job AS job on employee.id = job.employee_id WHERE NOT ("users"."status" = 'F') AND job.job_date BETWEEN '2018-10-21' AND '2018-10-30' GROUP BY employee.id 

I get 112 rows instead of 142 in original query and only records that exist in Job table

4 Answers

All of the answers provided will help you fix your code. I just want to expand on why they'll work.

The SQL engine evaluates your FROM and your JOINs first, pulling your initial data set into memory. At that point, because you've used a LEFT OUTER JOIN, all the rows you're expecting are still there.

After that, it applies your WHERE clause. In this case, your WHERE clause includes job.job_date BETWEEN '2018-10-21' AND '2018-10-30', so at that point the engine filters off all of the rows that don't meet that criteria. This effectively makes the results from your LEFT JOIN exactly the same as the results you would get with an INNER JOIN.

The best answer, which has been suggested a number of times, is to move that filtering criteria to your ON clause. An alternative that will work, and that sort of demonstrates that the results are really all there to begin with, is to add the IS NULL possibility to your existing WHERE clause:

... WHERE NOT ("users"."status" = 'F') AND ( (job.job_date BETWEEN '2018-10-21' AND '2018-10-30') OR job.job_date IS NULL ) 

Using the condition on the JOIN, though, will perform better because you're pulling fewer records into memory in the first place.

Its because of this bite:

AND job.job_date BETWEEN '2018-10-21' AND '2018-10-30' 

You need to move it to the LEFT JOIN like:

LEFT OUTER JOIN jobs_job AS job on employee.id = job.employee_id AND job.job_date BETWEEN '2018-10-21' AND '2018-10-30' 

You need to move the condition on the last table to the on clause. The filtering on the second table stays in the where:

SELECT "employee"."id", "employee"."created", SUM(job.minutes) AS job_minutes FROM users_employee employee INNER JOIN users_user users ON employee.user_id = users.id LEFT OUTER JOIN jobs_job job ON employee.id = job.employee_id AND job.job_date BETWEEN '2018-10-21' AND '2018-10-30' WHERE "users"."status" <> 'F' GROUP BY employee.id; 

The logic works this way because left outer joins produce results with NULL values from the second table in the JOIN. These NULL values can be filtered out in the WHERE clause.

You need to use your other condition in ON cluase instead of where clause

SELECT "employee"."id", "employee"."created", SUM(job.minutes) AS job_minutes FROM users_employee AS employee INNER JOIN users_user AS users ON (employee.user_id = users.id) LEFT OUTER JOIN jobs_job AS job on employee.id = job.employee_id and "users"."status" <> 'F' AND job.job_date BETWEEN '2018-10-21' AND '2018-10-30' GROUP BY employee.id