0

When I wan't to match a column that has some certain string values or is null, I assumed I can do something like this:

SELECT * FROM table_name WHERE column_name IN (NULL, 'someTest', 'someOtherTest');

But it does not return the columns where column_name set set to NULL. Is this anywhere documented? Why does it not work?

Karl Adler
  • 15,780
  • 10
  • 70
  • 88
  • 1
    Does this answer your question? [IN Clause with NULL or IS NULL](https://stackoverflow.com/questions/6362112/in-clause-with-null-or-is-null) – Sund'er Aug 08 '22 at 13:38

2 Answers2

1

You can't compare NULL values using = (which is what IN is doing).

Quote from the manual

Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL

You need to add a check for NULL explicitly:

SELECT * 
FROM table_name 
WHERE (column_name IN ('someTest', 'someOtherTest') OR column_name IS NULL);
0

NULL and empty string (i.e ' ') both are considered different in Postgres, unlike Oracle.

The query can be modified as:

SELECT * 
FROM table_name 
WHERE (column_name IN ('someTest', 'someOtherTest', '', ' ') OR 
column_name IS NULL);
Sonia Valeja
  • 57
  • 3