mysql find numbers in query that are NOT in table

Question

Is there a simple way to compare a list of numbers in my query to a column in a table to return the ones that are NOT in the db?

I have a comma separated list of numbers (1,57, 888, 99, 76, 490, etc etc) that I need to compare to the number column in a table in my DB. SOME of those numbers are in the table, some are not. I need the query to return those that are in my comma separated list, but are NOT in the DB...

`select * from table where number not in (1,2,3,4,5)` should do what you are looking for if I understand you correctly — rpaskett, Jan 10 '15 at 03:47
no, I need the numbers from MY LIST that are NOT in the DB returned. There will be nothing from the DB returned — Stephen, Jan 10 '15 at 03:48
oh, I see. you are basically looking for a diff. you are better of doing the diff in your code, mysql will be slow at figuring this out for you — rpaskett, Jan 10 '15 at 03:51

David Faber · Accepted Answer · 2015-01-10T04:39:33.627

6

I would put the list of numbers to be checked in a table of their own, then use WHERE NOT EXISTS to check whether they exist in the table to be queried. See this SQLFiddle demo for an example of how this might be accomplished:

If you're comfortable with this syntax, you can even avoid putting into a temp table:

SELECT * FROM (
  SELECT 1 AS mycolumn
   UNION
  SELECT 2
   UNION
  SELECT 3
   UNION
  SELECT 4
   UNION
  SELECT 5
   UNION
  SELECT 6
   UNION
  SELECT 7
  ) a
 WHERE NOT EXISTS ( SELECT 1 FROM mytable b
                     WHERE b.mycolumn = a.mycolumn )

UPDATE per comments from OP

If you can insert your very long list of numbers into a table, then query as follows to get the numbers that are not found in the other table:

SELECT mynumber
  FROM mytableof37000numbers a
 WHERE NOT EXISTS ( SELECT 1 FROM myothertable b
                     WHERE b.othernumber = a.mynumber)

Alternately

SELECT mynumber
  FROM mytableof37000numbers a
 WHERE a.mynumber NOT IN ( SELECT b.othernumber FROM myothertable b )

Hope this helps.

edited Jan 10 '15 at 04:39

answered Jan 10 '15 at 03:49

David Faber

12,277
2
29
40

Unfortunately I don't think MySQL has a method of taking a comma-delimited list and turning it into a virtual table. In Oracle one might use `CONNECT BY`. – David Faber Jan 10 '15 at 03:56
looks like input is `CSV`. But clearly this the correct way of doing it. – Pரதீப் Jan 10 '15 at 04:26
Don't understand the use of UNION here, can you explain? my list of numbers is 37000 long btw, above I was just giving an example. – Stephen Jan 10 '15 at 04:33
Oh I am just using `UNION` to build a temp virtual table. If your list is 37,000 numbers long, put it in a table of its own (that would be the "a" in the query above). – David Faber Jan 10 '15 at 04:36
you are awesome, that solved it (after I imported the numbers to a new table and ran your query) – Stephen Jan 10 '15 at 04:55

score 1 · Answer 2 · edited May 23 '17 at 12:14

May be this is what you are looking for.

Convert your CSV to rows using SUBSTRING_INDEX. Use NOT IN operator to find the values which is not present in DB

Then Convert the result back to CSV using Group_Concat.

select group_concat(value) from(
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.a, ',', n.n), ',', -1) value
  FROM csv t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS 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) a
   ,(SELECT 0 AS 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) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.a) - LENGTH(REPLACE(t.a, ',', '')))) ou
where value not in (select a from db)

SQLFIDDLE DEMO

CSV TO ROWS referred from this ANSWER

@Stephen It is just a tally table to get 1 to 100 rows. you may have to increase it based on the max value in you `CSV` — Pரதீப், Jan 10 '15 at 04:35

score -1 · Answer 3 · answered Jan 10 '15 at 03:47

-1

You could use the 'IN' clause of MySQL. Maybe check this out IN clause tutorial

answered Jan 10 '15 at 03:47

Eric

9,870
14
66
102

how would I use the IN clause to return those numbers from my list that are not in the DB? – Stephen Jan 10 '15 at 03:48
I don't know how that would help... I need the numbers from MY LIST that are NOT in the DB returned. There will be nothing from the DB returned – Stephen Jan 10 '15 at 03:49

mysql find numbers in query that are NOT in table

3 Answers3

SQLFIDDLE DEMO