select a value where it doesn t exist in another table

Question

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

User · Answer

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.

User · Answer

SELECT ID    FROM A   WHERE NOT EXISTS  SELECT 1                      FROM B                     WHERE B ID   A ID

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server-2000