Are IN and NOT IN mutually exclusive?

Question

I have two tables

Table 1

Column1 _______ 1 2 3 4 5 6 

table 2

 Column 1 ________ 4 NULL //This NULL value added after answering the question, to show the real problem 5 6 7 8 9 

This is an example. When I tried

 SELECT column1 FROM Table1 WHERE column1 IN (SELECT column1 FROM Table2) 

I got 4,5,6

WHEN

 SELECT column1 FROM Table1 WHERE column1 NOT IN (SELECT column1 FROM Table2) 

I did not get 1,2,3 but NULL .

In the real case, column1 of table1 is nvarchar (max), and column1 of table2 is varchar (50). However, I tried pouring both into varchar (50).

Answer 1

Check the IN documentation, in particular:

Any null values ​​returned by a subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values ​​with IN or NOT IN may produce unexpected results.

You have not shown them, but I am sure that you have at least one NULL value hidden in your data.

You can exclude NULL (s), and then NOT IN will work as you expected:

 SELECT column1 FROM Table1 WHERE column1 NOT IN (SELECT t2.column1 FROM Table2 t2 WHERE t2.column1 IS NOT NULL) 

---

IN and NOT IN are opposites within the wave side, but you have to consider SQL three-valued logic . Imagine we wrote IN using the form of expression

 a IN (1,2,NULL) 

Which is handled in the same way as:

 a = 1 OR a = 2 or a = NULL 

For any row, where a = 1, we have:

 TRUE OR TRUE OR UNKNOWN 

which is equal to TRUE . And for any row, where a = 3, let's say we have:

 FALSE OR FALSE OR UNKNOWN 

which is equal to UNKNOWN

Now consider NOT IN as follows:

 a NOT IN (1,2,NULL) 

Which is handled in the same way as:

 a != 1 AND a != 2 AND a != NULL 

For any row, where a = 1, we have:

 FALSE AND TRUE AND UNKNOWN 

This is FALSE . And for a = 3 we have:

 TRUE AND TRUE AND UNKNOWN 

This is UNKNOWN . Having NULL means that there is no way to get this AND chain to get TRUE .

Answer 2

This can happen if table 2 has null values. Use this query instead:

 select * from Table1 as t1 where not exists (select * from Table2 as t2 where t2.column1 = t1.column1); 

demo sql

Test request:

 -- Table2 doesn't have null values, works good SELECT column1 FROM Table1 WHERE column1 IN (SELECT column1 FROM Table2); SELECT column1 FROM Table1 WHERE column1 NOT IN (SELECT column1 FROM Table2); insert into Table2 select null; -- nothing returned by query, because of null values in Table2 SELECT column1 FROM Table1 WHERE column1 NOT IN (SELECT column1 FROM Table2); -- works good select * from Table1 as t1 where not exists (select * from Table2 as t2 where t2.column1 = t1.column1); 

This is because the three-valued SQL logic , see Damien_The_Unbeliever for a nice explanation . You can use a not null query, for example:

 SELECT column1 FROM Table1 WHERE column1 NOT IN (SELECT column1 FROM Table2 where column1 is not null); 

But I like exists better because it filters out null implicitly (just because = used).

As a complement, do not use such queries in your question without aliases (in fact, not aliases, but dotted notations, such as Table.column or Alias.column ), because you may have incorrect results . Always use dots for columns. So your query should look something like this:

 SELECT t1.column1 FROM Table1 as t1 WHERE t1.column1 NOT IN (SELECT t2.column1 FROM Table2 as t2 where t2.column1 is not null); 

Answer 3

This is the main reason you should avoid NOT IN, it is based on a three-way logic, YES / NO / UNKNOWN :-)

 col IN (1,2,NULL) is the logically equivalent to col=1 OR col=2 OR col=NULL col NOT IN (1,2,NULL) is the logically equivalent to col<>1 AND col<>2 AND col<>NULL 

Now any comparison with NULL is evaluated UNKNOWN, only "col IS (NOT) NULL" is correct.

If you have ORed conditions, UNKNOWN does not matter, but one UNKNOWN under ANDed conditions results in a final UNKNOWN.

When you add NULL to external table1 and delete NULL from table2, you will notice that this row is not in the IN / NOT IN answer sets.

The best workaround is to use EXISTS / NOT EXISTS instead, only YES / NO, because conditins evaluating UNKNOWN are ignored and simply treated as FALSE.