MySQL get results of items not found in where clause

user_979331 0
Viewed 60

I have a query like so:

select id from users where id in (1,2,3,4,5)

I have a user with the ids 1,3 & 4. What I am trying to do is get the results of ids not found. Example if i have ids 1,3 & 4 in the table, I am expecting 2 & 5 to be returned as those ids do not exists. I hope this makes sense and is possible.

mysql
3 Answers

Yes, you can do that. Create a table and insert the IDs you wish to enumerate in it like so:

create table enumerate_ids (eid int not null primary key);
insert into enumerate_ids values (1),(2),(3),(4),(5);

Then, do a join with your users table to find which one does not exist.

select eid from enumerate_ids e
where not exists (
  select 1 from users where id = e.eid
)

Or, you can do this:

select eid
from enumerate_ids e
left join users u on u.id = e.eid
where u.id is null

Example: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=5e1e138aacf5f41a3d5d28e999993ccc

If you are doing all of this dynamically, you use your application code to create a temporary table, enter IDs you are wanting to enumerate or check against users table, run a query like above, get results and then remove the temporary table.

You could do this with a stored procedure as well, I'd think, so that you can pass it your values and it spits out the IDs that don't exist.

user_25545370

We can use WITH recursive to generate a set of incrementing id's and then perform not in operation on the user's table.

WITH recursive numbers AS (
    select 1 as id
   union all
   select id + 1
   from numbers
   where id < (select max(id) from user))
select id from numbers where id not in (select distinct(id) from user);
user_136120960

First create numbers views (or choose size you need):

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_4k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_16 hi;

CREATE OR REPLACE VIEW generator_64k
AS SELECT ( ( hi.n << 8 ) | lo.n ) AS n
     FROM generator_256 lo, generator_256 hi;

CREATE OR REPLACE VIEW generator_1m
AS SELECT ( ( hi.n << 16 ) | lo.n ) AS n
     FROM generator_64k lo, generator_16 hi;

And join it to the users table:

SELECT n AS unused_id 
FROM generator_16 number 
    LEFT JOIN users ON users.id=number.n 
WHERE users.id IS NULL AND number.n < (SELECT MAX(users.id) FROM test)

Result is rows with unused ids.

One sidenote tho; You wanted to return an not existing number higher than the max existing id, in the query above I maxed to the highest existing number. You can change that to a const if you like.

user_59248480
Related