CODEWITHSUNDEEP
mysql
Neil C. Obremski
Neil C. Obremski

Reputation: 20264

How to define a table of inline values in MySQL?

Here is a way of defining inline table data within a SQL query:

SELECT 1 `id`, 'hello' `name`
UNION ALL
SELECT 2 `id`, 'goodbye' `name`

You can put this into something that expects a query returning the columns id, name and it will work correctly. However, this takes up a lot of unnecessary characters when there are more than one or two rows.

Is there a better way than UNION'ing multiple SELECT's for defining an inline table of values within a MySQL query?

Upvotes: 2

Views: 1627

Answers (1)

O. Jones
O. Jones

Reputation: 108676

If you want a sequence of integers and you're using MariaDB, the MySQL fork, you can use the SEQUENCE storage engine.

SELECT seq FROM seq_0_to_6

for example, will give you a table of integers 0-6.

Other than that, you asked whether there's a more efficient way than a UNION ALL query or a temp table definition to get a so-called inline table.

The answer is no, not really.

But there's one minor efficiency you didn't pick up on. You don't need to repeat the column names more than once in your UNION ALL cascade. So, instead of 

SELECT 1 id, 'hello' name
UNION ALL
SELECT 2 id, 'goodbye' name
UNION ALL
SELECT 3 id, 'salut' name

You can do this:

SELECT 1 id, 'hello' name
UNION ALL
SELECT 2, 'goodbye'
UNION ALL
SELECT 3, 'salut'

It saves a few characters.

Notice also that UNION ALL is a better choice than UNION because UNION implies the removal of duplicates, which in turn implies a bunch of processing. UNION ALL skips that.

Upvotes: 1

Related Questions