SQL Server - NOT IN

Question

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   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    Table 1 has 5 records   When I change the query to IN  I get 1 record  What am I doing wrong with the NOT

User · Answer

SELECT  T1    FROM  Table1  AS  T1  WHERE  NOT EXISTS  SELECT      1 AS  C1      FROM  Table2  AS  T2      WHERE   T2   MAKE     T1   MAKE   AND           T2   MODEL     T1   MODEL   AND           T2   Serial Number     T1   Serial Number

User · Answer

SELECT     FROM Table1  WHERE MAKE MODEL  Serial Number   not in      select make model  serial number  from Table2       WHERE make model  serial number  IS NOT NULL    That worked for me  where make model  serial number  was one field name

User · Answer

One issue could be that if either make  model  or  serial number  were null  values would never get returned  Because string concatenations with null values always result in null  and not in    with null will always return nothing  The remedy for this is to use an operator such as IsNull make        IsNull Model       etc

User · Answer

You re probably better off comparing the fields individually  rather than concatenating the strings   SELECT t1       FROM Table1 t1         LEFT JOIN Table2 t2             ON t1 MAKE   t2 MAKE                 AND t1 MODEL   t2 MODEL                 AND t1  serial number    t2  serial number      WHERE t2 MAKE IS NULL

User · Answer

It s because of the way NOT IN works   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

User · Answer

Use a LEFT JOIN checking the right side for nulls   SELECT a Id FROM TableA a LEFT JOIN TableB on a Id   b Id WHERE b Id IS NULL   The above would match up TableA and TableB based on the Id column in each  and then give you the rows where the B side is empty

[sql] SQL Server - NOT IN

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to notin