Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql: Create inline table within select statement?

Tags:

sql

mysql

Is there a way in MySql to create an inline table to use for join?

Something like:

SELECT LONG [1,2,3] as ID, VARCHAR(1) ['a','b','c'] as CONTENT

that would output

|  ID  |  CONTENT  |
| LONG | VARCHAR(1)|
+------+-----------+
|   1  |    'a'    |
|   2  |    'b'    |
|   3  |    'c'    |

and that I could use in a join like this:

SELECT 
  MyTable.*,
  MyInlineTable.CONTENT
FROM
  MyTable
  JOIN 
    (SELECT LONG [1,2,3] as ID, VARCHAR(1) ['a','b','c'] as CONTENT MyInlineTable)
  ON MyTable.ID = MyInlineTable.ID

I realize that I can do

SELECT 1,'a' UNION SELECT 2,'b' UNION SELECT 3,'c'

But that seems pretty evil

I don't want to do a stored procedure because potentially a,b,c can change at every query and the size of the data as well. Also a stored procedure needs to be saved in the database, and I don't want to have to modify the database just for that. View is the same thing.

What I am really looking for is something that does SELECT 1,'a' UNION SELECT 2,'b' UNION SELECT 3,'c' with a nicer syntax.

like image 320
edeboursetty Avatar asked Mar 01 '12 16:03

edeboursetty


People also ask

How do you create a table from a SELECT statement in SQL?

You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl; MySQL creates new columns for all elements in the SELECT .

What is CREATE TABLE as SELECT?

The CREATE TABLE AS SELECT (CTAS) statement is one of the most important T-SQL features available. CTAS is a parallel operation that creates a new table based on the output of a SELECT statement. CTAS is the simplest and fastest way to create and insert data into a table with a single command.


1 Answers

The only ways i can remember now is using UNION or creating a TEMPORARY TABLE and inserting those values into it. Does it suit you?


TEMPORARY_TABLE (tested and it works):

Creation:

CREATE TEMPORARY TABLE MyInlineTable (id LONG, content VARCHAR(1) );

INSERT INTO MyInlineTable VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');

Usage:

SELECT 
  MyTable.*,
  MyInlineTable.CONTENT
FROM
  MyTable
  JOIN 
    SELECT * FROM MyInlineTable;
  ON MyTable.ID = MyInlineTable.ID

TEMPORARY_TABLES lifetime (reference):

Temporary tables are automatically dropped when they go out of scope, unless they have already been explicitly dropped using DROP TABLE:

.

All other local temporary tables are dropped automatically at the end of the current session.

.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.`

like image 175
Fernando Carvalhosa Avatar answered Nov 02 '22 23:11

Fernando Carvalhosa