SQL如何查找不在另一个结果中的特定值

Question

TLDR；我想通过检查当前结果是否不存在于另一个结果中来筛选出结果

所以目前我有这2个查询

SELECT
  dosen.nama
FROM
  dosen AS dosen 
  INNER JOIN role as role
    ON dosen.NIP = role.NIP
WHERE
    role.Role != 'dosen'
    AND
    dosen.Nama LIKE '%em%'

导致

| nama |
| RATIH KEMALA DEWI, SP. M.Si | 
| GEMA PARASTI MINDARA, S.Si., M.Kom |

而且

SELECT 
  dosen.nama 
FROM 
  dosen AS dosen 
INNER JOIN 
  chats as chats ON dosen.NIP = chats.KonselorNIP 
WHERE 
  chats.ThreadStatus = 'OPEN'

结果（当前只有一个数据）

| nama |
| GEMA PARASTI MINDARA, S.Si., M.Kom |

就我的逻辑而言，这就是我想出的

SELECT
    dosen.nama
FROM
  dosen AS dosen 
  INNER JOIN role as role
    ON dosen.NIP = role.NIP
WHERE
    role.Role != 'dosen'
    AND
    dosen.Nama LIKE '%em%'
    AND NOT EXISTS
    ( SELECT dosen.nama FROM dosen AS dosen INNER JOIN chats as chats ON dosen.NIP = chats.KonselorNIP WHERE chats.ThreadStatus = 'OPEN')

但结果是空的，我所期待的是

| nama |
| RATIH KEMALA DEWI, SP. M.Si | 

我哪里做错了？

Answer 1

我认为您希望不存在:

SELECT d.nama
FROM dosen AS dosen 
WHERE NOT EXISTS (SELECT 1
                  FROM role r
                  WHERE d.NIP = r.NIP AND r.Role <> 'dosen'
                 ) AND
WHERE d.Nama LIKE '%em%';