Thursday, October 16, 2008

Dangerous SQL usage of "NOT IN"

Here is example structure:

CREATE TABLE CUSTOMER
(
ID INTEGER NOT NULL,
FIRSTNAME VARCHAR NOT NULL,
LASTNAME VARCHAR NOT NULL
);
CREATE TABLE CARD
(
ID INTEGER NOT NULL,
CUSTOMERID INTEGER,
SOMEDATA VARCHAR DEFAULT 'ffff'
);
insert into card (id, customerid, somedata)
values (1, null, 'abcd');
insert into card (id, customerid, somedata)
values (2, null, 'efgh');
insert into card (id, customerid, somedata)
values (3, null, 'ijkl');
insert into customer (id, firstname, lastname)
values (1, 'John', 'Doe');
insert into customer (id, firstname, lastname)
values (2, 'Jane', 'Doe');
insert into customer (id, firstname, lastname)
values (3, 'Jim', 'Doe');
update card set customerid = 1 where id = 2;

SELECT
MIN (customer.ID)
FROM customer
WHERE customer.ID NOT IN
(SELECT customerId FROM card WHERE customerId IS NOT NULL)

Last SELECT statement returns 2 which is ok but if you remove this part "WHERE customerId IS NOT NULL" all you get is NULL.
Database recognizes NULL value as "unknown".
It can't tell "I'm sure there IS NO customer.ID in that SELECT." because there are unknowns!!!
:)

No comments: