mySQL "IN" function to Return the list of not found

Question

Just wanted to ask if this is possible or a way to determine the strings that is not on my table. for example

select name from table_person where name IN('name1','name2','name3')

scenario is name1 and name2 is available on my table but what I want to display is name3, since I want to know what are the things I haven't added.

Possible duplicate of [MySQL "NOT IN" query](http://stackoverflow.com/questions/1519272/mysql-not-in-query) — PM 77-1, Sep 01 '16 at 18:35
So lets call name1, name2 and name3 set B. Set A is all the data in your table_person. You want all records in set B where no record exists in set A? — xQbert, Sep 01 '16 at 18:35
No, because it still won't display the name3 in there since it is not on my table. What I wanted to achieved is that from the list of my arguments which of those are not present on my table. — Lego_blocks, Sep 01 '16 at 18:35

1000111 · Accepted Answer · 2016-09-01T19:06:05.853

2

Just playing around with the worst approach (may be).

_{Not Recommended}

SELECT 
 suppliedArgTable.name 
FROM 
(
 SELECT 'name1' AS name
 UNION 
 SELECT 'name2' 
 UNION 
 SELECT 'name3' 
) AS suppliedArgTable

LEFT JOIN 

table_person TP ON TP.name = suppliedArgTable.name 
WHERE TP.name IS NULL;

http://sqlfiddle.com/#!9/edcbe/2/0

edited Sep 01 '16 at 19:06

answered Sep 01 '16 at 18:38

1000111

13,169
2
28
37

yep, that's kinda like that, but what if I have 100 arguments to check and 14 of them is missing from my table. – Lego_blocks Sep 01 '16 at 18:57
I just showed the other way around. But I don't recommend this. Better do this kind of job in application logic. – 1000111 Sep 01 '16 at 19:00
I honestly can't think of a way around this. Your master set of data you want to compare against isn't in the database. So first have to some how get it there... either a union query load it into a table first and then do this. – xQbert Sep 01 '16 at 19:03
I agree. I don't advocate this @xQbert – 1000111 Sep 01 '16 at 19:13

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

NOT IN combined with reversing your query is a solution.

With the 'list' ('name1','name2','name3') in a (temporary) table e.g. temp_list and with the data in table_person the query would be:

select name from temp_list 
where name not in (
    select distinct(name) from table_person
)

distinct removes doubles. (see also MySQL: Select only unique values from a column)

edited May 23 '17 at 12:16

Community

1
1

answered Sep 01 '16 at 19:08

Bert

119
8

score 0 · Answer 3 · answered Apr 11 '22 at 13:29

SELECT name_field
FROM   (VALUES('name1'),
              ('name2'),
              ('name3'),
              ('name4'),
              ('name5')) V(name_field)
EXCEPT
SELECT name_field
FROM   name_table

You can use a temporary table to hold a list of all names and find non-matching names with EXCEPT.

mySQL "IN" function to Return the list of not found

3 Answers3