NULL values inside NOT IN clause

Whenever you use NULL you are really dealing with a Three-Valued logic.

Your first query returns results as the WHERE clause evaluates to:

3 = 1 or 3 = 2 or 3 = 3 or 3 = null
which is:
FALSE or FALSE or TRUE or UNKNOWN
which evaluates to
TRUE

The second one:

3 <> 1 and 3 <> 2 and 3 <> null
which evaluates to:
TRUE and TRUE and UNKNOWN
which evaluates to:
UNKNOWN

The UNKNOWN is not the same as FALSE
you can easily test it by calling:

select 'true' where 3 <> null
select 'true' where not (3 <> null)

Both queries will give you no results

If the UNKNOWN was the same as FALSE then assuming that the first query would give you FALSE the second would have to evaluate to TRUE as it would have been the same as NOT(FALSE).
That is not the case.

There is a very good [article on this subject on SqlServerCentral][1].

The whole issue of NULLs and Three-Valued Logic can be a bit confusing at first but it is essential to understand in order to write correct queries in TSQL

Another article I would recommend is [SQL Aggregate Functions and NULL][2].

[1]:

[To see links please register here]

[2]:

[To see links please register here]

this is for Boy:

select party_code
from abc as a
where party_code not in (select party_code
from xyz
where party_code = a.party_code);

this works regardless of ansi settings

SQL uses three-valued logic for truth values. The `IN` query produces the expected result:

```sql
SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE col IN (NULL, 1)
-- returns first row
```

***But adding a `NOT` does not invert the results:***

```sql
SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT col IN (NULL, 1)
-- returns zero rows
```

This is because the above query is equivalent of the following:

```sql
SELECT * FROM (VALUES (1), (2)) AS tbl(col) WHERE NOT (col = NULL OR col = 1)
```

Here is how the where clause is evaluated:

```none
| col | col = NULL⁽¹⁾ | col = 1 | col = NULL OR col = 1 | NOT (col = NULL OR col = 1) |
|-----|----------------|---------|-----------------------|-----------------------------|
| 1 | UNKNOWN | TRUE | TRUE | FALSE |
| 2 | UNKNOWN | FALSE | UNKNOWN⁽²⁾ | UNKNOWN⁽³⁾ |
```

Notice that:

1. The comparison involving `NULL` yields `UNKNOWN`
2. The `OR` expression where none of the operands are `TRUE` and at least one operand is `UNKNOWN` yields `UNKNOWN` ([ref][1])
3. The `NOT` of `UNKNOWN` yields `UNKNOWN` ([ref][1])

You can extend the above example to more than two values (e.g. NULL, 1 and 2) but the result will be same: if one of the values is `NULL` then no row will match.

[1]:

[To see links please register here]