84

I have two tables

Table A:

ID
1
2
3
4

Table B:

ID
1
2
3

I have two requests:

  • I want to select all rows in table A that table B doesn't have, which in this case is row 4.
  • I want to delete all rows that table B doesn't have.

I am using SQL Server 2000.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Wai Wong
  • 2,671
  • 4
  • 21
  • 17

6 Answers6

143

You could use NOT IN:

SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B)

However, meanwhile i prefer NOT EXISTS:

SELECT A.* FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE B.ID=A.ID)

There are other options as well, this article explains all advantages and disadvantages very well:

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 4
    how do you add another "where" statement in that? if you wanna say ````SELECT A.* FROM A WHERE A.ID=1 AND NOT EXISTS(...)```` – Chemist Feb 26 '19 at 19:34
  • @Chemist That's how you do it. Multiple where clauses can be combined with AND or OR, depending on the logic you require, with exactly the syntax you've used. – bindsniper001 Mar 16 '20 at 18:17
41

For your first question there are at least three common methods to choose from:

  • NOT EXISTS
  • NOT IN
  • LEFT JOIN

The SQL looks like this:

SELECT * FROM TableA WHERE NOT EXISTS (
    SELECT NULL
    FROM TableB
    WHERE TableB.ID = TableA.ID
)

SELECT * FROM TableA WHERE ID NOT IN (
    SELECT ID FROM TableB
)

SELECT TableA.* FROM TableA 
LEFT JOIN TableB
ON TableA.ID = TableB.ID
WHERE TableB.ID IS NULL

Depending on which database you are using, the performance of each can vary. For SQL Server (not nullable columns):

NOT EXISTS and NOT IN predicates are the best way to search for missing values, as long as both columns in question are NOT NULL.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
3
select ID from A where ID not in (select ID from B);

or

select ID from A except select ID from B;

Your second question:

delete from A where ID not in (select ID from B);
Behrang
  • 46,888
  • 25
  • 118
  • 160
3

This would select 4 in your case

SELECT ID FROM TableA WHERE ID NOT IN (SELECT ID FROM TableB)

This would delete them

DELETE FROM TableA WHERE ID NOT IN (SELECT ID FROM TableB)
kamasheto
  • 1,020
  • 6
  • 12
3
SELECT ID 
  FROM A 
 WHERE NOT EXISTS( SELECT 1
                     FROM B
                    WHERE B.ID = A.ID
                 )
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
-2
SELECT ID
  FROM A
 WHERE ID NOT IN (
      SELECT ID
        FROM B);

SELECT ID    
  FROM A a
 WHERE NOT EXISTS (
      SELECT 1 
        FROM B b
       WHERE b.ID = a.ID)

         SELECT a.ID 
           FROM A a    
LEFT OUTER JOIN B b 
             ON a.ID = b.ID    
          WHERE b.ID IS NULL

DELETE 
  FROM A 
 WHERE ID NOT IN (
      SELECT ID 
        FROM B)
Neil Knight
  • 47,437
  • 25
  • 129
  • 188