CODEWITHSUNDEEP
mysql
zhuguowei
zhuguowei

Reputation: 8477

how to filter non exist id of a table

there is a table have these data

select * from t;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
|  5 | e    |
|  6 | f    |
+----+------+

now I have a id list, I want to get which is not exist in this table, the effect I want is like this

select non_exist(id) from t where id in (1,2,5,7,10)
7
10

So if mysql have some convenient function could support this requirement?

Now my way is

 cat all_id
 1
 2
 5
 7
 10

cat all_id | tr '\n' ','|awk '{gsub(/,$/,"");print "mysql -uroot -p123456 test -e '\''select id from t where id in ("$0");'\''"}'|bash>exist_id

cat exist_id 
id 
1
2
5
comm <(sort all_id) <(sort exist_id) -2 -3 
10
7

Upvotes: 0

Views: 77

Answers (2)

Forbairt
Forbairt

Reputation: 11

Creating a temp table might be one way of going about it ... 

CREATE TEMPORARY TABLE tempy (id INT);
INSERT INTO tempy values (1),(2),(5),(7),(10);
select id from tempy WHERE id NOT IN (select id from t);

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

There is no such function in MySQL. You have to build an in-line table containing all the ids, then LEFT JOIN your table to it:

SELECT x.v
FROM (
   SELECT 1 AS v UNION ALL SELECT 2 UNION ALL SELECT 5 UNION ALL
   SELECT 7 UNION ALL SELECT 10) AS x
LEFT JOIN t ON x.v = t.id
WHERE t.id IS NULL

Predicate t.id IS NULL detects non-matching ids.

Upvotes: 1

Related Questions