Select rows with missing value for each item

Question

I'm trying to make a report to find rows in a table, which have a mistake, a missing item order. I.e.

ID   Item  Order
----------------
 1     A       1
 2     A       2
 3     A       3
 4     B       1
 5     B       2
 6     C       2
 7     C       3
 8     D       1

Note, that Item "C" is missing row with Order index "1". I need to find all items, which are missing index "1" and start with "2" or other. One way I figured is this:

SELECT DIstinct(Item) FROM ITEMS as I
WHERE I.Item NOT IN (SELECT Item FROM Items WHERE Order = 1)

But surprisingly (to me), it does not give me any results even though I know I have such items. I guess, it first selects items wich are not in sub-select and then distincts them, but what I wanted to is select distinct Items and find which of them have no lines with "Order = 1".

Also, this code is to be executed over some 70 thousands of lines, so it has to be feasible (another way I can think of is a CURSOR, but that would be very slow and possibly unstable?).

Regards,

Oak

Answer 1

The idea is sound, but there is one tiny detail with NOT IN that may be problematic. That is, if the subquery after NOT IN results in any NULLs, the NOT IN is evaluated as if it were false. This may be the reason why you get no results. You can try NOT EXISTS, like in the other answer, or just

SELECT DISTINCT Item FROM ITEMS as I WHERE I.Item NOT IN (SELECT Item FROM Items WHERE Order = 1 AND Item IS NOT NULL)

Answer 2

You can use NOT EXISTS:

SELECT DISTINCT(i1.Item) FROM ITEMS i1
WHERE NOT EXISTS
(
    SELECT 1 FROM Items i2 
    WHERE i1.Item = i2.Item AND i2.[Order] = 1
)

NOT IN has it's issues, worth reading:

http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

The main problem is that the results can be surprising if the target column is NULLable (SQL Server processes this as a left anti semi join, but can't reliably tell you if a NULL on the right side is equal to – or not equal to – the reference on the left side). Also, optimization can behave differently if the column is NULLable, even if it doesn't actually contain any NULL values

because of this...

Instead of NOT IN, use a correlated NOT EXISTS for this query pattern. Always. Other methods may rival it in terms of performance, when all other variables are the same, but all of the other methods introduce either performance problems or other challenges.

Answer 3

You can find the missing orders using a HAVING clause. HAVING allows you to filter on aggregated records. In this case we are filtering for Items with a min Order in excess of 1.

The benefit of this approach over a sub query in the WHERE clause is SQL Server doesn't have to rerun the sub query multiple times. It should run faster on large datasets.

Example

/* HAVING allows us to filter on aggregated records. 
 */
WITH SampleData AS
    (
        /* This CTE creates some sample records 
         * to experiment with.
         */
        SELECT
            r.*
        FROM
            (
                VALUES
                    ( 1,     'A',       1),
                    ( 2,     'A',       2),
                    ( 3,     'A',       3),
                    ( 4,     'B',       1),
                    ( 5,     'B',       2),
                    ( 6,     'C',       2),
                    ( 7,     'C',       3),
                    ( 8,     'D',       1)
            ) AS r(ID, Item, [Order])
    )
SELECT
    Item,
    COUNT([Order])        AS Count_Order,
    MIN([Order])        AS Min_Order
FROM
    SampleData
GROUP BY
    Item
HAVING 
    MIN([Order]) > 1
;

Answer 4

Your query should work. The problem is probably that Item could be NULL. So try this:

SELECT Distinct(Item)
FROM ITEMS as I
WHERE I.Item NOT IN (SELECT Item FROM Items WHERE Order = 1 AND Item IS NOT NULL);

This is why NOT EXISTS is preferable to NOT IN.

I would do this, though, with an aggregation query:

select item
from items
group by item
having sum(case when [order] = 1 then 1 else 0 end) = 0;