SQL Server-不在 [英] SQL Server - NOT IN

问题描述

我需要基于make-model-serial数字组合构建一个查询，以显示表1中但表2中没有的记录.

I need to build a query that will show me records that are in Table 1, but that are not in Table 2, based on the make-model-serial number combination.

事实上，我知道有4条记录是不同的，但是我的查询总是回到空白.

I know for fact that there are 4 records that differ, but my query always comes back blank.

SELECT  *  
FROM Table1 WHERE MAKE+MODEL+[Serial Number] NOT IN
(SELECT make+model+[serial number] FROM Table2)

表1有5条记录.

当我将查询更改为IN时，我得到1条记录. NOT我怎么了?

When I change the query to IN, I get 1 record. What am I doing wrong with the NOT?

推荐答案

这是因为为避免这些头痛(在许多情况下，为了更快地查询)，我总是更喜欢不存在":

To avoid these headaches (and for a faster query in many cases), I always prefer NOT EXISTS:

SELECT  *  
FROM Table1 t1 
WHERE NOT EXISTS (
    SELECT * 
    FROM Table2 t2 
    WHERE t1.MAKE = t2.MAKE
    AND   t1.MODEL = t2.MODEL
    AND   t1.[Serial Number] = t2.[serial number]);

这篇关于SQL Server-不在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！