Compare IN List and SQL Table rows and get missing IDs

Compare IN List and SQL Table rows and get missing IDs

Added by Nasir Mahmood updated on Friday, July 12, 2019

Problem:

Many time we need to find ID's which are not present in table.If we have this data in tables then we can find by joins but if we only values then we need some other hacks like this.

SELECT * FROM
  (VALUES (1),(2),(3),(4)) AS T(PK)
EXCEPT
SELECT PK 
FROM [TABLE];				
				

Where PK is Columns name and T is table. In general

SELECT 
        FieldName1, FieldName2, ..., FieldNameN
FROM
  (
    Values
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN ),
        ( ValueForField1, ValueForField2,..., ValueForFieldN )
  ) AS TempTableName ( FieldName1, FieldName2, ..., FieldNameN )				
				

Related Tags

About

32 Tutorials
27 Snippets
6 Products

More

Contact Us

Contact us

Stay Connected