要查找缺少的值，SQL视图应该很简单

Question

我有两个带有普通id的表，table1有一个任务编号列，table2有documents列，每个任务都可以有多个文档。我正在寻找没有特定文档的所有任务号

假数据：

SELECT * FROM table1
id  tasknumber
1   3210-012
2   3210-022
3   3210-032

SELECT * FROM table2
id  document
1   revision1
1   SB
1   Ref
2   revision1
2   Ref
3   revision1
3   SB

但是，我怎样才能找到没有文件名为SB的任务号呢？

Answer 1

SELECT t1.tasknumber
FROM   table1 t1
LEFT   JOIN table2 t2 ON t2.id = t1.id AND t2.document = 'SB'
WHERE  t2.id IS NULL;

基本上有四种技术：

• Select rows which are not present in other table

Answer 2

select t1.tasknumber from table1 t1
where not exists 
     (select 1 from table2 t2 where t1.id = t2.id and t2.document = 'SB')

Answer 3

select
    tasknumber
from 
    table1
where
    not exists (select 1
                  from table2
                 where table1.id = table2.id
                       and table2.document = 'SB');

或

select
    tasknumber
from 
    table1
where
    id not in (select id
                 from table2 
                where document = 'SB');