Hello, with the following schema and contents:
BEGIN TRANSACTION; CREATE TABLE a(id INTEGER); INSERT INTO a VALUES(1); INSERT INTO a VALUES(2); INSERT INTO a VALUES(3); CREATE TABLE b(id INTEGER); INSERT INTO b VALUES(NULL); INSERT INTO b VALUES(3); INSERT INTO b VALUES(4); INSERT INTO b VALUES(5); COMMIT; mysql, postgres, sqlite and mssql agree on: SELECT * FROM a WHERE id IN (SELECT id FROM b); yielding one row with id=3. However, on the query: SELECT * FROM a WHERE id NOT IN (SELECT id FROM b); mysql, postgres and mssql correctly return zero rows. SQLite however returns two rows, for id=1 and id=2. http://www.sqlite.org/nulls.html doesn't list it, so perhaps this has never come up before. Cheers, Peter _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users