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)

Tailing

Show us the SQL you are using. – Decode

can you use the Except keyword? i.e select id from table_1 (the table with the excel ids) EXCEPT select id from table 2 (the sql table) It works similar to the MINUS keyword in oracle. – Paralogism

Answer 1

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

Fiberglass

@LenielMacaferi: No one has doubt that. I've noted the differences. Add for instance INSERT INTO @Excel VALUES(7); to my sample data and you see that your query now would return 4 records(repeating "1") whereas Except still returns 3 DISTINCT Values. So it depends on the requirement. – Fiberglass

@Myself: of course repeating "1" was a typo and should be repeating "7" ;) – Fiberglass

Answer 2

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

Miliary

Answer 3

Import your excel file into SQL Server using the Import Data Wizard found in SQL Server Management Studio.

Then you can write the following query to find any IDs which are in the file but not in the table:

SELECT id     
FROM imported_table
WHERE id NOT IN (SELECT id FROM db_table)

Appalachian

Answer 4

You should move excel data to a table in SQL Server, and then do the query in SQL Server.

  select distinct id from Excel where id not in (select your ids from Sqltable)

(Obviously select your ids from Sqltable is a select which returns the Ids existing on SQL Server).

You may think that moving data to SQL Server is hard to do, but, on the contrary, it's very easy:

1) create a table

  CREATE TABLE ExcelIds (Id int)

2) add a new column in excel with the following formula:

  ="insert into ExcelIds values(" & XX & ")"

where XX is the reference to the cell in the column with excel Ids.

3) copy the "inserts" from Excel into SSMS or whatever tool you're usin in SQL Server, and execute them.

Now you have 2 tables in SQL Server, so that querying it is absolutely easy.

When you're over, just drop the table

DROP TABLE ExcelIds

NOTE: I didn't create a key on SQL Server table because I suppose that the Ids can be repeated. Neither is justified to create a more complex SQL Query to avoid duplicates in ExcelIds for this ad hoc solution.

Avivah