NOT EXISTS ; sql

DB

solution to odd action at NOT IN (...only result)

(avoid record If NULL is included)

SELECT * FROM members;

 id |  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 | otani   |     | tottori        <<< age is NULL

SELECT * FROM members AS main_m
  WHERE NOT EXISTS
    (SELECT * FROM members AS sub_m
     WHERE main_m.age IS NULL OR main_m.home = 'tottori');

 id |  name   | age | home  
----+---------+-----+-------
  3 | itano   |  20 | osaka
  4 | kasai   |  20 | osaka
  5 | mishima |  21 | hyogo
  6 | imano   |  23 | hyogo

memo...

NOT EXISTS (... age IS NULL OR home = 'tottori');
.
EXISTS (... IS NOT NULL AND home <> 'tottori');

Refferred to

www.pursue.ne.jp