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,933
21
68
111

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

score 0 · Answer 2 · answered Jun 16 '23 at 13:44

I have the same case here...

If the DB supports Common Table Expressions and there are not too many values to check:

create table reftable (
   pkey int not null primary key,
   name varchar(40)
);
insert into reftable values ( 101, 'aaaaa' );
insert into reftable values ( 103, 'bbbbbb' );

with checklist as
(
select 101 c1
 union
select 102 c1
 union
select 103 c1
 union
select 104 c1
)
select c1 from checklist
   where c1 not in (select pkey from reftable);

Still looking for a better solution...

Find all strings that do not exist in table column

2 Answers2