Records that are present IN the CSV string and are NOT IN the table?

Question

I've the following table

|ID | NodeID |
|---|--------|
|1  | N001   |
|2  | N006   |
|3  | N0010  |
|4  | N0015  |
|5  | N0016  |

And the following CSV string

'N001','N002','N003','N004','N005','N006','N007'

How can i find the NodeIDs that are present IN the CSV string and are NOT IN the table?

A MySQL solution is very different to a SQL Server solution because of how the CSV must be split. For SQL Server, this has been asked before http://stackoverflow.com/q/2647/27535 — gbn, Nov 28 '11 at 11:54

score 1 · Accepted Answer · answered Nov 28 '11 at 11:58

There are several options depending on your environment and data size. Here are my most common two...

The Way I Prefer...

Insert the CSV values into a table, then do all the work using SQL...

INSERT INTO myTempTable (itemValue) ......

SELECT * FROM myTempTable WHERE NOT EXISTS (SELECT * FROM dataTable WHERE NodeID = itemValue)
-- Or...
SELECT * FROM myTempTable LEFT JOIN dataTable ON NodeID = itemValue WHERE NodeID IS NULL

Another option avoids the temp table, but requires client side processing...

Ensure the CSV is alpha-numerically ordered.
Select all NodeIDs that ARE in the CSV, alpha-numerically ordered.
Filter out the values found in step 2, from the original set of values.

Step 2 can simply be done as...

SELECT * FROM dataTable WHERE NodeID IN (yourCSV) ORDER BY NodeID

Records that are present IN the CSV string and are NOT IN the table?

1 Answers1