IN Clause with NULL or IS NULL

Question

Postgres is the database

Can I use a NULL value for a IN clause? example:

SELECT *
FROM tbl_name
WHERE id_field IN ('value1', 'value2', 'value3', NULL)

I want to limit to these four values.

I have tried the above statement and it doesn't work, well it executes but doesn't add the records with NULL id_fields.

I have also tried to add a OR condition but this just make the query run and run with no end in sight.

SELECT *
FROM tbl_name
WHERE other_condition = bar
AND another_condition = foo
AND id_field IN ('value1', 'value2', 'value3')
OR id_field IS NULL

Any suggestions?

Urbani

An in statement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=null which won't work. – Fetter

the 2nd query should be right. what other things are in your where clause? – Viceregal

@Daniel A. White, updated to reflect the query with more conditions – Urbani

Answer 1

An in statement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=null which won't work.

(Comment by Marc B)

I would do this for clairity

SELECT *
FROM tbl_name
WHERE 
(id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL)

Viceregal

Ahh that did it, thanks! So wrapping the fields adds both conditions to the WHERE clause? Just for clarification – Urbani

@Phill - it was an order of operations problem. – Viceregal

Answer 2

Your query fails due to operator precedence. AND binds before OR!
You need a pair of parentheses, which is not a matter of "clarity", but pure logic necessity.

SELECT *
FROM   tbl_name
WHERE  other_condition = bar
AND    another_condition = foo
AND   (id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL);

The added parentheses prevent AND binding before OR. If there were no other WHERE conditions (no AND) you would not need additional parentheses. The accepted answer is misleading in this respect.

Transferor

Answer 3

SELECT *
FROM tbl_name
WHERE coalesce(id_field,'unik_null_value') 
IN ('value1', 'value2', 'value3', 'unik_null_value')

So that you eliminate the null from the check. Given a null value in id_field, the coalesce function would instead of null return 'unik_null_value', and by adding 'unik_null_value to the IN-list, the query would return posts where id_field is value1-3 or null.

Lookeron

A little explanation would be helpful to future readers. How does your code help solve the issue? – Pettus

From documentation: The COALESCE() function returns the first non-null value in a list. So when id_field is null then 'unik_null_value' would be returned and query return the correct record. – Bohman

Answer 4

The question as answered by Daniel is perfctly fine. I wanted to leave a note regarding NULLS. We should be carefull about using NOT IN operator when a column contains NULL values. You won't get any output if your column contains NULL values and you are using the NOT IN operator. This is how it's explained over here http://www.oraclebin.com/2013/01/beware-of-nulls.html , a very good article which I came across and thought of sharing it.

Rodriquez

Not sure how many of use have experienced this problem, but I had this exact issue mixing nulls and NOT IN operator and was scwabbling over it for the past two hour. THIS is the perp... – Everrs

The link is dead. – Avlona

The like is alive now. – Dandify

Answer 5

Note: Since someone claimed that the external link is dead in Sushant Butta's answer I've posted the content here as a separate answer.

Beware of NULLS.

Today I came across a very strange behaviour of query while using IN and NOT IN operators. Actually I wanted to compare two tables and find out whether a value from table b existed in table a or not and find out its behavior if the column containsnull values. So I just created an environment to test this behavior.

We will create table table_a.

SQL> create table table_a ( a number);
Table created.

We will create table table_b.

SQL> create table table_b ( b number);
Table created.

Insert some values into table_a.

SQL> insert into table_a values (1);
1 row created.

SQL> insert into table_a values (2);
1 row created.

SQL> insert into table_a values (3);
1 row created.

Insert some values into table_b.

SQL> insert into table_b values(4);
1 row created.

SQL> insert into table_b values(3);
1 row created.

Now we will execute a query to check the existence of a value in table_a by checking its value from table_b using IN operator.

SQL> select * from table_a where a in (select * from table_b);
         A
----------
         3

Execute below query to check the non existence.

SQL> select * from table_a where a not in (select * from table_b);
         A
----------
         1
         2

The output came as expected. Now we will insert a null value in the table table_b and see how the above two queries behave.

SQL> insert into table_b values(null);
1 row created.

SQL> select * from table_a where a in (select * from table_b);
         A
----------
         3

SQL> select * from table_a where a not in (select * from table_b);

no rows selected

The first query behaved as expected but what happened to the second query? Why didn't we get any output, what should have happened? Is there any difference in the query? No.

The change is in the data of table table_b. We have introduced a null value in the table. But how come it's behaving like this? Let's split the two queries into "AND" and "OR" operator.

First Query:

The first query will be handled internally something like this. So a null will not create a problem here as my first two operands will either evaluate to true or false. But my third operand a = null will neither evaluate to true nor false. It will evaluate to null only.

select * from table_a whara a = 3 or a = 4 or a = null;

a = 3  is either true or false
a = 4  is either true or false
a = null is null

Second Query:

The second query will be handled as below. Since we are using an "AND" operator and anything other than true in any of the operand will not give me any output.

select * from table_a whara a <> 3 and a <> 4 and a <> null;

a <> 3 is either true or false
a <> 4 is either true or false
a <> null is null

So how do we handle this? We will pick all the not null values from table table_b while using NOT IN operator.

SQL> select * from table_a where a not in (select * from table_b where b is not null);

         A
----------
         1
         2

So always be careful about NULL values in the column while using NOT IN operator.

Beware of NULL!!

Electrodynamometer

Excellent answer. I have just discovered the problem in my work. Very very strange behavior of Oracle. – Selfappointed

Answer 6

I know that is late to answer but could be useful for someone else You can use sub-query and convert the null to 0

SELECT *
FROM (SELECT CASE WHEN id_field IS NULL 
                THEN 0 
                ELSE id_field 
            END AS id_field
      FROM tbl_name) AS tbl
WHERE tbl.id_field IN ('value1', 'value2', 'value3', 0)

Hellenhellene

This is similar to Ove Halseth's answer using coalesce. – Her

Answer 7

EASY: This would give TRUE and null values:

SELECT *
FROM tbl_name
WHERE 
id_field IN ('value1', 'value2', 'value3') IS NOT FALSE;

Pheon

Answer 8

Null refers to an absence of data. Null is formally defined as a value that is unavailable, unassigned, unknown or inapplicable (OCA Oracle Database 12c, SQL Fundamentals I Exam Guide, p87). So, you may not see records with columns containing null values when said columns are restricted using an "in" or "not in" clauses.

Tractarianism

Answer 9

If you're field is a numerical type, here is the solution I've used successfully.

(campaignid::text in ($campaignid) or (campaignid is null and 'null' in ($campaignid)))

Tillandsia