MySQL get results of items not found in where clause-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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.

CodePudding user response：

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);