如何返回值，其中一个值是在两个表，但另一个是在只有一个?

Question

我有两个表，大致如下:

Table1:
 CUSTOMER OFFER SALES
 -------- ----- -----
 A          111     1
 A          222     1
 B          111     1
 B          222     1
 C          111     1

Table2:
 CUSTOMER OFFER
 -------- -----
 A          111
 A          222
 B          111

我想查询这些表，只返回在两个表中的客户，但也只返回客户&表2中不存在Offer

换句话说，结果应该是这样的:

 CUSTOMER OFFER SALES
 -------- ----- -----
 B          222     1

有人知道怎么做最好吗?

Answer 1

听起来像exists和not exists:

select t1.*
from table1 t1
where exists (select 1
              from table2 t2
              where t2.customer = t1.customer
             ) and
      not exists (select 1
                  from table2 t2
                  where t2.customer = t1.customer and t2.offer = t1.offer
                 );
  

Answer 2

IN和EXCEPT的用法如下:

SELECT T1.* FROM T1 JOIN
(SELECT CUSTOMER, OFFER FROM T1
 WHERE T1.CUSTOMER IN (SELECT T2.CUSTOMER FROM T2)
EXCEPT
SELECT CUSTOMER, OFFER FROM T2) T11
ON T1.CUSTOMER = T11.CUSTOMER AND T1.OFFER = T11.OFFER