Search code examples
sqlselectwhere-in

Items not returned from a list

I have a list of items that is 860 items long. When i execute the query: select * from tableA where item in (... items ...) I get 858 items. I would like to know the 2 items in the list that are not in tableA.

NOT returns all of the items in the table that are not in the list, I want all the items in the list that are not in the table.

Solution

  • I would recommend that you convert your list into a temp table (there are a ton of udfs floating around that you can use ex: http://blog.sqlauthority.com/2007/05/06/sql-server-udf-function-to-convert-list-to-table/)

    Once you have your temp table #List, you can do the following;

    CREATE TABLE #List
(
  [ListItem] INT
)

SELECT
    *
FROM
    #List AS l
LEFT OUTER JOIN
    tableA AS t
ON
    t.[Item] = l.[ListItem]
WHERE
    t.[Item] IS NULL

    See it in action: https://data.stackexchange.com/stackoverflow/query/61259/items-not-returned-from-a-list