Return items not found in database in Oracle I've been given a list of employee names (several hundred), and I need to look them up, and return their employee number.

However, the names given are not necessarily exactly as they appear in the database.  I therefore also need to know which ones it does not find, so we can do some further manual research to locate them and find out their employee id - but I cannot figure out how to also return the employees not found.

Example:

select name, emplid
from employees
where name in ('Smith,John','Brown,Jill','Doe,Jane')


If one of the names is 'Doe,Jane April') in the db, then I don't find her.

I am using Oracle, and I do not have the access to create tables.  Is there any way to do this?
You can use this approach as it demonstrated in this SQL fiddle:

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table cust (custname varchar2(20), custid int);
insert into cust values('Andrew', 1);
insert into cust values('John', 2);
insert into cust values('Mary', 3);


Query 1:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
)
select n.search, NVL(c.custid, 0) custid
from names n left join cust c 
on n.search = c.custname


Results:

| SEARCH | CUSTID |
|--------|--------|
| Andrew |      1 |
|   Mary |      3 |
|   Mark |      0 |


Query 2:

with names as (
select 'Andrew' AS search from dual
union all
select 'Mark' AS search from dual
union all
select 'Mary' AS search from dual
union all
select 'Jo' AS search from dual
)
select n.search, NVL(c.custid, 0) custid,
CASE WHEN c.custid IS NULL THEN 'not found'
 WHEN c.custname = n.search THEN 'exact match'
 ELSE 'partial match' END AS result
from names n left join cust c 
on c.custname LIKE n.search || '%'


Results:

| SEARCH | CUSTID |        RESULT |
|--------|--------|---------------|
| Andrew |      1 |   exact match |
|   Mark |      0 |     not found |
|   Mary |      3 |   exact match |
|     Jo |      2 | partial match |


As you can see, you need to put the search values into the cte called names, and then left join it with your table. Using this approach will also allow you to use LIKE operator with a 'result' column, as demonstrated by the second query.

BTW, read this article on how to create a UDF that will convert the IN-list to a table