0

Today I have seen one weird issue in PostgreSQL query. In which I have 2 tables Products and Parameters.

Purpose:

It's very simple query, I just want to list all products which are not there in parameter table.

Query: 

select 
    id 
from product_proudct
where active=True and 
      id not in 
      (
          select distinct product_id from parameter_view
      )

Issue:

It won't list anything if there is null value in parameter table in product_id column. I have verified by just removing null record from the parameter table by adding where clause.

select distinct product_id from parameter_view where product_id is not null

And then it's working fine but with null it won't work, it's really strange for me.

I would like to know the reason why subquery not working well with null ?

Emipro Technologies Pvt. Ltd.
  • 14,598
  • 5
  • 45
  • 75
  • https://www.postgresql.org/docs/current/static/functions-subquery.html#FUNCTIONS-SUBQUERY-NOTIN (the distinct in the subquery is useless by the way) –  Jun 29 '17 at 08:24
  • cant reproduce - please share structure and data, I believe you can narrow sample data to two rows - with null and without and put select result to the post?.. – Vao Tsun Jun 29 '17 at 08:35

0 Answers0