How to select several hardcoded SQL rows?
MysqlSqlOracleMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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)
Solution 2 - Mysql
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'
Solution 3 - Mysql
UNION ALL is the best bet. It's faster than UNION and you will have mutually exclusive rows.
Solution 4 - Mysql
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
Solution 5 - Mysql
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
Solution 6 - Mysql
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
Solution 7 - Mysql
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
Solution 8 - Mysql
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.
Solution 9 - Mysql
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