SQL文で not in をつなげると、結果がなくなってしまいました

Question

###実現したいこと
block、follow、favoriteというアクションが詰まったaction_datasというテーブルがあるのですが、そこからユーザーリストを取得するときに、block関係にあるユーザーを除外するSQLを書きたいです。

###発生している問題
「➀followとfavoriteのユーザーリスト」を取得するSQLは別の機会で教えて頂いたものがあります。これは問題ありません。

そして「➁block関係にあるユーザーリスト」を取得するSQLもなんとか自分で書けました。

しかし、➀から➁を除外すべくnot inでつなげると、何も取得されなくなってしまう状況です。

###検索対象となるテーブル（action_datas）
まず検索対象となるテーブルaction_datasの作成コードになります。

# action_datas の作成
CREATE TABLE action_datas
    (`action_id` int, `action_name` varchar(8), `action_date` varchar(4), `actor_id` int, `target_id` int, `target_type` varchar(6))
;
INSERT INTO action_datas
    (`action_id`, `action_name`, `action_date`, `actor_id`, `target_id`, `target_type`)
VALUES
    (1, 'block', '1月1日', 1, 2, 'user'),
    (2, 'follow', '2月2日', 1, 20, 'thread'),
    (3, 'follow', '3月3日', 2, 20, 'thread'),
    (4, 'favorite', '4月4日', 3, 2, 'user'),
    (5, 'follow', '5月5日', 3, 20, 'thread'),
    (6, 'favorite', '6月6日', 1, 3, 'user'),
    (7, 'block', '7月7日', 1, 4, 'user'),
    (8, 'follow', '8月8日', 4, 20, 'thread'),
    (9, 'block', '9月9日', 2, 1, 'user')
;

###➀followとfavoriteのユーザーリスト
そして下記は「➀followとfavoriteのユーザーリスト」です。

（サイト閲覧者が1で、閲覧中のスレッドが20という設定なので、actor_id=1でtarget_id=20となっています。）

ここからblock関係のユーザーの除外を実現したいので、コメントアウトしてる部分を実装できればと思っています。

        select follow.actor_id
             , max(
                  case when action.action_name='follow' then action.action_date end
               ) as follow_date
             , max(
                  case when action.action_name='favorite' then action.action_date end
               ) as favorite_date
        from  action_datas follow
              left join action_datas action
              on  follow.actor_id=action.target_id 
              and action.actor_id=1 and action.target_type='user'
        where follow.target_type='thread' and follow.target_id=20
        
        /*
        # 1とblock関係にあるユーザーを除外
        not in( 
          
        )
        */

        group by follow.actor_id 

###➁block関係にあるユーザーリスト
続いて自分で書いたものなのでおかしな点があるかもしれませんが、「➁block関係にあるユーザーリスト」が下記になります。

一応、先のテーブルaction_datasから、1をblockしている2と、1がblockしている4が取得できる状況です。

          # 1をblockしているユーザーを取得
          select block.actor_id
          from action_datas block
          where block.target_type='user' and block.target_id=1 and action_name='block'
          group by block.actor_id
          # 1がblockしているユーザーをunionする
          union
          select target_id
          from action_datas
          where action_name='block' and actor_id=1    

###試したこと
「➀followとfavoriteのユーザーリスト」のコメントアウトしてる部分に「➁block関係にあるユーザーリスト」を記載すると、何も取得できなくなってしまいました。

こちらのSQL fiddleで試していただけます。

そしてnullがある場合に取得できなくなるという例を見つけまして、「➀followとfavoriteのユーザーリスト」にはnullが入るからそれが原因かと思いis not nullを加えてみたのですがやはり何も取得できず、先に進めなくなってしまいました。

ご助力願えましたら幸甚に存じます。
どうぞ宜しくお願い致します。

###補足情報（FW/ツールのバージョンなど）
phpMyAdmin（4.8.5）で、MySQL（10.0.33-MariaDB）を使っています。

Answer 1

in で使用するサブクエリーはunionマージやgroup byするとコストが掛かるので、極力使用しない方がいいですね。結果は同じですから

select follow.actor_id
     , max(
          case when action.action_name='follow' then action.action_date end
       ) as follow_date
     , max(
          case when action.action_name='favorite' then action.action_date end
       ) as favorite_date
from  action_datas follow
      left join action_datas action
      on  follow.actor_id=action.target_id 
      and action.actor_id=1 and action.target_type='user'
where follow.target_type='thread' and follow.target_id=20
  and follow.actor_id not in(
        select case when target_id=1 then actor_id else target_id end
        from action_datas
        where target_type='user' and action_name='block' and 1 in (target_id, actor_id) 
      )
group by follow.actor_id

Comments

[ikatako]

質問のコードは
「not in()」
ではなく
「and follow.actor_id not in()」
でできたのですね。

しかしその変更だけではかっこ内のコストが高いとのことで、別の案をありがとうございます。仰るように致します。

恐れ入りますが、もしよろしければこの初心者に２点教えて頂けませんでしょうか。

【１点目】
「not in()」から「and follow.actor_id no in()」とすることは、どんな意味なのでしょうか？

【２点目】
not in() のかっこ内のコードについて下記のように翻訳してみたのですが、この最後の行がわかりません。
1 in (target_id, actor_id) は、どんな意味なのでしょうか

[ikatako]

/*
not in() のかっこ内のコードをコメントアウトで翻訳
*/

# target_id=1ならactor_idを表示し、1以外ならtarget_idを表示する
# つまり自分がblock対象ならその実行者を表示し、自分がblockする側ならblock相手を表示するということ
select
case when target_id=1 then actor_id else target_id end

# 検索テーブルは action_datas を指定する
from
action_datas

# action_datas の中から user かつ block かつ
where target_type='user' and action_name='block' and

# こちらの 1 in (target_id, actor_id) は、どんな意味なのでしょうか
1 in (target_id, actor_id)

[sazi]

in句の構文は通常、A in (B)です。(否定の場合は A not in (B))
質問の記述は、Aが指定されていないので文法エラーです。
AやBはカラムや値を指定します。Bは値の列挙が可能です。

1 in (target_id, actor_id)は　(target_id=1 or actor_id=1)と同義です。

[sazi]

サブクエリーは多用していると、遅い場面にあたることがありますから気を付けて下さい。
なぜMySQLのサブクエリは遅いのか。(http://nippondanji.blogspot.com/2009/03/mysql_25.html )
記事は古いので、以降のバージョンでは改善されているかもしれませんが。

[ikatako]

なるほど。少しずつですがわかってきました。いつもありがとうございます。