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

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.
mysql


Solution 1:[1]

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

Solution 2:[2]

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)

Solution 3:[3]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Community
Solution 3 Vivek Kumar

Related Questions