The VALUES row constructor can be used anywhere where a table can be used:

FROM ( VALUES (1,2), (2,3) );

The columns can can be named using the AS clause (which is required by some DBMS):

FROM ( VALUES (1,2), (2,3) ) AS tv (pid,cid);

This can e.g. be used to find values from a list that aren't in the database:

WITH id_list (id) AS (
   VALUES (1),(2),(3)
FROM id_list
  LEFT JOIN some_table t ON =;

Will return all IDs from the list of ids that are not in the table some_table. The above example uses a common table expression

Back to the SQL Feature Comparison