![]() So always be careful about NULL values in the column while using NOT IN operator. SQL> select * from table_a where a not in (select * from table_b where b is not null) So how do we handle this? We will pick all the not null values from table table_b while using NOT IN operator. select * from table_a whara a 3 and a 4 and a null Since we are using an "AND" operator and anything other than true in any of the operand will not give me any output. The second query will be handled as below. select * from table_a whara a = 3 or a = 4 or a = null But my third operand a = null will neither evaluate to true nor false. So a null will not create a problem here as my first two operands will either evaluate to true or false. The first query will be handled internally something like this. But how come it's behaving like this? Let's split the two queries into "AND" and "OR" operator. We have introduced a null value in the table. ![]() The change is in the data of table table_b. 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. SQL> select * from table_a where a not in (select * from table_b) Let's see what we get if we try concatenating a NULL or a 1-character string to the values in our example table. NULLs and non-NULLs Another important difference between Oracle and PostgreSQL is when a NULL value is concatenated with a non-NULL character. SQL> select * from table_a where a in (select * from table_b) PostgreSQL's behaviour follows the standard in its treatment of NULL values. Now we will insert a null value in the table table_b and see how the above two queries behave. SQL> select * from table_a where a in (select * from table_b) Įxecute below query to check the non existence. ![]() 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. So I just created an environment to test this behavior. 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 contains null values. Null is returned only if all arguments are null SELECT coalesce (max (codeid) + 1, 1) FROM configentries WHERE configtableid. Today I came across a very strange behaviour of query while using IN and NOT IN operators. If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the rows fields are null, while IS NOT NULL is true. 4 Answers Sorted by: 105 try coalesce: The COALESCE function returns the first of its arguments that is not null. ![]() 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |