Why do I receive an unexpected result set in SQLite when excluding rows via Sub Query?

At my work, we came across a bug in our SQLite query that was returning strange results. The query looked a little bit like this:

SELECT * FROM TableA  
WHERE TableA.ID NOT IN 
(
    SELECT TableA.ID   -- Table A
    FROM TableB        -- Table B
)

What this query seems to return is all the results in TableA which are not in TableB.

You can try this your self:

CREATE TABLE `TableA` (
    `ID`    INTEGER,
    PRIMARY KEY(ID)
)

CREATE TABLE `TableB` (
    `ID`    INTEGER,
    PRIMARY KEY(ID)
)

INSERT INTO TableA (ID)
values (1),(2),(3);

INSERT INTO TableB (ID)
values (1),(2)

In this case, the above query will just return 3 (as 3 is not in TableB).

What I would like to know is what is actually going on here. How is this query valid, and why is it returning the results in A, not in B?

Personally I would have expected this query to return no results. I tried the same setup and query in Microsoft SQL Server where calling the above query will return no results.

Thanks