How to select several hardcoded SQL rows?

user_220474 0
Viewed 85637

If you execute this query

SELECT 'test-a1' AS name1, 'test-a2' AS name2

the result will be a one row-selection with two columns having these values: 

test-a1, test-a2

How can I modify the above query to have a selection with several rows, e.g.

test-a1, test-a2
test-b1, test-b2
test-c1, test-c2

I know how to do this with UNION but I feel that there exists a more simple way to do it.

PS. Sorry for such a basic question, it is very hard to google it.

oracle mysql sql
9 Answers

In MySQL you could use UNION like this:

SELECT * from 
    (SELECT 2006 AS year UNION
     SELECT 2007 AS year UNION
     SELECT 2008 AS year UNION
    ) AS years
user_26534570

As of MySQL 8.0.19, it is possible to do

SELECT
    column_0 AS name1,
    column_1 AS name2
FROM
    (VALUES
        ROW('test-a1','test-a2'),
        ROW('test-b1','test-b2'),
        ROW('test-c1','test-c2')
    ) AS hardcodedNames

Which returns

name1   name2
==================
test-a1 test-a2
test-b1 test-b2
test-c1 test-c2

A note on column names

The columns of the table output from VALUES have the implicitly named columns column_0, column_1, column_2, and so on, always beginning with 0.

Documentation here: https://dev.mysql.com/doc/refman/8.0/en/values.html.

user_38167790

The following code work for me in MSSQL environment:

SELECT Name1,Name2 FROM(VALUES  ('test-a1', 'test-a2'),
                                   ('test-b1', 'test-b2'),
                                   ('test-c1', 'test-c2'))AS Test(Name1,Name2)

Output:

Name1   Name2
------- -------
test-a1 test-a2
test-b1 test-b2
test-c1 test-c2
user_113712810
Related