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 · Accepted 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

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

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

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 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 NOT EXISTS  SELECT 1                      FROM B                     WHERE B ID   A ID

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

Examples related to sql

Examples related to sql-server-2000