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

SqlExists

Sql Problem Overview

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.

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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

Solution 3 - Sql

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
)

Solution 4 - Sql

This also works

SELECT *
FROM tableB
WHERE ID NOT IN (
  SELECT ID FROM tableA
);

Solution 5 - Sql

Or if "NOT EXISTS" are not implemented

SELECT *
FROM   B
WHERE (SELECT count(*)  FROM   A WHERE  A.ID = B.ID) < 1
Categories
Recommended Sql Solutions
Recommended Exists Solutions

Previous Solution:

python: how to convert a valid uuid from String to UUID?

PythonUuid

Next Solution:

How do I add a Font Awesome icon to input field?

HtmlCssFont Awesome

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestioncodingknobView Question on Stackoverflow
Solution 1 - Sqlgdoron is supporting MonicaView Answer on Stackoverflow
Solution 2 - SqlDanylo ZherebetskyyView Answer on Stackoverflow
Solution 3 - SqlAbe MiesslerView Answer on Stackoverflow
Solution 4 - SqlKai DongView Answer on Stackoverflow
Solution 5 - SqlDaflanckView Answer on Stackoverflow