CODEWITHSUNDEEP
mysql
Lego_blocks
Lego_blocks

Reputation: 301

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')

Upvotes: 0

Views: 955

Answers (3)

Vivek Kumar
Vivek Kumar

Reputation: 2889

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.

Upvotes: 0

Bert
Bert

Reputation: 119

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)

Upvotes: 0

1000111
1000111

Reputation: 13519

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

Upvotes: 2

Related Questions