Find all strings that do not exist in table column

Question

I have a list of 338 strings like the following:

How do I get all of those strings that do not exist in my table/column: tblclients.clientID?

I've tried the following:

SELECT clientID FROM tblclients WHERE clientID NOT IN (
000100,
000834,
001855,
etc...)

But that only returns values from my table that do not exist in my list of strings. I need the opposite: strings from my list, that do not exist in the table. I'm wondering if this is possible with SQL or if I need to use Excel in some way...?

@PM77-1 Yes, I just put them into Excel and sorted them. I have the min and the max values (although they are really strings, not numbers, since they start with zero) — FastTrack, Aug 10 '15 at 19:48
Convert it to a number when you're checking the condition, and do a BETWEEN statement. — durbnpoisn, Aug 10 '15 at 19:53
make temporary table and select invert - not in the main table — splash58, Aug 10 '15 at 19:55
Does `select * from (values (1), (2), (3)) x(number)` work in MySQL? If so, that is a road you could go down. — Patrick87, Aug 10 '15 at 19:56
I guess you don't need that. You just need to check if record exists on Excel side. Do you? — Alex, Aug 10 '15 at 20:05

score 1 · Answer 1 · answered Aug 10 '15 at 19:56

1

If the numbers that you are testing are in YourNumbers then you can run a query like this:

SELECT clientID 
FROM YourNumbers y
LEFT JOIN TblClients t ON y.clientID = t.clientID
WHERE clientID IS NULL

answered Aug 10 '15 at 19:56

PM 77-1

12,254
20
64
106

The question states that the numbers are not in a table. – User15 Aug 10 '15 at 21:47
@User15 - I read it that OP wants to find numbers which are not in his main table. The numbers to compare have to be stored somewhere. My solution uses a temporary table for this purpose. – PM 77-1 Aug 10 '15 at 21:59

Find all strings that do not exist in table column

1 Answers1