MySQL - Determine data that exists in given list but not in table

Question

I have a table my_tab :

+--------+
| tab_id |
+--------+
|    1   |
|    2   |
|   ...  |
|   50   |
|   56   |
|   100  |
+--------+

*Note that I have no tab_id = 51, 52, 53, 54 or 55

Now, I have a list of IDs. 1, 2, 3, 4, ..., 50, 51, 52, 53, 54, 55, 56, ..., 100.

I want to know which id of the list of IDs does not exist in the table my_tab. I mean, the desired output should be 51, 52, 53, 54 and 55.

I thought about using a temporary table, store in it the list and do a Left JOIN with my_tab.

But, I don't have the right to do that (read only access).

https://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table — Ali Sheikhpour, Aug 24 '18 at 13:19
Use a subquery instead of a left join: `select 1 as id union all select 2 union all select ...` — juergen d, Aug 24 '18 at 13:20
@AliSheikhpour Thank you, but that thread suggest using temporary tables. I don't have the right to do that. — Hamza Abdaoui, Aug 24 '18 at 13:20
@juergend, in my real case, i have >3000 ID. I don't think using `SELECT 1 UNION ...` is a good solution ! — Hamza Abdaoui, Aug 24 '18 at 13:22
""Now, i have a list of IDs. 1, 2, 3, 4, ..., 50, 51, 52, 53, 54, 55, 56, ..., 100." is the number list always consecutive or can there be gaps? — Raymond Nijland, Aug 24 '18 at 13:34
From where List of other IDs is coming? Are they stored in another table? — Ankit Bajpai, Aug 24 '18 at 13:49
@AnkitBajpai, unfortunately no. I'm getting those IDs from another source. Not a DB. — Hamza Abdaoui, Aug 24 '18 at 13:50

D-Shih · Answer 1 · 2018-08-24T13:38:07.210

If your mysql version support cte you can try to use CTE Recursion make a full table the use NOT exists to check value didn't exist in my_tab table.

Here is a small sample for you.

Schema (MySQL v8.0)

create table my_tab(
    tab_id int
);

insert into my_tab values (1);
insert into my_tab values (2);
insert into my_tab values (3);
insert into my_tab values (4);
insert into my_tab values (5);
insert into my_tab values (9);
insert into my_tab values (10);
insert into my_tab values (20);

Query #1

WITH RECURSIVE  cte AS (
  SELECT MIN(tab_id) fromVal,MAX(tab_id) toVal
  FROM my_tab
  UNION ALL
  SELECT (fromVal+1),toVal
  FROM cte
  WHERE fromVal < toVal
)
SELECT fromVal
FROM cte c
WHERE NOT exists
(
  SELECT 1 
  FROM my_tab t1
  WHERE t1.tab_id = c.fromVal
);

| fromVal |
| ------- |
| 6       |
| 7       |
| 8       |
| 11      |
| 12      |
| 13      |
| 14      |
| 15      |
| 16      |
| 17      |
| 18      |
| 19      |

View on DB Fiddle

NOTE

mysql CTE RECURSIVE Default depth is 1000.

if you need to use more than 1000, you can try to set @@cte_max_recursion_depth value.

This method assumes the number list is always consecutive without gaps — Raymond Nijland, Aug 24 '18 at 13:35
@RaymondNijland Ok I edit my answer I use `MIN`, `MAX` function be start and end value then do CTE RECURSIVE — D-Shih, Aug 24 '18 at 13:41

score 1 · Answer 2 · answered Aug 24 '18 at 13:44

Here is another way without using CTEs -

select n+1 from my_tab
right outer join
(select (d1.n*10 + d2.n) n from (select 1 as n 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 0
 ) d1 cross join
 (select 1 as n 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 0
 ) d2) temp_num
on tab_id = n+1
where tab_id is null
order by n

Here is the fiddle - http://www.sqlfiddle.com/#!9/4645be/24

This method also assumes no gaps in the number list. – Raymond Nijland Aug 24 '18 at 13:46 — Raymond Nijland, Aug 24 '18 at 13:46

Raymond Nijland · Accepted Answer · 2018-08-24T14:06:15.837

want to know wich id of the list of IDs does not exist in the table my_tab. I mean, the desired output should be 51, 52, 53, 54 and 55.

i assume with the current data example and number list the desired output would be

| number |
|--------|
|      3 |
|      4 |
|     51 |
|     52 |
|     53 |
|     54 |
|     55 |

This query allows gaps in both the number list and table data as you can see.

Query

SELECT  
 number_list.number
FROM (

  SELECT 
   1 AS number
  UNION 
   SELECT 
     2 AS number   
  UNION
  SELECT 
   3 AS number
  UNION 
  SELECT 
     4 AS number 
  # ...
  # ...
  UNION
  SELECT 
    50 AS number
  UNION 
  SELECT 
    51 AS number 
  UNION
  SELECT 
    52 AS number
  UNION 
  SELECT 
    53 AS number 
  UNION
  SELECT 
    54 AS number
  UNION 
  SELECT 
    55 AS number 
  UNION
  SELECT 
    56 AS number
  UNION 
  # ...
  # ...  
  SELECT 
    100 AS number 

) AS number_list
LEFT JOIN 
 Table1
ON
 number_list.number = Table1.tab_id
WHERE
  Table1.tab_id IS NULL

Result

| number |
|--------|
|      3 |
|      4 |
|     51 |
|     52 |
|     53 |
|     54 |
|     55 |

see demo http://sqlfiddle.com/#!9/31956e/13

Update because off comment.

Don't you think if number list goes up to 1000, it's almost impossible to generate a list by using only Select or up to 10000 in any case about which I am pretty sure it will.

The more dynamic query requires a MySQL number generator and nested subindex functions to work.

The number generator query which generates number 1 to 100.
So the query only works for number list up to 100 numbers.
If you need more just add a new

CROSS JOIN ( SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 ) AS record_<number>

Query

SELECT 
 (@row_number := @row_number + 1) AS row_number
FROM (
 SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS record_1
CROSS JOIN (
 SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS record_2
CROSS JOIN (SELECT @row_number := 0) AS init_user_param

see demo http://sqlfiddle.com/#!9/31956e

Use nested SUBSTRING_INDEX functions to split items from a string in MySQL.

Query

SELECT  
 SUBSTRING_INDEX(
   SUBSTRING_INDEX(
      '1,2,3,4,50,51,52,53,54,55,56,100'
     ,',', 1
   )
   , ','
   , -1
 ) AS number

see demo http://sqlfiddle.com/#!9/340e01/528

Using the two methodes and getting the results you need.

Query

SELECT 
 number_list.number
FROM ( 

  SELECT
   DISTINCT 
   SUBSTRING_INDEX(
     SUBSTRING_INDEX(
        '1,2,3,4,50,51,52,53,54,55,56,100' #this is the number list
       ,',', number_generator.row_number
     )
     , ','
     , -1
 ) AS number
  FROM ( 
    SELECT 
     (@row_number := @row_number + 1) AS row_number
    FROM (
     SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
    ) AS record_1
    CROSS JOIN (
     SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
    ) AS record_2
    CROSS JOIN (SELECT @row_number := 0) AS init_user_param
  ) AS number_generator 
) AS number_list
LEFT JOIN 
 Table1
ON
 number_list.number = Table1.tab_id
WHERE
  Table1.tab_id IS NULL

see demo http://sqlfiddle.com/#!9/31956e/35

Don't you think if number list goes up to 1000, it's almost impossible to generate a list by using only Select or up to 10000 in any case about which I am pretty sure it will. — Ankit Bajpai, Aug 24 '18 at 13:47
@AnkitBajpai true give me some time to figure out a more dynamic method — Raymond Nijland, Aug 24 '18 at 13:50
@RaymondNijland, thank you for this answer. Despite that I don't like using this `SELECT .. UNION` statement, but I managed to create that part of the query dynamicaly (Used Sublime Text editing tools :P ) and now I'm getting what I want. — Hamza Abdaoui, Aug 24 '18 at 13:55
@AnkitBajpai "Don't you think if number list goes up to 1000, it's almost impossible to generate a list by using only Select or up to 10000 in any case about which I am pretty sure it wil" i've posted a upgrade to make it more dynamic — Raymond Nijland, Aug 24 '18 at 14:06
Ultimately you also landed with checking the numbers against series without gaps. — Ankit Bajpai, Aug 24 '18 at 15:28
"Ultimately you also landed with checking the numbers against series without gaps." Not completely true @AnkitBajpai the number string `"1,2,3,4,50,51,52,53,54,55,56,100"` used in the dynamic query has "gaps" the MySQL number generator in combination with the nested substring_idnex functions splits that string. The MySQL number generator is indeed a number generator series without gaps. — Raymond Nijland, Aug 24 '18 at 15:51

sinecospi · Answer 4 · 2019-09-08T07:51:16.340

The key to this example using MySql 8 is not in :

drop table if exists tableA ;
create table tableA (tabId int) ;
insert into tableA (tabId) values
    (1), (2), (3), (4), (22), (19), (50), (51), (52), (53), (54), (55), (56), (88), (76), (100) ;


drop table if exists tableB ;
create table tableB (tabId int) ;
insert into tableB (tabId) values
    (1), (5), (3), (4), (22), (19), (50), (56), (88), (76), (100) ;

select *
    from tableA
    where tableA.tabId not in (select tabId from tableB) ;

Produces:

There are also some similar uses of not in ... in this thread: SQL: find missing IDs in a table

EDIT: A couple of related examples:

-- ----------------------------------------------------------------
-- Example 2: Which Id's are in which table
--
select *
    from
    (
        select distinct tabId from tableA
        union
        select distinct tabId from tableB
    ) as unionAB
    left join tableA on unionAB.tabId = tableA.tabId
    left join tableB on unionAB.tabId = tableB.tabId
    order by unionAB.tabId
;

/*
tabId |tabId |tabId |
------|------|------|
     1|     1|     1|
     2|     2|[NULL]|
     3|     3|     3|
     4|     4|     4|
     5|[NULL]|     5|
    19|    19|    19|
    22|    22|    22|
    50|    50|    50|
    51|    51|[NULL]|
    52|    52|[NULL]|
    53|    53|[NULL]|
    54|    54|[NULL]|
    55|    55|[NULL]|
    56|    56|    56|
    76|    76|    76|
    88|    88|    88|
   100|   100|   100|
*/


-- ----------------------------------------------------------------
-- Example 3: Which Id's are missing from both tables
--
-- Determine the start/end of the range of Id's
set @startId = least((select min(tabId) from tableA), (select min(tabId) from tableB)) ;
set @endId = greatest((select max(tabId) from tableA), (select max(tabId) from tableB)) ;
select @startId, @endId ;

/*
@startId|@endId|
--------|------|
       1|   100|
*/

-- Build the full list of Id's from min to max (no gaps), using a "Common Table Expression"
-- This is based on @Brad's post in Stackoverflow: https://stackoverflow.com/questions/2157282/generate-days-from-date-range
with recursive lstIds as 
(
    select @startId as 'tabId'
    union
    select lstIds.tabId + 1 as 'tabId' 
        from lstIds
        where lstIds.tabId < @endId
)   -- no trailing ';' needed/allowed
-- Extract the new data so that it can be used
    -- Note: this 'select' needs to be imediately after the 'with' block
select *
    from lstIds
    -- Join the other tables into the full list
    left join tableA on lstIds.tabId = tableA.tabId
    left join tableB on lstIds.tabId = tableB.tabId
    -- where isnull(tableA.tabId) and isnull(tableB.tabId)  -- uncomment this line to see only the Id's that don't have a match in both tables
    order by lstIds.tabId
;


/*
tabId |tabId |tabId |
------|------|------|
     1|     1|     1|
     2|     2|[NULL]|
     3|     3|     3|
     4|     4|     4|
     5|[NULL]|     5|
     6|[NULL]|[NULL]|
     7|[NULL]|[NULL]|
            { snipped repeated nulls }
    17|[NULL]|[NULL]|
    18|[NULL]|[NULL]|
    19|    19|    19|
    20|[NULL]|[NULL]|
    21|[NULL]|[NULL]|
    22|    22|    22|
    23|[NULL]|[NULL]|
    24|[NULL]|[NULL]|
            { snipped repeated nulls }
    48|[NULL]|[NULL]|
    49|[NULL]|[NULL]|
    50|    50|    50|
    51|    51|[NULL]|
    52|    52|[NULL]|
    53|    53|[NULL]|
    54|    54|[NULL]|
    55|    55|[NULL]|
    56|    56|    56|
    57|[NULL]|[NULL]|
    58|[NULL]|[NULL]|
            { snipped repeated nulls }
    74|[NULL]|[NULL]|
    75|[NULL]|[NULL]|
    76|    76|    76|
    77|[NULL]|[NULL]|
    78|[NULL]|[NULL]|
            { snipped repeated nulls }
    86|[NULL]|[NULL]|
    87|[NULL]|[NULL]|
    88|    88|    88|
    89|[NULL]|[NULL]|
    90|[NULL]|[NULL]|
            { snipped repeated nulls }
    98|[NULL]|[NULL]|
    99|[NULL]|[NULL]|
   100|   100|   100|
*/

MySQL - Determine data that exists in given list but not in table

4 Answers4