NOT IN ; sql

DB PostgreSQL

check whether exists NULL when use NOT IN

(related to: return value of IN predicate ; sql - Red > Green > Refactor > Red)

create table member (
  memberid serial,
  name text,
  age  integer,
  home text,
  PRIMARY KEY(memberid)
);


SELECT * FROM members;
memberid |  name   | age |  home
---------+---------+-----+---------
1        | takagi  |  23 | tottori
2        | kinashi |  19 | tottori
3        | itano   |  20 | osaka
4        | kasai   |  20 | osaka
5        | mishima |  21 | hyogo
6        | imano   |  23 | hyogo



1.
SELECT * FROM members
  WHERE age
  NOT IN (SELECT age
          FROM members as m_sub
          WHERE m_sub.home = 'tottori');
memberid |  name   | age | home
---------+---------+-----+-------
3        | itano   |  20 | osaka
4        | kasai   |  20 | osaka
5        | mishima |  21 | hyogo

====================================================

SELECT * FROM members;
memberid |  name   | age |  home
---------+---------+-----+---------
1        | takagi  |  23 | tottori
2        | kinashi |  19 | tottori
3        | itano   |  20 | osaka
4        | kasai   |  20 | osaka
5        | mishima |  21 | hyogo
6        | imano   |  23 | hyogo
7        | shimano |     | tottori   << age IS NULL



2. (same query code as 1.)
SELECT * FROM members
  WHERE age
    NOT IN (SELECT age
            FROM members as m_sub
            WHERE m_sub.home = 'tottori');
memberid | name | age | home
---------+------+-----+------
(0 rows)

in 2. query will be

SELECT * FROM members
  WHERE NULL;

...

images

WHERE age
  NOT IN (SELECT age
     FROM members as m_sub
     WHERE m_sub.home = 'tottori');

...

WHERE age
  NOT IN (23, 19, NULL);

...

WHERE NOT (age = 23 OR age = 19 OR age = NULL);

...

WHERE NOT (age = 23) AND NOT (age = 19) AND NOT (age = NULL);

...

WHERE (age <> 23) AND (age <> 19) AND (age <> NULL);
  • age <> NULL is unknown (NULL)
  • at AND operation, if unknown is included, unknown (NULL) returns