I had this select on MySQL that was working, and returned 100 rows. I now have an identical table in a PostgreSQL DB, but I only get 5 rows instead of the entire 100; presumably, because a matching tourprice row only exists for 5 of the 100 rows.

Here's the query:

select *  from registration
left join tourprice on tourprice.customerstatus_id = registration.customerstatus_id 
         and tourprice.tour_id = registration.tour_id 
  where tourprice.deleted is NOT TRUE

Moving the where inside the join, I got the results I expected:

select *  from registration
left join tourprice on tourprice.customerstatus_id = registration.customerstatus_id 
         and tourprice.tour_id = registration.tour_id 
         and tourprice.deleted is NOT TRUE

I don't care about MySQL, just want to know more about PostgresSQL theory. Is it incorrect to use a WHERE condition for boolean fields that have NULL values (because their table's on the right side, and doesn't have a matching row)? or can I still use the WHERE clause, but using something like IS DISCTINCT ... AND IS NOT NULL or NOT DEFINED?

registration columns:

  • id
  • name
  • tour_id
  • customerstatus_id

tourprice columns:

  • id
  • tour_id
  • customerstatus_id
  • price

tour columns (not really needed for this query):

  • id
  • name
  • 1
    @stackdave I didin't downvote but the votes were likely caused because people thought that the question was poor. Votes are always against the question, not the user who asks or answers. Consider them as an incentive to improve (the question). – ypercubeᵀᴹ Jun 15 at 22:05
  • 2
    stackdave -@ypercubeᵀᴹ and I have both made some edits to your question (since your acceptance of his answer validated his understanding of it). Obviously, you've got your answer, but hopefully this will help others to understand the question better when they read it, and thus to get to the answer provided as well. Note that if my changes seem too extreme, and need to be rolled back, the edits I made to ypercubeᵀᴹ's answer should also be rolled back, since I synchronized the wording in both posts. – RDFozz Jun 15 at 22:43
up vote 1 down vote accepted

I had this select on MySQL that was working, and returned 100 rows. I now have an identical table in a PostgreSQL DB, but I only get 5 rows instead of the entire 100; presumably, because a matching tourprice row only exists for 5 of the 100 rows.

No, you probably had this SELECT in MySQL - similar to your first but without the WHERE:

select *  from registration
left join tourprice 
     on  tourprice.customerstatus_id = registration.customerstatus_id 
     and tourprice.tour_id = registration.tour_id
-- where tourprice.deleted is NOT TRUE

The above query would work the same in MySQL and Postgres - giving you X rows in the result. X would likely be the number of rows in registration (assuming that the join doesn't produce more rows). Certainly not less than the number of rows in registration.


Now, when you add that WHERE clause, which has a column from the right table in an equality condition, it essentially converts the LEFT join to an INNER join. So the results will be less rows than the previous query - both in MySQL and Postgres.

select *  from registration
left join tourprice 
     on  tourprice.customerstatus_id = registration.customerstatus_id 
     and tourprice.tour_id = registration.tour_id
where tourprice.deleted is NOT TRUE

Now the specific case is a bit more complex because your tourprice.deleted IS NOT TRUE is not an equality condition. It is equivalent to:

(tourprice.deleted IS FALSE) OR (tourprice.deleted IS NULL)

so the LEFT JOIN is converted to an inner-join + anti-semijoin.

Therefore if only 5 rows (from the 100) pass the condition tourprice.deleted IS NOT TRUE, the result you get is expected.


Moving the where inside the join, I got the results I expected:

select *  from registration
left join tourprice 
         on  tourprice.customerstatus_id = registration.customerstatus_id 
         and tourprice.tour_id = registration.tour_id 
         and tourprice.deleted is NOT TRUE

Correct, now the query is again and acts as a LEFT JOIN. No result from registration is removed. Only the columns from tourprice show NULL values for the rows that do not pass the (triple) ON condition.


I don't care about MySQL, just want to know more about PostgresSQL theory. Is it incorrect to use a WHERE condition for boolean fields that have NULL values (because their table's on the right side, and doesn't have a matching row)? or can I still use the WHERE clause, but using something like IS DISCTINCT ... AND IS NOT NULL or NOT DEFINED?

If you want all the rows from the left table (registration here) in the results, then don't use any column of the right table in the WHERE clause. Columns from the right table should be used in the ON condition in that case.

Columns from the left table can be used in WHERE of course, restricting the results accordingly.

PostgreSQL Documentation Link: https://www.postgresql.org/docs/current/static/queries-table-expressions.html [..] This is because a restriction placed in the ON clause is processed before the join, while a restriction placed in the WHERE clause is processed after the join. That does not matter with inner joins, but it matters a lot with outer joins. [..]

  • thanks i needed the theorie: "a where convert a left join in inner join" – stackdave Jun 15 at 22:05
  • 1
    It's a bit more complicated than that in this case (see my last edit) – ypercubeᵀᴹ Jun 16 at 11:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.