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, May 05 '16 at 02:07
Well you an use [similar query](http://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table) for finding missing IDs. — Muhammad Abid, May 05 '16 at 02:48

score 2 · Answer 1 · answered May 05 '16 at 02:06

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 05 '16 at 02:06

Gordon Linoff

1,242,037
58
646
786

Thanks for answering. But i am having more 300 IDs , is there any query something like IN ( 1,2,3...etcc.) – Munna Babu May 05 '16 at 02:09
You should put the values into a table and use the table in the query. – Gordon Linoff May 05 '16 at 02:10

How to get not exist records from table SQL?

1 Answers1