Not in In SQL statement?

Question

I have set of ids in excel around 5000 and in the table I have ids around 30000. If I use 'In' condition in SQL statment I am getting around 4300 ids from what ever I have ids in Excel. But If I use 'Not In' with Excel id. I have getting around 25000+ records. I just to find out I am missing with Excel ids in the table.

How to write sql for this?

Example: Excel Ids are

 1,
 2,
 3,
 4,
 5,
 6,
 7,
 8,
 9,
10,

Table has IDs

 1,
 2,
 3,
 4,
 6,
 8,
 9,
11,
12,
14,
15

Now I want get 5,7,10 values from Excel which missing the table?

Update:

What I am doing is

SELECT  [GLID]      
  FROM [tbl_Detail] 
  where datasource = 'China' and  ap_ID  not in (5206896,
5206897,
5206898,
5206899,
5117083,
5143565,
5173361,
5179096,
5179097,
5179150)

Answer 1

Try this:

SELECT    tableExcel.ID
FROM      tableExcel
WHERE     tableExcel.ID NOT IN(SELECT anotherTable.ID FROM anotherTable)

Here's an SQL Fiddle to try this: sqlfiddle.com/#!6/31af5/14

Answer 2

You're probably looking for EXCEPT:

SELECT Value 
FROM @Excel 
EXCEPT
SELECT Value 
FROM @Table;

Edit:

Except will

• treat NULL differently(NULL values are matching)
• apply DISTINCT

unlike NOT IN

Here's your sample data:

declare @Excel Table(Value int);
INSERT INTO @Excel VALUES(1);
INSERT INTO @Excel VALUES(2);
INSERT INTO @Excel VALUES(3);
INSERT INTO @Excel VALUES(4);
INSERT INTO @Excel VALUES(5);
INSERT INTO @Excel VALUES(6);
INSERT INTO @Excel VALUES(7);
INSERT INTO @Excel VALUES(8);
INSERT INTO @Excel VALUES(9);
INSERT INTO @Excel VALUES(10);

declare @Table Table(Value int);
INSERT INTO @Table VALUES(1);
INSERT INTO @Table VALUES(2);
INSERT INTO @Table VALUES(3);
INSERT INTO @Table VALUES(4);
INSERT INTO @Table VALUES(6);
INSERT INTO @Table VALUES(8);
INSERT INTO @Table VALUES(9);
INSERT INTO @Table VALUES(11);
INSERT INTO @Table VALUES(12);
INSERT INTO @Table VALUES(14);
INSERT INTO @Table VALUES(15);