Check which IDs from a set do not exists in a table

Question

I have a list of user IDs, like this:

757392,733602,749955,744304,746852,753904,755117,636163,564931,740787,751450,743799,643918,
749903,571888,30207,705953,749120,749001,749192,749978,750840,544228,702121,746246,383667,
558790,585628,592771,745818,749375,241209,749235,746860,748318,748016,748951,747321,748684,
748225,565375,748673,747869,748522,748335,744775,672229,578056,713127,740234,632608,711135,
746528,362131,742223,746567,745224,332989,439837,745418,673582,269584,742606,745135,746950,
476134,740830,742949,276934

I have a MySQL table users with the id field.

How do I check - using a query - which IDs of the ones I have do not exists in the users table?

This sounds like a simple problem to me, yet I couldn't find any example on StackOverflow which would address a fixed set of ID values.

This is not simple, because SQL does not process string lists very well. — Gordon Linoff, Jun 08 '17 at 16:58
At least I know why I spent the past hour looking for a solution - and failed. I guess I could create a dummy or temporary table with those IDs and make a LEFT JOIN between the new table and the `users` but this is a chore. Definitely an overkill. — lesssugar, Jun 08 '17 at 17:01
Create a temporary table out of this list. Then compare that table with your data set. — Strawberry, Jun 08 '17 at 18:28

score 2 · Answer 1 · answered Jun 08 '17 at 17:31

I didn't know the find_in_set() function and took a more handcrafted approach. Not that it makes any sense given the first answer, but I'll post it anyway:

SELECT id
FROM (
    SELECT '757392' AS id UNION
    SELECT '733602' UNION
    SELECT '749955' UNION
    SELECT '744304' UNION
    SELECT '746852' UNION
    SELECT '753904' UNION
    SELECT '755117' UNION
    SELECT '636163' UNION
    SELECT '564931' UNION
    SELECT '740787' UNION
    SELECT '751450' UNION
    SELECT '743799' UNION
    SELECT '643918' UNION
    SELECT '749903' UNION
    SELECT '571888' UNION
    SELECT '30207'  UNION   
    SELECT '705953' UNION
    SELECT '749120' UNION
    SELECT '749001' UNION
    SELECT '749192' UNION
    SELECT '749978' UNION
    SELECT '750840' UNION
    SELECT '544228' UNION
    SELECT '702121' UNION
    SELECT '746246' UNION
    SELECT '383667' UNION
    SELECT '558790' UNION
    SELECT '585628' UNION
    SELECT '592771' UNION
    SELECT '745818' UNION
    SELECT '749375' UNION
    SELECT '241209' UNION
    SELECT '749235' UNION
    SELECT '746860' UNION
    SELECT '748318' UNION
    SELECT '748016' UNION
    SELECT '748951' UNION
    SELECT '747321' UNION
    SELECT '748684' UNION
    SELECT '748225' UNION
    SELECT '565375' UNION
    SELECT '748673' UNION
    SELECT '747869' UNION
    SELECT '748522' UNION
    SELECT '748335' UNION
    SELECT '744775' UNION
    SELECT '672229' UNION
    SELECT '578056' UNION
    SELECT '713127' UNION
    SELECT '740234' UNION
    SELECT '632608' UNION
    SELECT '711135' UNION
    SELECT '746528' UNION
    SELECT '362131' UNION
    SELECT '742223' UNION
    SELECT '746567' UNION
    SELECT '745224' UNION
    SELECT '332989' UNION
    SELECT '439837' UNION
    SELECT '745418' UNION
    SELECT '673582' UNION
    SELECT '269584' UNION
    SELECT '742606' UNION
    SELECT '745135' UNION
    SELECT '746950' UNION
    SELECT '476134' UNION
    SELECT '740830' UNION
    SELECT '742949' UNION
    SELECT '276934') AS id_list
WHERE id NOT IN (
    SELECT id
    FROM users);

Nice. This one **works** as well. A bit cumbersome to build, but doable. — lesssugar, Jun 09 '17 at 08:18

score 1 · Accepted Answer · edited Jun 09 '17 at 08:04

This is an option:

SELECT ids.id
  FROM ( SELECT @i
              , substring(@string, @start, @end-@start) id
            FROM <BigTable>
               , ( SELECT @string := <YourStringOfIds>
                        , @start:=0
                        , @end:=0
                        , @i:=0
                        , @len:=length(@string)
                        , @n:=@len-length(replace(@string,',',''))+1
                 ) t
            WHERE (@i := @i+1) <= @n
              AND (@start := @end+1)
              AND (@loc := locate(',',@string,@start))
              AND @end := if(@loc!=0,@loc,@len+1)
       ) ids
    LEFT JOIN <BigTable> u
      ON u.id = ids.id
  WHERE u.id is null

BigTable can be any table whose number of rows >= number of ids in your string.

This solution is neat and the easiest one to use with a fixed, comma separated list of values. It's also fast (tested on a table with over 300k rows). Anyone interested in how it works should get familiar with [user-defined variables](https://dev.mysql.com/doc/refman/5.6/en/user-variables.html) in MySQL. — lesssugar, Jun 10 '17 at 20:37

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

You're looking for the IN clause with a negation. I.e. you can specify your list as the argument to the IN clause like so:

SELECT * FROM users 
WHERE id NOT IN ( 757392,733602,749955,744304,746852,753904,755117,636163,564931,740787,751450,743799,643918,749903,571888,30207,705953,749120,749001,749192,749978,750840,544228,702121,746246,383667,558790,585628,592771,745818,749375,241209,749235,746860,748318,748016,748951,747321,748684,748225,565375,748673,747869,748522,748335,744775,672229,578056,713127,740234,632608,711135,746528,362131,742223,746567,745224,332989,439837,745418,673582,269584,742606,745135,746950,476134,740830,742949,276934 );

UPDATE

My bad - I didn't read the question properly. So the correct way would be to go with UNIONs then outer join and filter by NULL, like this:

SELECT WantedIds.id
FROM users
    RIGHT JOIN (
        SELECT x.id
        FROM (
            SELECT '757392' AS id UNION
            SELECT '733602' UNION
            SELECT '749955' UNION
            SELECT '744304' UNION
            SELECT '746852' UNION
            SELECT '753904' UNION
            SELECT '755117' UNION
            SELECT '636163' UNION
            SELECT '564931' UNION
            .
            .
            .
        ) x
    ) WantedIds
    ON WantedIds.id = users.id
WHERE users.id IS NULL

score 1 · Answer 4 · answered Jun 09 '17 at 08:16

Create temporary table, then fill it

CREATE TABLE tmp (
  `id` INT NOT NULL,
  PRIMARY KEY (`id`));

INSERT INTO tmp (id) VALUES (1),(2),(3),(4),(5),(6)

then make a query

SELECT tmp.id
FROM tmp 
LEFT JOIN users u ON u.id = tmp.id
WHERE tmp.id IS NULL

finally drop the table

DROP TABLE tmp

score 0 · Answer 5 · answered Jun 08 '17 at 17:06

0

You can use MySQL's find_in_set() function to check if a value exists in a commase separated list of values:

select * from your_table
where find_in_set(field_name,'757392,733602,749955,744304,746852,753904,755117,636163,564931,740787,751450')=0

answered Jun 08 '17 at 17:06

Shadow

33,525
10
51
64

Looks neat but, unfortunately, it gives me **all** the rows in the table `users`. I even tried to add some dummy IDs to the set. Also, I don't understand how this is supposed to work. What I need is the ID (from the given set) which *cannot* be found in the table. So, your query seems to try to `select` something that *shouldn't be there*. Am I getting this wrong? – lesssugar Jun 09 '17 at 07:57

Check which IDs from a set do not exists in a table

5 Answers5

UPDATE

Linked