TSQL/SQL 2005/2008 Return Row from one table that is not in othe table

Question

I have to compare a row of one table that is not in another table

TableA

ID
1
2
3
4
NULL

TableB

ID
1
4

When comparing TableA with TableB ,the following o/p (NULL Can be ignored)

ID   STATUS

1    FOUND   
2    NOT FOUND
3    NOT FOUND
4    FOUND

I tried with

SELECT
      case T.ID when isnull(T.ID,0)=0 then 'NOT FOUND'
      case T.ID when isnull(T.ID,0)<>0  then 'FOUND'
     end,T.ID 
     FROM
          TableA  T 
               LEFT JOIN
          TableB N 
   ON T.ID=N.ID

Its ended with incorrect syntax near '=',moreover i have no idea whether the query is correct.

Answer 1

Try this:

SELECT a.ID, 
    CASE WHEN b.ID IS NULL THEN 'NOT FOUND' ELSE 'FOUND' END AS Status
FROM TableA a
    LEFT JOIN TableB b ON a.ID = b.ID

Note the difference in the structure of the CASE statement - that was your problem.

Answer 2

SELECT 
T.ID 
FROM TableA T WHERE NOT EXISTS ( SELECT X.ID FROM TableB X WHERE X.ID = T.ID)

If you want the 'Found' or 'Not Found' answer go for what AdaTheDev posted

Answer 3

To generate the result as shown in the question:

SELECT ID,
       CASE WHEN EXISTS (SELECT * FROM TableB WHERE ID = TableA.ID)
            THEN 'FOUND'
            ELSE 'NOT FOUND'
       END AS STATUS
  FROM TableA

But if you are only interested in the missing records:

SELECT ID
  FROM TableA
 WHERE NOT EXISTS (SELECT * FROM TableB WHERE ID = TableA.ID)