Not in In SQL statement?

James123 picture James123 · Apr 12, 2012 · Viewed 43.1k times · Source

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

Leniel Maccaferri picture Leniel Maccaferri · Apr 12, 2012

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