Select id from array which is not in table

Question

I have a PHP coma separated string of ids like 1,2,3. I have a MySQL table which has id column

Table task_comments:

id
--
1
2

I want to get all the ids in the list which are not in the table. Here i would like to get the 3 as result.

Currently I am building a query like the following in PHP and it is working.

SELECT id FROM (
SELECT 1 id FROM DUAL
UNION ALL
SELECT 2 id FROM DUAL
UNION ALL
SELECT 3 id FROM DUAL
) a WHERE id NOT IN (SELECT id FROM task_comments);

I don't think this is a good way to do this. I want to know if there is a better method to do this, because if the list is big the union list will grow.

Thanks

PS: I can post the PHP code used to make the query also if needed.
PPS: I would like to know if there is better MySQL Query.

Answer 1

What you do is already the way to do it. There is no other way to create sets to reason over than the (pretty ugly) union construct. You can leave of the "from dual"s and replace the union alls with plain unions to make it shorter - although with a very large list union all might be the more performant solution as it does not sort for duplicate deletion.

SELECT id FROM (
SELECT 1 id
UNION
SELECT 2 id
UNION 
SELECT 3 id
) a WHERE id NOT IN (SELECT id FROM tasklist);

You might also want to have a look at temporary tables. That way you could create the set you need in a more natural way without hitting the limits of the large SQL involving unions.

CREATE TEMPORARY TABLE temp_table (id int);

INSERT INTO temp_table VALUES((1),(2),(3)); -- or just repeat for as many values as you might have from your app (batch insert?)

SELECT id FROM temp_table
WHERE id NOT IN (SELECT id FROM tasklist);

See more on temporary tables here.

Answer 2

Your string separated values in PHP:

$my_ids = "1,2,3";

SQL query in PHP:

$query = "SELECT id FROM task_comments WHERE id IN ($my_ids)";

This will return the id values from database which is 1 or 2 or 3. Then you can simply compare it.