SQL query return what's NOT in table

Question

Possible Duplicate:
SQL: find missing IDs in a table
getting values which dont exist in mysql table

Just wondering, is it possible to have a query that somehow tells you the values it did not find in a table?

So if I had a query SELECT * FROM mytable WHERE id IN (1,2,3,4,5,6,7,8,9) and only 2,3,6,7,9 was returned. I wouldd like to know that 1,4,5,8 were not found.

It will be a little hard to do a manual comparision, because this is going to be run over apx 2,000+ rows in a table (the id's are going to be provided via a csv file which can be copied into the query)

Thanks in advance

While that question is about missing values from a range, the result is the same -> only difference is you have an allready known list of id's you need to provide to the temp table. — Nanne, Jan 17 '13 at 12:30

score 1 · Accepted Answer · answered Jan 17 '13 at 12:29

1

This is probably silly, but what about creating a temporary table containing all your IDs from which you'll substract the result of your SELECT query ?

answered Jan 17 '13 at 12:29

halflings

1,540
1
13
34

Substracting sounds like something inefficient. I'd say `LEFT JOIN` the table. – Nanne Jan 17 '13 at 12:32
A temporary table and a JOIN to show values in table B that are not in table A, giving me what I'm looking for. – KS1 Jan 18 '13 at 16:39

score 1 · Answer 2 · answered Jan 17 '13 at 12:35

Untested, but in theory:

Table 1:
+----+-----+
| id | num |
+----+-----+

Table 2:
+----+
| id |
+----+

Table 1 contains the data you're looking for (and num is any field containing any data) Table 2 contains the IDs from the CSV

SQL:
SELECT COUNT(`Table1`.`num`) AS `count`
FROM `Table1`
LEFT JOIN `Table2` ON `Table1`.`id` = `Table2`.`id`
WHERE `count` = 0

score 0 · Answer 3 · answered Jan 17 '13 at 12:44

0

Quick solution, open your csv file, replace all comma's with " union select " put select in front of that line and use it as the first line of the query at the bottom query.

So 1,2,3 becomes

Select 1 union select 2 union select 3

Use this in the query below

    Select 1 union select 2 union select x -- replace this line with the line generated from your csv
    Except
    (
     Select id from mytable
    )

answered Jan 17 '13 at 12:44

Joost Aarts

673
9
19

1

MySQL doesn't support EXCEPT. – Mike Sherrill 'Cat Recall' Jan 17 '13 at 12:46

score 0 · Answer 4 · answered Jan 17 '13 at 12:56

What about:

SELECT *
   FROM (select 1 as f
               UNION
               SELECT 2 as f
               UNION
               SELECT 3 as f
               UNION
               SELECT 4 as f
               UNION
               SELECT 5 as f
               UNION
               SELECT 6 as f
               UNION
               SELECT 7 as f
               UNION
               SELECT 8 as f
               UNION
               SELECT 9 ) as s1
WHERE f NOT IN (SELECT id FROM mytable);

SQL query return what's NOT in table

4 Answers4