sql check if entry in table A exists in table B

Question

I have a definition table that I know is not being maintained very well  lets call this table A  I have another table  call it table B  that is much smaller and ideally should be a subset of table A but I know that table A is somewhat stale and does not contain new entries that are in Table B   Note  that tables A and B have different columns   Table A  ID  Name  blah  blah  blah  blah  Table B  ID  Name   I want all rows in Table B such that the ID in Table B does NOT exist in Table A  This does not just match rows in Table A  I want only rows in Table B where the ID does NOT exist at all in Table A

User · Answer

This also works  SELECT   FROM tableB WHERE ID NOT IN     SELECT ID FROM tableA

User · Answer

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

User · Answer

The classical answer that works in almost every environment is  SELECT ID  Name  blah  blah FROM TableB TB LEFT JOIN TableA TA ON TB ID TA ID WHERE TA ID IS NULL   sometimes NOT EXISTS may be not implemented  not working

User · Answer

Or if  NOT EXISTS  are not implemented  SELECT   FROM   B WHERE  SELECT count     FROM   A WHERE  A ID   B ID   lt  1

User · Answer

If you are set on using EXISTS you can use the below in SQL Server   SELECT   FROM TableB as b WHERE NOT EXISTS      SELECT   FROM TableA as a    WHERE b id   a id

[sql] sql: check if entry in table A exists in table B

Examples related to sql

Examples related to exists