How to get not exist records from table SQL?

Question

Sorry if my question was stupid or basic.I trying to get not exist id records from db. But it is showing all records.

---------------------+
id  |  name  |  age  |
---------------------+
 1  |   ase  |   33  |
 3  |   ase  |   33  |
 4  |   ase  |   33  |
 5  |   ase  |   33  |
 7  |   ase  |   33  |
 9  |   ase  |   33  |
---------------------+

i tried something like this

SELECT * FROM tablename WHERE id NO IN ('1','2','4','5','6','7') but it showing all records. In this given query id 2,6 are not exist in table show i need to show them as result. please some one help me to solve this. thanks

expecting output like

----------------------+
id not exist in table |
----------------------+
          2           |
          6           |
----------------------+

from the given IN statement this ID are not exist in TABLE something like this. I need to show id which are not exist in table as result.

i have updated question. from the given ID which are not exist in TABLE should show as output — Munna Babu
– Munna Babu, Commented May 5, 2016 at 2:07

Gordon Linoff · Accepted Answer · 2016-05-05 02:06:29Z

2

You need to use a (derived) table of values. Here is a solution using left join instead of not in:

select n.n
from (select 1 as n union all select 2 union all select 4 union all select 5 union all
      select 6 union all select 7
     ) n left join
     tablename t
     on t.id = n.n
where t.id is null;

answered May 5, 2016 at 2:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Munna Babu Over a year ago

Thanks for answering. But i am having more 300 IDs , is there any query something like IN ( 1,2,3...etcc.)

Gordon Linoff Over a year ago

You should put the values into a table and use the table in the query.

Collectives™ on Stack Overflow

How to get not exist records from table SQL?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related