CODEWITHSUNDEEP
sqlsqlite
chacham15
chacham15

Reputation: 14251

Why does a string match NULL in SQLite?

I have two tables: 

CREATE TABLE Foo(
    id      VARCHAR,
    data    VARCHAR
);

CREATE TABLE Bar(
    id      VARCHAR,
    foo_id  VARCHAR
);

When I run the query: SELECT * FROM Foo WHERE id='EB7AB72D7802D1146B72E3F1D761BFA2382BE102' AND id NOT IN (SELECT foo_id FROM Bar) I get no results.

However, if I change the query to SELECT * FROM Foo WHERE id='EB7AB72D7802D1146B72E3F1D761BFA2382BE102' AND id NOT IN (SELECT foo_id FROM Bar WHERE foo_id IS NOT NULL) I get the correct result.

The data in the tables is similar to what follows:

Foo:

10AF193A2C0465B6DAD69C4E9E2BF86321961434, blah
10AF193A2C0465B6DAD69C4E9E2BF86321961434, bloo
ED724BB8F46596A8F14B891DE4B6BE5ADB0B5903, fdsj
ED724BB8F46596A8F14B891DE4B6BE5ADB0B5903, fdsjio
EB7AB72D7802D1146B72E3F1D761BFA2382BE102, baz

Bar:

0009546DA32EFB77CB29F21E7399A8242866BBC1, 10AF193A2C0465B6DAD69C4E9E2BF86321961434
B732EA74329257213D7076F7672CCD8B1DF8E41B, (null)
7A6752AFAD1F7A887127A38937C15729673EF25F, ED724BB8F46596A8F14B891DE4B6BE5ADB0B5903

Upvotes: 0

Views: 102

Answers (1)

CharlesC
CharlesC

Reputation: 350

Comparisons with a null cannot be made. So in your first query the boolean result of that statement is 'unknown' instead of true or false.

Check out this blog post for nicely detailed explanation:
http://www.sqlbadpractices.com/using-not-in-operator-with-null-values/

Upvotes: 1

Related Questions