CODEWITHSUNDEEP
mysqlsql
d--b
d--b

Reputation: 5779

Mysql: Create inline table within select statement?

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.

Upvotes: 17

Views: 12734

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

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.

Yes, it is possible with ROW CONSTRUCTOR introduced in MySQL 8.0.19:

VALUES ROW (1,'a'), ROW(2,'b'), ROW(3,'c')

and with JOIN:

SELECT *
FROM tab 
JOIN (VALUES ROW (1,'a'), ROW(2,'b'), ROW(3,'c') ) sub(id, content)
  ON tab.id = sub.id;

db<>fiddle demo

Upvotes: 9

Fernando Carvalhosa
Fernando Carvalhosa

Reputation: 1107

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.`

Upvotes: 8

user319198
user319198

Reputation:

Yes. Do with stored procedures or views.

Upvotes: -2

Related Questions