Not in In SQL statement?

notin, set-difference, sql, sql-server, t-sql

Solution

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

Problem

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

Original source