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

User · Answer

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

User · Answer

Your query fails due to operator precedence  AND binds before OR  You need a pair of parentheses  which is not a matter of  quot clarity quot   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

User · Answer

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

User · Answer

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

User · Answer

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 gt  create table table a   a number   Table created    We will create table table b   SQL gt  create table table b   b number   Table created    Insert some values into table a   SQL gt  insert into table a values  1   1 row created   SQL gt  insert into table a values  2   1 row created   SQL gt  insert into table a values  3   1 row created    Insert some values into table b   SQL gt  insert into table b values 4   1 row created   SQL gt  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 gt  select   from table a where a in  select   from table b            A ----------          3   Execute below query to check the non existence   SQL gt  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 gt  insert into table b values null   1 row created   SQL gt  select   from table a where a in  select   from table b            A ----------          3  SQL gt  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  lt  gt  3 and a  lt  gt  4 and a  lt  gt  null   a  lt  gt  3 is either true or false a  lt  gt  4 is either true or false a  lt  gt  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 gt  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

User · Answer

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

User · Answer

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

[sql] IN Clause with NULL or IS NULL

Examples related to sql

Examples related to postgresql

Examples related to null

Examples related to conditional-statements

Examples related to in-clause