Reputation: 31

SQL query to find missing rows

I have two tables A and B

A -> A_ID
     net_ID

B-> B_ID
    net_ID

these tables are related by net_ID

I have to find all the net_ID's which are missing in table B from that of A.

I have below query

select net_ID from A where net_ID not in (select net_ID from B);

Will this work?

Upvotes: 0

Answers (2)

Reputation: 6073

SELECT      A.*
FROM        A
LEFT JOIN   B On A.net_ID = B.net_ID
WHERE       B.net_ID is NULL

Upvotes: 1

Reputation: 10466

The simplest way would be to use the EXCEPT operator:

SELECT net_ID FROM A
EXCEPT 
SELECT net_ID FROM B

From MSDN:

EXCEPT returns any distinct values from the left query that are not also found on the right query.

Which is exactly what you are looking for...

Upvotes: 1