Select a row of the table with desired value, when a column's value which is not in the table i-CodePudding

I have a table with one of the columns as ID. I have a set of values which I give in the where clause to compare the 'ID' column using 'in' keyword. I want to select the row if the value in that set of values has a record in the table. If not, the value that is not in the table has to be selected along with empty values other columns.

For example: There is a table with columns ID & Animal. It has 8 records.

The table with all records

If I run the query:

SELECT ID, Animal from #Temp1 where ID in (4,8)

it will return the following result.

The table result filtered

But, if I run the query:

SELECT ID, Animal from #Temp1 where ID in (4,8,12)

it should return the following result.

The table result with desired values

CodePudding user response：

Use a LEFT JOIN in concert with string_split() instead

Select ID = A.value
      ,Animal = coalesce(B.Animal,'ID Not Found') 
 From string_split('4,8,12',',') A
 Left Join YourTable B on A.value=B.ID

Results

ID  Animal
4   Donkey
8   Hampster
12  ID Not Found

If by chance string_split() is not available

Select ID = A.value
      ,Animal = coalesce(B.Animal,'ID Not Found') 
 From (values (4)
             ,(8)
             ,(12)
      ) A(value)
 Left Join YourTable B on A.value=B.ID