SQL select from data in query where this data is not already in the database?

Question

I want to check my database for records that I already have recorded before making a web service call.

Here is what I imagine the query to look like, I just can't seem to figure out the syntax.

SELECT * 
  FROM (1,2,3,4) as temp_table 
 WHERE temp_table.id 
LEFT JOIN table ON id IS NULL 

Is there a way to do this? What is a query like this called?

I want to pass in a list of id's to mysql and i want it to spit out the id's that are not already in the database?

Answer 1

SELECT * FROM table where id NOT IN (1,2,3,4)

Answer 2

Use:

   SELECT x.id
     FROM (SELECT @param_1 AS id
             FROM DUAL
           UNION ALL
           SELECT @param_2
             FROM DUAL
           UNION ALL
           SELECT @param_3
             FROM DUAL
           UNION ALL
           SELECT @param_4
             FROM DUAL) x
LEFT JOIN TABLE t ON t.id = x.id
    WHERE x.id IS NULL

If you need to support a varying number of parameters, you can either use:

• a temporary table to populate & join to
• MySQL's Prepared Statements to dynamically construct the UNION ALL statement

Answer 3

I would probably just do:

SELECT id
FROM table
WHERE id IN (1,2,3,4);

And then process the list of results, removing any returned by the query from your list of "records to submit".

Answer 4

How about a nested query? This may work. If not, it may get you in the right direction.

SELECT * FROM table WHERE id NOT IN (
        SELECT id FROM table WHERE 1
);

Answer 5

To confirm I've understood correctly, you want to pass in a list of numbers and see which of those numbers isn't present in the existing table? In effect:

SELECT Item
FROM IDList I
    LEFT JOIN TABLE T ON I.Item=T.ID
WHERE T.ID IS NULL

You look like you're OK with building this query on the fly, in which case you can do this with a numbers / tally table by changing the above into

SELECT Number
FROM (SELECT Number FROM Numbers WHERE Number IN (1,2,3,4)) I
    LEFT JOIN TABLE T ON I.Number=T.ID
WHERE T.ID IS NULL

This is relatively prone to SQL Injection attacks though because of the way the query is being built. It'd be better if you could pass in '1,2,3,4' as a string and split it into sections to generate your numbers list to join against in a safer way - for an example of how to do that, see http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

All of this presumes you've got a numbers / tally table in your database, but they're sufficiently useful in general that I'd strongly recommend you do.