IN with NULL or NULL

Question

Postgres is a database

Can I use a NULL value for an IN clause? Example:

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

I want to limit these four values.

I tried the above statement and it does not work, but it executes, but does not add entries with NULL id_fields.

I also tried to add an OR condition, but that just makes the request run and run endlessly.

 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?

Answer 1

The in statement will be parsed identically to field=val1 or field=val2 or field=val3 . Entering a null value will be compressed to field=null , which will not work.

( Comment Marc B )

I would do it for clairity

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

Answer 2

Your request failed due to operator priority . AND binds to OR !
You need a pair of parentheses, which is not a matter of "clarity", but a pure logical 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 ) 

Added parentheses prevent the binding of AND to OR . If there were no other WHERE conditions (no AND ), you do not need parentheses. The accepted answer is a bit misleading in this regard.

Answer 3

The question Daniel answered is completely right. I wanted to leave a note on NULLS. We must be careful about using the NOT IN statement if the column contains NULL values. You will not get any output if your column contains NULL values ​​and you use the NOT IN operator. Here is how it is explained here http://www.oraclebin.com/2013/01/beware-of-nulls.html , a very good article I came across and thought about sharing it.

Answer 4

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

To exclude null from the check. Given the null value in id_field, the coalesce function instead of null returns unix_null_value and adds' unik_null_value to the IN-list, the request will return messages where id_field is 1-3 or null.

Answer 5

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

Beware of NULLS .

Today I came across very strange query behavior when using the IN and NOT IN operators. Actually, I wanted to compare two tables and find out if a value exists from table b to table a or not, and find out its behavior if the column contains null values. So I just created an environment to test this behavior.

We will create the table table_a .

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

We will create the 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 run a query to check if a value exists in table_a , checking its value from table_b using the IN statement.

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

Run the following query to verify the absence.

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

The way out was as expected. Now we insert the null value in table_b and see how these 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 request behaved as expected, but what happened to the second request? Why didn’t we get any result, what was supposed to happen? Is there a difference in request? No.

The change is shown in table_b . We entered null in the table. But how is this going? Let divide the two queries into "AND" and "OR" .

First request:

The first request will be processed inside something like this. This way, null will not create a problem here, since my first two operands will either evaluate to true or false . But my third operand a = null will not evaluate to true and false . It will be evaluated only null .

 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 request:

The second request will be processed as shown below. Since we use the "AND" operator, and nothing but true in any of the operands will 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 select all not null values ​​from table_b when using the NOT IN operator.

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

Therefore, always be careful with the null values ​​in a column when using the NOT IN operator.

Beware of NULL !!