在NOT IN MySQL中使用UNION sub查询

Question

我想在MySQL中的not null中使用联合子查询。查询如下所示。

SELECT s.student_id
     , s.student_name
  FROM students` s
 WHERE s.student_id NOT IN (
    SELECT student_id
      FROM free_students
     UNION
    SELECT student_id
    FROM paid_students
)

如果UNION子查询返回null，则查询结果也变为null。如何处理这种情况？

Answer 1

我会使用exists子句，实际上有两个：

SELECT s.student_id, s.student_name
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM free_students fs WHERE s.student_id = fs.student_id) AND
      NOT EXISTS (SELECT 1 FROM paid_students ps WHERE s.student_id = ps.student_id);