SELECT from a list of values

Question

So it is pretty straight forward to do:

Select Val from MyTable where Val not in ('100','200','300'....)

1. How do I write a query so that I select values from the list. eg how do I do something like Select * from ('100','200','300'....) so that the output is:

   100    
   200    
   300    
   ...
   

2. Further, How do I do something like select * from ('100','200','300'....) that are not in MyTable.Val column?

Answer 1

How do I do something like select * from ('100','200','300'....) that are not in MyTable.Val

You can use values() to build a derived table that contains the list of values, and then not exists to filter on those that cannot be found in the table:

select v.*
from (values (100), (200), (300)) v(val)
where not exists (select 1 from mytable t where t.val = v.val)