Selecting from a list of Values

Selecting from a list of Values

Sometimes you have a list of values or parameters that you’d like to run a select statement over the top of to get a result. There’s a few ways of doing this, usually I see people create a Temporary Table and insert the data. But is there an easier way?

Yes, we can select from VALUES:

SELECT * FROM (VALUES(1,2,3)) dataset(col1, col2, col3)

Which spits us out a dataset with 1 record and 3 columns. We can add more than 1 record by adding more sets of values:

SELECT * FROM (VALUES(1,2,3),(4,5,6)) dataset(col1, col2, col3)

Which will produce our dataset with 2 records:

You can also join to a list of values using the same logic:

SELECT		*
FROM		SampleData
INNER JOIN ( VALUES (1, 2, 3), (4, 5, 6)) AS dataset (col1, col2, col3)
		ON dataset.col1 = sampledata.ID

Basically, it works exactly like a table would. In these examples, I’ve used constants for the data, but you could use variables or parameters in a Stored Procedure, or you can use values coming out of another table directly in there.

Next time you need to join to some data that’s not in a table, instead of going through the hassle of setting up a table and inserting data, try using VALUES instead and let me know how you go.