views:

78

answers:

6

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.

A: 
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);
Bytecode Ninja
Except is not in SQL Server 2000.
Ardman
+2  A: 
SELECT A.* FROM A WHERE ID NOT IN(SELECT ID FROM B)
Tim Schmelter
Works great, thanks
Wai Wong
+1  A: 

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
+3  A: 
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) 
Ardman
Is this syntax valid? Shouldn't it be where ID not in (...)?
Bytecode Ninja
This will not work. Correct syntax should be: `SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM B);`
msakr
+1  A: 

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)
msakr
EXCEPT is not part of SQL Server 2000.
Ardman
A: 
SELECT ID 
  FROM A 
 WHERE NOT EXISTS( SELECT 1
                     FROM B
                    WHERE B.ID = A.ID
                 )
Mark Baker