Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select without FROM but with more than one rows

Tags:

sql

select

mysql

How can I produce a table with 2 rows and 2 columns without selecting from an existing table? What I'm looking for is a select statement that returns,

e.g.

 id | value
 ---------
 1  | 103
 2  | 556
like image 804
Konrad Waldorf Avatar asked Oct 30 '12 09:10

Konrad Waldorf


People also ask

Is it possible to have multiple rows with SELECT from dual?

No. dual has just one row, but you can use union all : SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL UNION ALL SELECT 4542 FROM DUAL UNION ALL SELECT 342 FROM DUAL; This is just one way to generate a table "on-the-fly" in Oracle.

Can SELECT clause be used without from?

If more than one column name appears in the phrase, each one must be a connected reference to the same group. The clause "SELECT" cannot be used without the clause "FROM." With the aid of certain functions, the SQL GROUP BY Statement is used to group identical data into groups.

How do I SELECT all rows except one in SQL?

The SQL EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

How can I get multiple row data in SQL?

You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows. Contents: Using IN operator with a Multiple Row Subquery.


2 Answers

Use UNION

SELECT 1 as id, 103 as value
UNION
SELECT 2 as id, 556 as value

See this SQLFiddle

like image 90
Himanshu Jansari Avatar answered Nov 16 '22 04:11

Himanshu Jansari


You can use UNION ALL. Which is better choice then UNION

SELECT 1 as id, 103 as value
UNION ALL
SELECT 2 as id, 556 as value
like image 33
Arion Avatar answered Nov 16 '22 04:11

Arion