In Clause with Null or Is Null

IN Clause with NULL or IS NULL

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)

Understanding SQL IN clause with NULL values

Don't use not in with subqueries. Period. Use not exists; it does what you want:

select x.*
from x
where not exists (select 1 from y where y.y_name = x.x_name);

The problem is this. When you have:

x_name in ('a', 'b', null)

SQL actually returns NULL, not false. However, NULL is treated the same as false in where clauses (and when clauses but not for check constraints). So, the row gets filtered out.

When you negate this, either as:

not x_name in ('a', 'b', null)
x_name not in ('a', 'b', null)

The results is not NULL which is also NULL and everything gets filtered out.

Alas. The simplest solution in my opinion is to get in the habit of using not exists.

Not in clause on a column with Null values

Hive implements the NULL-safe comparison operator. So you can do:

select *
from table1
where not status <=> 'Rejected' ;

As for your question, it is a pretty basic question on what NULL means in databases. It doesn't mean "missing", it means "unknown". Almost all comparison operations return NULL when either operand is NULL -- the exceptions are operands (such as <=>, is not null and is null) that are specific designed to handle NULL values.

SQL IN () is including NULL values

If the subquery SELECT superior_emp_id FROM employee returns NULL for Michael Smith how is it that the IN() operator returns it in the final result set?

Short answer, it doesn't.

The subquery effectively returns the whole set of superior_emp_ids [NULL, 1, 1, 3, 3, 4, 4, 6, 6, 6, 4, 10, 10, 4, 13, 13, 4, 16, 16] for each row.

Your WHERE clause tests each emp_id to see if it is IN this set. And IN is basically a series of equals comparisons OR'd together.

Michael's emp_id is 1 and his row is returned because 1 = NULL OR 1 = 1 .... which can be written as FALSE OR TRUE .... returns TRUE.

You are correct in assuming that NULL doesn't equal anything, including NULL, so WHERE NULL IN (NULL, 1, FALSE, ... anything you like ...) will return FALSE. But that is not what's happening in your example.

N.B. To avoid any confusion it is much better to avoid NULL records on either side of an IN clause where possible as referenced by @Donal

How to use null value IN operator SQL

Your condition should be:

AND (TRP.[Transfer-from Code] IN ('X1','X2') OR TRP.[Transfer-from Code] IS NULL)

Check for NULLs only with the operators IS and IS NOT.

Never with = or IN because the result of a comparison to NULL is always NULL.

The NOT IN with NULL values dilemma in ORACLE SQL

This is not an issue with Oracle. This is how SQL is defined.

When the subquery returns a NULL value with NOT IN, then no rows match at all. For this reason, I strongly recommend always using NOT EXISTS instead:

WHEN NOT EXISTS (SELECT 1 FROM bst WHERE x.n = bst.p)
     THEN 'Leaf'

As a corollary, I usually use EXISTS instead of IN, even though it does not have this problem.

Why does this occur? NULL means that the value is unknown, not that the value is "missing" or something else.

So, if all the elements have values, this is easy to calculate:

1 NOT IN (1, 2)  --> false
3 NOT IN (1, 2)  --> true

However:

1 NOT IN (1, 2, NULL)  --> false 
3 NOT IN (1, 2, NULL)  --> NULL, because NULL could be equal to "3"

Basically, if any value is NULL, then NOT IN returns either "false" or NULL. Both "false" and NULL are treated the same in WHEN and WHERE.

NULL usage in WHERE IN SQL statement

If you set ANSI_NULLS OFF first, you can use IN (Null) Fine.

Comparisons to NULL can't be performed (=, >, < etc) with ANSI_NULLS ON

SET ANSI_NULLS OFF
Select 
RPAD(x.QUOTE_ID,20,' ')
from csa_sli_all.T_CONV_XREF_CUST_QUOTE x ,
        csa_sli_all.T_CONV_quote q
where   q.select_indicator is null and 
    q.QUOTE_ID = X.QUOTE_ID and 
    q.HOLD_CODE IN ('CAQ' , NULL )

Should work fine

NULL values inside NOT IN clause

Query A is the same as:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Since 3 = 3 is true, you get a result.

Query B is the same as:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

When ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.

When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row.

Why rows with NULL in column used in WHERE clause are omitted in results?

Is that bug in SQL server or I don't know something

Well, it's not a bug.

Think of NULL as a placeholder for "Unknown" and it will be clearer.

If I ask you to find me all the rows where the value is not 2 then you cannot return any NULL (unknown) value since you do not know that it is NOT 2.

If you want to include NULLs then the criteria should be

where value != 2 or value is null;

Using NULL in WHERE clause

This is not Postgres-specific behavior. It is how SQL is defined and how NULL values are defined in SQL.

The condition:

WHERE c > 0

is an affirmative condition. It keeps all rows where c > 0 evaluates to true.

When c is NULL, then c > 0 evaluates to NULL. NULL is not true (neither is it false), so c > 0 filters out NULL values. Almost all comparisons to NULL return NULL.

In addition:

WHERE NOT (c > 0)

also filters out NULL values, because NOT NULL is the same as NULL.

If you want to keep NULL values, I would recommend being explicit:

WHERE c > 0 OR c IS NULL

---

Related Topics