Show elements of where clause that are not present in table

sql, sql-server, sql-server-2005

Solution

If I understand correctly what you need you can do it this way

SELECT q.id, 
       CASE WHEN t.id IS NULL THEN 'no' ELSE 'yes' END id_exists
  FROM
(
  SELECT '9FG' id UNION ALL
  SELECT '1GH' UNION ALL
  SELECT '3UI'
) q LEFT JOIN table1 t
    ON q.id = t.id

Output:

|  ID | ID_EXISTS |
|-----|-----------|
| 9FG |        no |
| 1GH |       yes |
| 3UI |        no |

or if you just need a list of non-existent ids

SELECT q.id 
  FROM
(
  SELECT '9FG' id UNION ALL
  SELECT '1GH' UNION ALL
  SELECT '3UI'
) q LEFT JOIN table1 t
    ON q.id = t.id
 WHERE t.id IS NULL

Output:

|  ID |
|-----|
| 9FG |
| 3UI |

The trick is to use an `OUTER JOIN` instead of `WHERE` condition to filter data from your table and be able to see the mismatches.

Here is SQLFiddle demo

Problem

I search a table based on an ID column in my where clause. I have a list of IDs that may or may not be present in this table. A simple query will give me the IDs which exist in that table (if any). Is there a way to also return ID's that were not found ? ``` Table -- ID 1GH 2BN 3ER SELECT * FROM Table WHERE ID IN (big list 9FG, 1GH, 3UI etc) --If ID's in above list are not in table, then show those ids. ``` Desired output - ``` 9FG, 3UI were not found in the table ```

Original source