How to select several hardcoded SQL rows?

Question

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.

Is there a reason you don't want to use union? Are there a large number of values? — NullRef, Jun 14 '11 at 15:17

score 83 · Answer 1 · edited Apr 08 '15 at 15:02

83

Values keyword can be used as below.

select * from 
(values ('test-a1', 'test-a2'), ('test-b1', 'test-b2'), ('test-c1', 'test-c2')) x(col1, col2)

edited Apr 08 '15 at 15:02

Rory McCrossan

331,213
40
305
339

answered Apr 08 '15 at 14:44

Muthu

1,177
1
9
3

1

Is there a reason why UNION ALL answer is preferred over this one? – svz Jun 28 '15 at 21:35
4

This code snippet can’t run in MuSQL 5.6.25. Is it a new feature in 5.7? – HKTonyLee Jan 22 '16 at 08:51
2

This example worked fine in MS SQL Server for me, but not in Oracle. – Graeck Jun 22 '16 at 23:11
Returns syntax error on 10.6.4-MariaDB-1:10.6.4+maria~focal-log – Matanya Dec 07 '21 at 11:50
This works in Postgres! The Postgres docs have it written as `(SELECT * FROM (VALUES (...), (...), ....) AS t(col_name1, ...)` https://www.postgresql.org/docs/current/queries-values.html – Fullchee Zhang Nov 07 '22 at 21:44
This was super easy to use in excel to generate the insert lines with a `(A2, B2),` per row – Chris Marisic Apr 26 '23 at 21:51

openshac · Answer 2 · 2021-07-01T08:25:11.943

58

The following will work for SQL:

SELECT 'test-a1' AS name1, 'test-a2' AS name2 
UNION ALL 
SELECT 'test-b1', 'test-b2'
UNION ALL 
SELECT 'test-c1', 'test-c2'

edited Jul 01 '21 at 08:25

answered Jun 14 '11 at 15:25

openshac

4,966
5
46
77

score 29 · Accepted Answer · answered Jun 14 '11 at 15:05

29

UNION ALL is the best bet. It's faster than UNION and you will have mutually exclusive rows.

answered Jun 14 '11 at 15:05

HLGEM

94,695
15
113
186

Here is the Oracle answer that helped me the best (note there is a solution for strings rather than numbers as well, just look for my comment): https://stackoverflow.com/questions/10353969/how-can-i-select-from-list-of-values-in-oracle/10355365#10355365 – Skystrider Mar 14 '19 at 16:11
2

@HLGEM the opposite, UNION ALL generates duplicate rows while UNION generates mutually exclusive rows. – Shirkan Jan 24 '22 at 11:11

score 8 · Answer 4 · edited Oct 17 '17 at 08:22

8

Extending the answer of @openshac for oracle, as the below mentioned code works for oracle:

SELECT 'test-a1' AS name1, 'test-a2' AS name2 from dual 
UNION ALL 
SELECT 'test-b1', 'test-b2' from dual
UNION ALL 
SELECT 'test-c1', 'test-c2' from dual

edited Oct 17 '17 at 08:22

openshac

4,966
5
46
77

answered Jun 10 '15 at 16:00

Alok Chaudhary

3,481
1
16
19

score 4 · Answer 5 · edited May 14 '15 at 12:14

You can use a temp table, fill it up with your results and then select from it

create table #tmpAAA (name1 varchar(10), name2 varchar(10))
insert into #tmpAAA (name1, name2) 
values ('test_a', 'test_b'),
       ('test_c', 'test_d'),
       ('test_e', 'test_f'),
       ('test_g', 'test_h'),
       ('test_i', 'test_j');
select * from #tmpAAA;

This will return

name1   name2
==================
test_a  test_b
test_c  test_d
test_e  test_f
test_g  test_h
test_i  test_j

score 4 · Answer 6 · answered May 21 '21 at 18:28

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

score 3 · Answer 7 · answered Feb 09 '20 at 06:25

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.

score 2 · Answer 8 · answered Jun 14 '11 at 15:09

2

I'd love to hear is anyone has a better solution. In the past I've used this:

Select top 3 'Hardcode'
from tableWithLotsOfRows

Would you mind switching abc, with 123?

select top 3 
    'test-A'+convert(varchar, row_number() over (order by PrimaryKey)),
    'test-B'+convert(varchar, row_number() over (order by PrimaryKey))
from tableWithLotsOfRows

that should return something like:

TestA1, Test-B1
TestA2, Test-B2
TestA3, Test-B3

answered Jun 14 '11 at 15:09

TizzyFoe

1,489
1
15
28

It's a MySQL question, so there are no ranking functions. But nice idea! – Lukas Eder Jun 14 '11 at 15:11
1

That sucks. In t-sql i abuse the hell out of row_number(). – TizzyFoe Jun 14 '11 at 17:33

score 1 · Answer 9 · edited Nov 11 '20 at 00:38

1

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

edited Nov 11 '20 at 00:38

Giacomo1968

25,759
11
71
103

answered Oct 29 '18 at 12:41

vencedor

663
7
9

Don't need the final UNION – user1718097 Dec 14 '20 at 12:57

score 0 · Answer 10 · answered Aug 24 '23 at 20:18

With Mysql

code:

SELECT
    column_0 AS date,
    column_1 AS value
FROM
    (VALUES
        ROW('2023-01-01', 3434.33),
        ROW('2023-01-02', 5455.73),
        ROW('2023-01-03', 3434.53),
        ROW('2023-01-04', 3434.53),
        ROW('2023-01-05', 3434.53),
        ROW('2023-01-06', 3434.53),
        ROW('2023-01-07', 3434.53),
        ROW('2023-01-08', 3424.53)
    ) AS data

How to select several hardcoded SQL rows?

10 Answers10

Linked