Menu
A SELECT without a FROM clause gets us a multiple columns without querying a table:
SELECT 17+23, REPLACE('bannanna', 'nn', 'n'), RAND(), CURRENT_TIMESTAMP;
How can we write a query that results in multiple rows without referring to a table? Basically, abuse SELECT to turn it into a data definition statement. The result could have a single column or multiple columns.
I'm most interested in a DBMS neutral answer, but others (e.g. based on UNPIVOT) are welcome. I'd like to collect as many ways of doing this as possible. There's no technique application behind this question; it's more theoretical than practical.
694
No. dual has just one row, but you can use union all : This is just one way to generate a table "on-the-fly" in Oracle.
You can't. That's what a primary key is - something that is unique to every row.
In this case, we use GROUP_CONCAT function to concatenate multiple rows into one column. GROUP_CONCAT concatenates all non-null values in a group and returns them as a single string. If you want to avoid duplicates, you can also add DISTINCT in your query.
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. Using NOT IN operator with a Multiple Row Subquery.
Use a UNION:
SELECT 1
UNION
SELECT 2
Looks like this in MySQL:
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
Use UNION ALL to avoid the loss of non-unique rows.
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
