IN Clause with NULL or IS NULL

Question

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_field.

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?

• Marc B: 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.

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)

• Phill Pafford: Ahh that did it, thanks! So wrapping the fields adds both conditions to the WHERE clause? Just for clarification
• Daniel A. White: @Phill - it was an order of operations problem.

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.

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.

• showdev: A little explanation would be helpful to future readers. How does your code help solve the issue?
• amisiuryk: 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.

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.

• Govind Rai: 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...
• Dag Høidahl: The link is dead.
• always-a-learner: The like is alive now.

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!!

• oputyk: Excellent answer. I have just discovered the problem in my work. Very very strange behavior of Oracle.

Answer 6

EASY: This would give TRUE and null values:

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

Answer 7

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)

• user890332: This is similar to Ove Halseth's answer using coalesce.

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.

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)))

Answer 10

Checking for NULL using IN in this manner won't work, since the IN construct always returns NULL if the left hand side is NULL, regardless of what the list on the right hand side is.

In your case, the search condition id_field IN ('value1', 'value2', 'value3', NULL) will return NULL when id_field is NULL. Since the WHERE clause only keeps rows with a TRUE result, these rows will be excluded from the output.

This is explained in the PostgreSQL documentation:

9.25.1. IN

expression IN (value [, ...])

The right-hand side is a parenthesized list of expressions. The result is "true" if the left-hand expression's result is equal to any of the right-hand expressions. This is a shorthand notation for

expression = value1
OR
expression = value2
OR
...

Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand expression yields null, the result of the IN construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.

Here's a concrete example of the NULL behavior of IN:

SELECT 'a' IN ('a', 'b'), -- TRUE
 'c' IN ('a', 'b'), -- FALSE
 'a' IN ('a', 'b', NULL), -- TRUE
 'c' IN ('a', 'b', NULL), -- NULL
 NULL IN ('a', 'b'), -- NULL
 NULL IN ('a', 'b', NULL); -- NULL

On that note, since the list contains a NULL, the expression id_field IN ('value1', 'value2', 'value3', NULL) will return NULL (not FALSE) if id_field isn't one of 'value1', 'value2', or 'value3'. However, since the WHERE clause treats the non-TRUE values of FALSE and NULL identically, this distinction isn't observable when used as the search condition of a WHERE clause.