Use WHERE IN but report missing rows

Question

I have a requirement to report data back from a SQL table based on a list of key values (EIBItemID), I can just paste these into a query like this

select * 
from dbo.SupplyChain_1000469 
where EIBItemID in (421290,421316, and so on)

But if this returns fewer results than I am providing I need a quick way to report where the EIBItemID doesn't exist in the table, I could have thousands of these keys to search for but want to avoid creating a table each time for each list when it is easier to just paste them into a query.

I've tried putting the records into a temp table and then doing a join like this

DROP TABLE #EIBItemIDs

CREATE TABLE #EIBItemIDs (EIBItemID INT)

INSERT INTO #EIBItemIDs (EIBItemID) 
VALUES (00481771), (00481772), (00481773)

SELECT v.EIBItemID
FROM #EIBItemIDs v
LEFT JOIN dbo.SupplyChain_1799 t ON t.EIBItemID = v.EIBItemID
WHERE t.EIBItemID IS NULL

To just find the missing rows but is there a way to use the first query but report for every row (with just nulls if the key doesn't exist)?

The left join with null check is the best way of finding the rows that don't exist. You could use the first query with the EXCEPT operator, but it won't be as fast as your second query — Sparrow, Jan 12 '18 at 16:28
@sparrow Wouldn't that mean putting my list into a table first though? — Colster, Jan 12 '18 at 16:43
If I understand correctly, you are entering your list into a temp table, just so you don't have to repeat entering it. I don't see anything wrong with it. Most likely, this list is also coming from another table, which can be defined by another select statement or a different join in your query. Button line is, the left outer join with NULL condition check, is the fastest way of getting the result. How you define your list of IDs is another matter. Is your concern around defining the list or the query itself? — Sparrow, Jan 12 '18 at 16:55
The list will typically be provided as a column in a spreadsheet, we can import these into SQL as a new table but it's quicker to just copy the column out into a query — Colster, Jan 15 '18 at 08:22

score 1 · Answer 1 · answered Jan 12 '18 at 16:46

There is no way using IN. But you don't have to use a separate table. SQL Server (which I assume you are using based on the syntax) allows you to create a table in the FROM clause:

select *
from (values (421290), (421316), . . .
     ) v(EIBItemID)
where not exists (select 1 from dbo.SupplyChain_1000469 sc where sc.EIBItemID = v.EIBItemID) ;

score 1 · Accepted Answer · answered Jul 23 '19 at 07:07

Ended up using a range to hold the lowest and highest number that I would have in my list and then joining that range so it reports the range and matches from the actual data or NULL if not found in the data. I used Habeeb's reply from this post to get the range table (How to generate a range of numbers between two numbers?)

DECLARE @min bigint, @max bigint
SELECT @Min=7208901 ,@Max=8008170
;
DROP TABLE #missingIDs

Create Table #missingIDs(
ItemID numeric(8,0)
)
INSERT INTO #missingIDs (ItemID)
SELECT TOP (@Max-@Min+1) @Min-1+row_number() over(order by t1.number) as ItemID
FROM master..spt_values t1 
CROSS JOIN master..spt_values t2
;
with results  (SCID, EIBItemID, SubmissionDate, [HandoverDate], uploaddate,[Product],[Format],[Class],[Postcode],[DPS],[Status],[MMD_FileName], [Spare8] ,[ManifestID],[barcode]) As
(
SELECT EIBItemID, SubmissionDate, [HandoverDate], uploaddate,
    [Product]
    ,[Format]
    ,[Class]
    ,[Postcode]
    ,[DPS]
    ,[Status]
    ,[MMD_FileName], [Spare8] ,[ManifestID] ,[barcode]
FROM SupplyChain_1000468 with (nolock)
)
select t.SCID,v.ItemID,t.SubmissionDate, t.[HandoverDate], t.uploaddate,t.[Product],t.[Format],t.[Class],t.[Postcode],t.[DPS],t.[Status],t.[MMD_FileName],t.[Spare8],t.[ManifestID],t.[barcode]
from #missingIDs v
left join results t on t.EIBItemID = v.ItemID
where v.itemid in (<list of itemids(comma separated)>
)
order by v.itemid asc

Use WHERE IN but report missing rows

2 Answers2