How to write the sql to findout which value is not in the table?

Question

I am having a table Student and i have a set of 20 names.

by using his sql

select name from student st where st.name in (
 'abc', 'xyz', . . .
)

i can find out all student names which are in table and in the set.
Now, how can i find out which out of these 20 names are not in Student table.

Answer 1

SELECT NOT IN ?

postgresql: http://archives.postgresql.org/pgsql-sql/2002-08/msg00322.php

Answer 2

select name from student where name not in (
select name from student st where st.name in (
 'abc', 'xyz', . . .
))

EDIT: I might not get what you are looking for. Please run following script and it is giving the results.

declare @student table
(
    name varchar(50)
)

insert into @student select 'james'
insert into @student select 'will'
insert into @student select 'bill'
insert into @student select 'adam'
insert into @student select 'jon'
insert into @student select 'white'
insert into @student select 'green'

select name from @student where name in ('james', 'will', 'bill')

select name from @student where name not in (select name from @student where name in ('james', 'will', 'bill'))

Answer 3

I'm assuming you want the names themselves. One option is to create a table with all the available student names, then select from it rows which don't have corresponding rows in the student tables, it will look something like this
select name from student_names
where name not in (select name from students)

Answer 4

CREATE TABLE student(name VARCHAR(255));
INSERT INTO student VALUES('a'), ('abc');
CREATE TABLE temp(x VARCHAR(255));
INSERT INTO temp VALUES('abc'), ('xyz');
SELECT x FROM temp WHERE 
  NOT EXISTS (SELECT * FROM student st WHERE st.name = x);

Depending on the database you use, there might be an easier way. There is also a way using UNION.

Answer 5

DECLARE @names table ( name varchar(100) )
INSERT INTO @names VALUES ('abc')
...
INSERT INTO @names VALUES ('xyz')

SELECT name FROM @names WHERE name NOT IN ( SELECT DISTINCT Name FROM Student )

Answer 6

Assuming that the tool you are using can generate dynamic sql, try generating an inline view consisting of your set of user names - like so:

select 'abc' check_name union all
select 'xyz' check_name union all

...

(The syntax of the inline view may depend on which version of SQL you are using - some versions of SQL require a from [dummy_table] clause in select statements that are not accessing a table.)

Then construct a query using this inline view with a not exists in student clause, like this:

select check_name from (
select 'abc' check_name union all
select 'xyz' check_name union all

...

) ilv where not exists
(select null from student st where st.name = ilv.check_name)