sql - 如何使用左外连接而不是 in 编写此查询

Question

请帮助我使用左外连接而不是不在下面编写代码

SELECT * 
FROM A 
INNER JOIN B ON A.ID = B.ID AND A.NUM = B.NUM                          
WHERE (A.XX <> '') 
  AND (A.ID = 'MM') 
  AND A.XX NOT IN (SELECT NUM    
                   FROM A   
                   WHERE ID = 'YY')
  and (A.XX  <> '') 
  AND (A.ID = 'MM') 
  AND (A.XX) NOT IN (SELECT NUM    
                     FROM C   
                     WHERE ID = 'YY')

标签： sqlsql-server

Answer 1

只需进行左连接并检查连接的表是否为空（意味着没有找到匹配的行）。

此查询将返回与原始查询相同的数据，而不使用 NOT IN 运算符：

SELECT * 
FROM A 
     INNER JOIN B ON A.ID = B.ID AND  A.NUM = B.NUM
     LEFT JOIN A2 ON A2.ID = 'YY' AND A2.NUM = A.XX
     LEFT JOIN C  ON C.ID =  'YY' AND C.NUM = A.XX
WHERE A.XX <> '' AND A.ID = 'MM' AND A2.NUM IS NULL AND
      A.XX <> '' AND A.ID = 'MM' AND C.NUM IS NULL