環境
- macOS High Sierraバージョン10.13.6
- PostgreSQL 9.6.10
- PSequel Version 1 (1.5.3)
- 使っているデータベース PostgreSQL Sample Database

基本の使い方

値を直接指定

構文

select * from {テーブル名} where {列名} IN ({値1}[, {値n}...]);
-- 否定版
select * from {テーブル名} where {列名} NOT IN ({値1}[, {値n}...]);

sample

select * from country where country in ('Japan','Zambia');
 country_id | country |     last_update     
------------+---------+---------------------
         50 | Japan   | 2006-02-15 09:44:00
        109 | Zambia  | 2006-02-15 09:44:00
(2 rows)

値を副問合せで指定

構文

select * from {テーブル名1} where {列名} IN (select {列名1にヒットさせたい列名2} from {テーブル名2} [where ...]);
-- 否定版
select * from {テーブル名1} where {列名1} NOT IN (select {列名1にヒットさせたい列名2} from {テーブル名2} [where ...]);

sample

select * from city where country_id in (select country_id from country where country in ('Japan','Zambia'));
 city_id |     city     | country_id |     last_update     
---------+--------------+------------+---------------------
      10 | Akishima     |         50 | 2006-02-15 09:45:25
     172 | Fukuyama     |         50 | 2006-02-15 09:45:25
     203 | Higashiosaka |         50 | 2006-02-15 09:45:25
     204 | Hino         |         50 | 2006-02-15 09:45:25
     205 | Hiroshima    |         50 | 2006-02-15 09:45:25
     224 | Isesaki      |         50 | 2006-02-15 09:45:25
     226 | Iwaki        |         50 | 2006-02-15 09:45:25
     227 | Iwakuni      |         50 | 2006-02-15 09:45:25
     228 | Iwatsuki     |         50 | 2006-02-15 09:45:25
     229 | Izumisano    |         50 | 2006-02-15 09:45:25
     253 | Kakamigahara |         50 | 2006-02-15 09:45:25
     256 | Kamakura     |         50 | 2006-02-15 09:45:25
     260 | Kanazawa     |         50 | 2006-02-15 09:45:25
     272 | Kitwe        |        109 | 2006-02-15 09:45:25
     276 | Koriyama     |         50 | 2006-02-15 09:45:25
     284 | Kurashiki    |         50 | 2006-02-15 09:45:25
     287 | Kuwana       |         50 | 2006-02-15 09:45:25
     331 | Matsue       |         50 | 2006-02-15 09:45:25
     338 | Miyakonojo   |         50 | 2006-02-15 09:45:25
     355 | Nagareyama   |         50 | 2006-02-15 09:45:25
     376 | Okayama      |         50 | 2006-02-15 09:45:25
     377 | Okinawa      |         50 | 2006-02-15 09:45:25
     380 | Omiya        |         50 | 2006-02-15 09:45:25
     382 | Onomichi     |         50 | 2006-02-15 09:45:25
     386 | Otsu         |         50 | 2006-02-15 09:45:25
     440 | Sagamihara   |         50 | 2006-02-15 09:45:25
     463 | Sasebo       |         50 | 2006-02-15 09:45:25
     474 | Shimonoseki  |         50 | 2006-02-15 09:45:25
     521 | Tama         |         50 | 2006-02-15 09:45:25
     547 | Tsuyama      |         50 | 2006-02-15 09:45:25
     552 | Ueda         |         50 | 2006-02-15 09:45:25
     555 | Urawa        |         50 | 2006-02-15 09:45:25
(32 rows)

複数条件を指定する使い方

構文

-- 値を直接指定
select * from {テーブル名} where ({列名1},{列名2}) IN (({値1-1},{値2-1}[, ({値1-n},{値2-n})...]);
-- 値を副問合せで指定
select * from {テーブル名} where ({列名1},{列名2}) IN (select {列名1にヒットさせたい列名1-1}, {列名2にヒットさせたい列名2-1} from {テーブル名2} [where ...]);

sample

-- country='Zambia' の country_id=109 を country_id=110 にしたので country='Zambia' はヒットしません。
select * from country where (country_id,country) in ((50,'Japan'),(110,'Zambia'));
 country_id | country |     last_update     
------------+---------+---------------------
         50 | Japan   | 2006-02-15 09:44:00
(1 row)

select * from city where (city_id,country_id) in ((select city_id,country_id where city_id < 200 and country_id=50));
 city_id |   city   | country_id |     last_update     
---------+----------+------------+---------------------
      10 | Akishima |         50 | 2006-02-15 09:45:25
     172 | Fukuyama |         50 | 2006-02-15 09:45:25

こういうのはだめ

-- 列2つに対して1つづつに副問合せすることはできない。
select * from city where (city_id,country_id) in ((select city_id where city_id < 200),(select country_id where country_id = 50));
ERROR:  operator does not exist: record = integer
LINE 1: select * from city where (city_id,country_id) in ((select ci...
                                                      ^

NULLの注意

IN句の条件は条件演算子や ANY でも書くことができる

3つのSQLは同じ結果となる

-- IN句で条件指定
select count(*) from customer where active in (0,1);
 count 
-------
   598
(1 row)

-- 条件演算子で条件指定
select count(*) from customer where active=0 or active = 1;
 count 
-------
   598
(1 row)

-- ANYで条件指定
select count(*) from customer where active = any (select active from customer where active <= 1);
 count 
-------
   598
(1 row)

IN句でnullがあっても is null は適用されないので結果にnullのデータは出ない

左辺の式がNULLを生じる場合、または右辺の値に等しいものがなくて少なくとも1つの右辺の行がNULLを持つ場合、IN構文の結果は偽ではなくNULLとなることに注意してください。これは、NULL値の論理的な組み合わせに対するSQLの標準規則に従うものです。
9.22. 副問い合わせ式 - 9.22.2. IN

日本語も英語も弱いので何を言っているかわかるようなわからないような感じなので試してみました。

/* 最初に active列 が null のデータを作っておく。 */
update customer set active = null where first_name = 'Mary';
UPDATE 1
-- null のデータが1件
select customer_id,first_name,active from customer where active is null;
 customer_id | first_name | active 
-------------+------------+--------
           1 | Mary       |       
(1 row)
-- active列には「null」「0」「1」がある。
select active from customer group by active;
 active 
--------
       
      0
      1
(3 rows)

-- active列 を IN句 で select
select customer_id,first_name,active from customer where active in (0);
 customer_id | first_name | active 
-------------+------------+--------
          16 | Sandra     |      0
          64 | Judith     |      0
         124 | Sheila     |      0
         169 | Erica      |      0
         241 | Heidi      |      0
         271 | Penny      |      0
         315 | Kenneth    |      0
         368 | Harry      |      0
         406 | Nathan     |      0
         446 | Theodore   |      0
         482 | Maurice    |      0
         510 | Ben        |      0
         534 | Christian  |      0
         558 | Jimmie     |      0
         592 | Terrance   |      0
(15 rows)

-- 左辺の式がNULLを生じる場合
select customer_id,first_name,active from customer where null in (0);
 customer_id | first_name | active 
-------------+------------+--------
(0 rows)

-- 右辺の値に等しいものがなくて少なくとも1つの右辺の行がNULLを持つ場合
select customer_id,first_name,active from customer where active in (null);
 customer_id | first_name | active 
-------------+------------+--------
(0 rows)
select customer_id,first_name,active from customer where active in (100,null);
 customer_id | first_name | active 
-------------+------------+--------
(0 rows)

IN句の条件は条件演算子や ANY でも書くことができるけど null の場合は違う

3つは違う結果となる

-- IN句で条件指定
select customer_id,first_name,active from customer where active in (0,null);
 customer_id | first_name | active 
-------------+------------+--------
          16 | Sandra     |      0
          64 | Judith     |      0
         124 | Sheila     |      0
         169 | Erica      |      0
         241 | Heidi      |      0
         271 | Penny      |      0
         315 | Kenneth    |      0
         368 | Harry      |      0
         406 | Nathan     |      0
         446 | Theodore   |      0
         482 | Maurice    |      0
         510 | Ben        |      0
         534 | Christian  |      0
         558 | Jimmie     |      0
         592 | Terrance   |      0
(15 rows)

-- 条件演算子で条件指定
select customer_id,first_name,active from customer where active=0 or active is null;
 customer_id | first_name | active 
-------------+------------+--------
          16 | Sandra     |      0
          64 | Judith     |      0
         124 | Sheila     |      0
         169 | Erica      |      0
         241 | Heidi      |      0
         271 | Penny      |      0
         315 | Kenneth    |      0
         368 | Harry      |      0
         406 | Nathan     |      0
         446 | Theodore   |      0
         482 | Maurice    |      0
         510 | Ben        |      0
         534 | Christian  |      0
         558 | Jimmie     |      0
         592 | Terrance   |      0
           1 | Mary       |       
(16 rows)

-- ANYで条件指定
select customer_id,first_name,active from customer where active = any (select active from customer where active = 0 or active is null);
 customer_id | first_name | active 
-------------+------------+--------
          16 | Sandra     |      0
          64 | Judith     |      0
         124 | Sheila     |      0
         169 | Erica      |      0
         241 | Heidi      |      0
         271 | Penny      |      0
         315 | Kenneth    |      0
         368 | Harry      |      0
         406 | Nathan     |      0
         446 | Theodore   |      0
         482 | Maurice    |      0
         510 | Ben        |      0
         534 | Christian  |      0
         558 | Jimmie     |      0
         592 | Terrance   |      0
(15 rows)

否定の場合も同じ

3つのSQLは同じ結果となる

-- NOT IN句で条件指定
select count(*) from customer where active not in (1);
 count 
-------
    15
(1 row)

-- 条件演算子で条件指定
select count(*) from customer where active <> 1;
 count 
-------
    15
(1 row)

-- ALLで条件指定
sample=# select count(*) from customer where active = all (select active from customer where active <> 1);
 count 
-------
    15
(1 row)
select count(*) from customer where active <> all (select active from customer where active = 1);
 count 
-------
    15
(1 row)

3つは違う結果となる

-- NOT IN句で条件指定
select count(*) from customer where active not in (1,null);
 count 
-------
     0
(1 row)

-- 条件演算子で条件指定
select count(*) from customer where active <> 1 and active is not null;
 count 
-------
    15
(1 row)

-- ALLで条件指定
select count(*) from customer where active = all (select active from customer where active <> 1 and active is not null);
 count 
-------
    15
(1 row)
select count(*) from customer where active <> all (select active from customer where active = 1 or active is null);
 count 
-------
     0
(1 row)

複数条件を指定する場合もnullは検索条件にできません

-- これを検索条件にする
select first_name,active from customer where active is null;
 first_name | active 
------------+--------
 Mary       |       
(1 row)

-- active=nullとなって何もヒットしない
select count(*) from customer where (first_name,active) in (select first_name,active from customer where active is null);
 count 
-------
     0
(1 row)

基本の使い方

値を直接指定

値を副問合せで指定

複数条件を指定する使い方

NULLの注意

IN句の条件は 条件演算子 や ANY でも書くことができる

IN句でnullがあっても is null は適用されないので結果にnullのデータは出ない

IN句の条件は 条件演算子 や ANY でも書くことができるけど null の場合は違う

否定の場合も同じ

複数条件を指定する場合もnullは検索条件にできません

出展

IN句の条件は条件演算子や ANY でも書くことができる

IN句の条件は条件演算子や ANY でも書くことができるけど null の場合は違う