CODEWITHSUNDEEP
sql
Rakesh Juyal
Rakesh Juyal

Reputation: 36749

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

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.

Upvotes: 1

Views: 283

Answers (6)

user359040
user359040

Reputation:

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)

Upvotes: 0

Kashif
Kashif

Reputation: 14430

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

Upvotes: 0

David Hedlund
David Hedlund

Reputation: 129792

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 )

Upvotes: 0

Thomas Mueller
Thomas Mueller

Reputation: 50097

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.

Upvotes: 2

JohnoBoy
JohnoBoy

Reputation: 565

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)

Upvotes: 4

nilphilus
nilphilus

Reputation: 620

SELECT NOT IN ?

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

Upvotes: 0

Related Questions