select a value where it doesn't exist in another table

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.

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?

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.

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

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)

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

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)