比较SQL中的3个表,结果在A、B、而不是C中找到

有几种方法可以做到这一点,一种是使用 LEFT JOIN :

Select      *
From        TableA  A
Join        TableB  B   On  A.UserName = B.UserName
Left Join   TableC  C   On  A.UserName = C.UserName
Where       C.UserName Is Null;

另一种方法是使用 WHERE NOT EXISTS :

Select      *
From        TableA  A
Join        TableB  B   On  A.UserName = B.UserName
Where Not Exists
(
    Select  *
    From    TableC  C
    Where   C.UserName = A.UserName
)

你可以用两个子选项来绘制它们 IN 和 NOT IN

SELECT T1.UserName
  FROM Table1 T1
 WHERE T1.UserName     IN (SELECT UserName FROM Table2)
   AND T1.UserName NOT IN (SELECT UserName FROM Table3)

连接解决方案:

JOINs 例如:

     SELECT DISTINCT T1.UserName
       FROM Table1 T1
 INNER JOIN Table2 T2
         ON T1.UserName = T2.UserName
 LEFT OUTER JOIN Table3 T3
         ON T1.UserName = T3.UserName
      WHERE T3.UserName IS NULL

如果你愿意 EXISTS 你可以这样做:

SELECT T1.UserName
  FROM Table1 T1
 WHERE     EXISTS (SELECT 1 FROM Table2 T2 
                           WHERE T1.UserName = T2.UserName)
   AND NOT EXISTS (SELECT 1 FROM Table3 T3 
                           WHERE T1.UserName = T3.UserName)