0

I created two tables using below command: 

select top 20 ord_pk into temporders from orders

select top 20 ord_pk into temporders1 from orders

Now I changed the not null constraint of temporders1 table to allow null values and updated last 5 rows of temporders1 table with NULL.

Now when I am executing below query:

select ord_pk
from temporders
where temporders.ord_pk not in
(
select ord_pk from temporders1
)

It is returning no row as Output.

When I deleted all rows from temporders1 table which are having null value, it is returning last 5 Ord_Pk from temporders table.

I am not able to figure out why it is doing so because I had earlier checked that there was no primary key or any other constraint on any table(temporders and temporders1).

Please help me out to understand the logic behind it.

Jesuraja
  • 3,774
  • 4
  • 24
  • 48
Shivam Gupta
  • 429
  • 4
  • 16
  • 2
    This has nothing to do with primary keys (nor identity columns) - you could remove all mention of them from here and the problem would be the same - if a subquery returns `NULL` values, then a `NOT IN()` consuming that subquery can only return `FALSE` or `UNKNOWN` – Damien_The_Unbeliever Jun 24 '14 at 07:49

1 Answers1

0

Check this StackOverflow question: NOT IN clause and NULL values

NULL values cannot be used in predicates without proper implementation (using the ISNULL() function, for example) because they always evaluates as UNKNOWN, and a predicate that contains a UNKNOWN result, it also evaluates as UNKNOWN.

You can prove what I'm saying by running this simple query:

SELECT CASE WHEN NULL=NULL  THEN 'equals'
            WHEN NULL<>NULL THEN 'not equal'
            WHEN NULL<NULL  THEN 'less than'
            WHEN NULL>NULL  THEN 'greater than'
            ELSE 'unknown' END

Note that what i said is valid only with the default value of the server property ANSI_NULLS. Setting ANSI_NULLS to OFF, will produce a different behaviour.

Community
  • 1
  • 1
mordack550
  • 492
  • 4
  • 14