SQL Select to return a static list of values?

Question

Is there a way to perform a select and return a static list? Something like the following:

select * from ('CA', 'FB', 'FC')

It should return

CA
FB
FC

score 27 · Accepted Answer · edited Jan 11 '23 at 20:06

27

If you want each value on a separate row, you can use table constructor values():

select val
from (values ('CA'), ('FB'), ('FC')) as t(val)

If you wanted more columns, you would use tuples rather than singletons:

select val1, val2
from (values 
    ('CA', 'CB'), 
    ('FA', 'FB'),
    ('FC', 'FD') 
) as t(val1, val2)

edited Jan 11 '23 at 20:06

Joel Coehoorn

answered May 07 '20 at 21:41

GMB

If you want a multi-column table, you can also use this syntax (at least in Postgresql): SELECT col1, colA FROM (VALUES ('hello', 'world'), ('run', 'scream')) AS t(col1, colA) – Sarah Messer May 24 '22 at 15:14

score 2 · Answer 2 · answered May 07 '20 at 21:40

2

Perhaps:

select 'CA'
union all
select 'FB'
union all
select 'FC'

answered May 07 '20 at 21:40

avery_larry

2 Answers2