Sql-server – Why these two queries have different result

sql serversql-server-2012

Why these two queries have different result?

SELECT lf_transit_new.trayNo 
FROM lf_transit_new 
WHERE lf_transit_new.trayNo NOT IN
(
    SELECT lf_transit_new.trayNo 
    FROM lf_transit_new 
    WHERE lf_transit_new.trayNo IN
        (SELECT lf_transit_cage.trayNo
         FROM lf_transit_cage)
)

SELECT lf_transit_new.trayNo 
FROM lf_transit_new 
WHERE lf_transit_new.trayNo NOT IN
(SELECT lf_transit_cage.trayNo 
FROM lf_transit_cage)

First query show 561 rows but second query show none.

Best Answer

I suspect that lf_transit_cage.trayNo contains some NULL values, which causes problems for a NOT IN check but not an IN check. This is an issue that we've all hit at some point as it doesn't seem intuitive, you expect that the two checks will work more similarly.

The quick fix is to alter SELECT lf_transit_cage.trayNo FROM lf_transit_cage to SELECT lf_transit_cage.trayNo FROM lf_transit_cage WHERE lf_transit_cage.trayNo IS NOT NULL, and/or if there shouldn't be NULL values in that column fix the data and perhaps set the column to not accept NULLs.

With ANSI_NULLS set off NOT IN does work as you were expecting. See this answer for an explanation as to why in both cases.

Related Solutions

SQL Server Row Differences – How to Show Rows Different Between Two Tables or Queries

You don't need 30 join conditions for a FULL OUTER JOIN here.

You can just Full Outer Join on the PK, preserve rows with at least one difference with WHERE EXISTS (SELECT A.* EXCEPT SELECT B.*) and use CROSS APPLY (SELECT A.* UNION ALL SELECT B.*) to unpivot out both sides of the JOINed rows into individual rows.

WITH TableA(Col1, Col2, Col3) 
     AS (SELECT 'Dog',1,1     UNION ALL 
         SELECT 'Cat',27,86   UNION ALL 
         SELECT 'Cat',128,92), 
     TableB(Col1, Col2, Col3) 
     AS (SELECT 'Dog',1,1     UNION ALL 
         SELECT 'Cat',27,105  UNION ALL 
         SELECT 'Lizard',83,NULL) 
SELECT CA.*
FROM   TableA A 
       FULL OUTER JOIN TableB B 
         ON A.Col1 = B.Col1 
            AND A.Col2 = B.Col2 
/*Unpivot the joined rows*/
CROSS APPLY (SELECT 'TableA' AS what, A.* UNION ALL
             SELECT 'TableB' AS what, B.*) AS CA     
/*Exclude identical rows*/
WHERE  EXISTS (SELECT A.* 
               EXCEPT 
               SELECT B.*) 
/*Discard NULL extended row*/
AND CA.Col1 IS NOT NULL      
ORDER BY CA.Col1, CA.Col2

Gives

what   Col1   Col2        Col3
------ ------ ----------- -----------
TableA Cat    27          86
TableB Cat    27          105
TableA Cat    128         92
TableB Lizard 83          NULL

Or a version dealing with the moved goalposts.

SELECT DISTINCT CA.*
FROM   TableA A 
       FULL OUTER JOIN TableB B 
         ON EXISTS (SELECT A.*  INTERSECT  SELECT B.*) 
CROSS APPLY (SELECT 'TableA' AS what, A.* UNION ALL
             SELECT 'TableB' AS what, B.*) AS CA     
WHERE NOT EXISTS (SELECT A.*  INTERSECT  SELECT B.*) 
AND CA.Col1 IS NOT NULL
ORDER BY CA.Col1, CA.Col2

For tables with many columns it can still be difficult to identify the specific column(s) that differ. For that you can potentially use the below.

(though just on relatively small tables as otherwise this method likely won't have adequate performance)

SELECT t1.primary_key,
       y1.c,
       y1.v,
       y2.v
FROM   t1
       JOIN t2
         ON t1.primary_key = t2.primary_key
       CROSS APPLY (SELECT t1.*
                    FOR xml path('row'), elements xsinil, type) x1(x)
       CROSS APPLY (SELECT t2.*
                    FOR xml path('row'), elements xsinil, type) x2(x)
       CROSS APPLY (SELECT n.n.value('local-name(.)', 'sysname'),
                           n.n.value('.', 'nvarchar(max)')
                    FROM   x1.x.nodes('row/*') AS n(n)) y1(c, v)
       CROSS APPLY (SELECT n.n.value('local-name(.)', 'sysname'),
                           n.n.value('.', 'nvarchar(max)')
                    FROM   x2.x.nodes('row/*') AS n(n)) y2(c, v)
WHERE  y1.c = y2.c
       AND EXISTS(SELECT y1.v
                  EXCEPT
                  SELECT y2.v)

Sql-server – Are these two queries logically equivalent

Whether the two queries you posted are logically equivalent is irrelevant; you shouldn't use either of them. I will try to steer you away from a couple of things:

Whenever possible, try to avoid applying functions to columns. It's always as good, and mostly better, to keep those calculations against constants and not columns - this can destroy SARGability and render indexes on those columns useless. In this case, I much prefer query 2, especially if LogDateTime is indexed (or might ever be).
I don't like the shorthand date math and I recommend against it. Sure, it's faster to type, but try that with a DATE data type and you will get an ugly error. Much better to spell it out, e.g.:
```
WHERE LogInsertTime < DATEADD(DAY, -7, @DateTime);
```

Best Answer

Related Solutions

SQL Server Row Differences – How to Show Rows Different Between Two Tables or Queries

Sql-server – Are these two queries logically equivalent

Related Question