CODEWITHSUNDEEP
sqlsql-server-2008
user1552698
user1552698

Reputation: 597

Finding whats in one table that is not in another table

I have following two tables:

CREATE TABLE TEMP1(ID INT)
CREATE TABLE TEMP2(ID INT)

INSERT INTO TEMP1 SELECT 1
INSERT INTO TEMP1 SELECT 2
INSERT INTO TEMP1 SELECT 3
INSERT INTO TEMP1 SELECT 4

INSERT INTO TEMP2 SELECT 1
INSERT INTO TEMP2 SELECT 2
INSERT INTO TEMP2 SELECT 3
INSERT INTO TEMP2 SELECT 4
INSERT INTO TEMP2 SELECT 5

SELECT * FROM TEMP1
SELECT * FROM TEMP2

I am trying to find the records which are in TEMP2, but not in TEMP1 i.e. 5. If I run the following query, I get the proper result:

  SELECT DISTINCT [ID2] FROM (
   SELECT DISTINCT
   C1.[ID] AS [ID1]
   ,C2.[ID] AS [ID2]
   FROM TEMP1 C1
   FULL JOIN TEMP2 C2 ON C1.[ID]=C2.[ID]
   )A
   WHERE [ID1] IS NULL
   ORDER BY 1

But when I run following query, I don't return anything:

   SELECT DISTINCT T2.[ID] FROM TEMP2 T2
   WHERE T2.[ID] NOT IN (SELECT DISTINCT T1.[ID] FROM TEMP1 T1 )
   ORDER BY 1

Any idea whats wrong?

Upvotes: 0

Views: 44

Answers (3)

Red Devil
Red Devil

Reputation: 2393

I would suggest you should use EXCEPT 

select ID from TEMP2
EXCEPT
Select ID from TEMP1

Upvotes: 1

DDeMartini
DDeMartini

Reputation: 337

You can use a left join and test for null:

SELECT DISTINCT T2.ID 
   FROM TEMP2 T2 
      LEFT JOIN TEMP1 T1 USING(ID) 
   WHERE T1.ID IS NULL;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Don't use not in with a subquery. It doesn't have the right semantics. I returns no rows if even one value in the subquery is NULL. Use not exists instead:

SELECT T2.[ID]
FROM TEMP2 T2
WHERE NOT EXISTS (SELECT 1 FROM TEMP1 T1 WHERE T2.[ID] = T1.[ID])
ORDER BY 1;

I assumes the SELECT DISTINCT is not needed in the outer query.

Upvotes: 1

Related Questions