Skip to main content
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Ajay Sathuluri
Ben Nadel at CFUNITED 2009 (Lansdowne, VA) with: Ajay Sathuluri

Using VALUES / ROW To Create Derived Table From Static Values In MySQL 8.0.19

By
Published in — Comments (2)

Most of the time, when writing SQL statements, I'm working with data that is wholly contained within the database itself. But, when running reports or helping the Support team debug an issue, I sometimes need to write a SQL statement that provides temporary table data as part of the SQL statement itself. This has always been possible in MySQL (for as long as I can remember). However, in the recent releases of MySQL 8, the VALUES / ROW data manipulation language (DML) statements have greatly reduced the verbosity of such statements.

Consider this list of user ID and Email values:

1, 'sarah.hong@example.com'
2, 'jon.connor@example.com'
3, 'rick.martin@example.com'
4, 'emily.hill@example.com'
5, 'aaron.masters@example.com'
6, 'todd.mccleod@example.com'
7, 'molly.oswald@example.com'
8, 'bolton.taylor@example.com'
9, 'ellen.green@example.com'
10, 'hanah.oconnor@example.com'
11, 'william.pops@example.com'
12, 'dan.smith@example.com'
13, 'jenny.reed@example.com'
14, 'harry.fitzgerald@example.com'
view raw data.txt hosted with ❤ by GitHub

Pre-MySQL 8, if I wanted to author a SQL statement that provides these values as a derived table, I'd have to perform a SELECT..AS..UNION statement for each row of data:

SELECT
derived.id,
derived.email,
COALESCE( m.id, 0 ) AS memberID
FROM
(
SELECT 1 AS id, 'sarah.hong@example.com' AS email UNION
SELECT 2 AS id, 'jon.connor@example.com' AS email UNION
SELECT 3 AS id, 'rick.martin@example.com' AS email UNION
SELECT 4 AS id, 'emily.hill@example.com' AS email UNION
SELECT 5 AS id, 'aaron.masters@example.com' AS email UNION
SELECT 6 AS id, 'todd.mccleod@example.com' AS email UNION
SELECT 7 AS id, 'molly.oswald@example.com' AS email UNION
SELECT 8 AS id, 'bolton.taylor@example.com' AS email UNION
SELECT 9 AS id, 'ellen.green@example.com' AS email UNION
SELECT 10 AS id, 'hanah.oconnor@example.com' AS email UNION
SELECT 11 AS id, 'william.pops@example.com' AS email UNION
SELECT 12 AS id, 'dan.smith@example.com' AS email UNION
SELECT 13 AS id, 'jenny.reed@example.com' AS email UNION
SELECT 14 AS id, 'harry.fitzgerald@example.com' AS email
) AS derived
LEFT OUTER JOIN
member m
ON
m.email = derived.email
;
view raw snippet-1.sql hosted with ❤ by GitHub

In this approach, each input row of user data is being used to define a table-less SELECT statement. Each one of these results is then UNIONed together in order to populate the derived table. And, once I have the derived table, I can JOIN to it like any other SQL table.

This approach has worked for as long as I can remember. But, it's hella verbose. Much of this SQL statement is just repetitive noise used to define the derived table structure.

As of MySQL 8, we can reduce some of this noise by moving the column names into the AS derived specification:

SELECT
derived.id,
derived.email,
COALESCE( m.id, 0 ) AS memberID
FROM
(
SELECT 1, 'sarah.hong@example.com' UNION
SELECT 2, 'jon.connor@example.com' UNION
SELECT 3, 'rick.martin@example.com' UNION
SELECT 4, 'emily.hill@example.com' UNION
SELECT 5, 'aaron.masters@example.com' UNION
SELECT 6, 'todd.mccleod@example.com' UNION
SELECT 7, 'molly.oswald@example.com' UNION
SELECT 8, 'bolton.taylor@example.com' UNION
SELECT 9, 'ellen.green@example.com' UNION
SELECT 10, 'hanah.oconnor@example.com' UNION
SELECT 11, 'william.pops@example.com' UNION
SELECT 12, 'dan.smith@example.com' UNION
SELECT 13, 'jenny.reed@example.com' UNION
SELECT 14, 'harry.fitzgerald@example.com'
) AS derived ( id, email )
LEFT OUTER JOIN
member m
ON
m.email = derived.email
;
view raw snippet-2.sql hosted with ❤ by GitHub

This time, instead of include AS id and AS email in every single sub-select, we're factoring all of the column names out and into the derived table specification:

AS derived ( id, email )

This is a marked improvement over the MySQL 5.7 implementation. But, as of MySQL 8.0.19, we can do even better. MySQL 8.0.19 introduces the VALUES and ROW combination designed to do exactly what we're trying to do:

SELECT
derived.id,
derived.email,
COALESCE( m.id, 0 ) AS memberID
FROM
(
VALUES
ROW ( 1, 'sarah.hong@example.com' ),
ROW ( 2, 'jon.connor@example.com' ),
ROW ( 3, 'rick.martin@example.com' ),
ROW ( 4, 'emily.hill@example.com' ),
ROW ( 5, 'aaron.masters@example.com' ),
ROW ( 6, 'todd.mccleod@example.com' ),
ROW ( 7, 'molly.oswald@example.com' ),
ROW ( 8, 'bolton.taylor@example.com' ),
ROW ( 9, 'ellen.green@example.com' ),
ROW ( 10, 'hanah.oconnor@example.com' ),
ROW ( 11, 'william.pops@example.com' ),
ROW ( 12, 'dan.smith@example.com' ),
ROW ( 13, 'jenny.reed@example.com' ),
ROW ( 14, 'harry.fitzgerald@example.com' )
) AS derived ( id, email )
LEFT OUTER JOIN
member m
ON
m.email = derived.email
;
view raw snippet-3.sql hosted with ❤ by GitHub

This time, we're using the VALUES / ROW combination to collate the static data into a derived table. And, we're using the AS derived statement to define the column names. This is about as bare-bones as we can get.

For improved readability, we can use WITH to move this derived table into a common table expression (CTE):

WITH derived ( id, email ) AS (
VALUES
ROW ( 1, 'sarah.hong@example.com' ),
ROW ( 2, 'jon.connor@example.com' ),
ROW ( 3, 'rick.martin@example.com' ),
ROW ( 4, 'emily.hill@example.com' ),
ROW ( 5, 'aaron.masters@example.com' ),
ROW ( 6, 'todd.mccleod@example.com' ),
ROW ( 7, 'molly.oswald@example.com' ),
ROW ( 8, 'bolton.taylor@example.com' ),
ROW ( 9, 'ellen.green@example.com' ),
ROW ( 10, 'hanah.oconnor@example.com' ),
ROW ( 11, 'william.pops@example.com' ),
ROW ( 12, 'dan.smith@example.com' ),
ROW ( 13, 'jenny.reed@example.com' ),
ROW ( 14, 'harry.fitzgerald@example.com' )
)
SELECT
derived.id,
derived.email,
COALESCE( m.id, 0 ) AS memberID
FROM
derived
LEFT OUTER JOIN
member m
ON
m.email = derived.email
;
view raw snippet-4.sql hosted with ❤ by GitHub

How beautiful is that?! MySQL 8 really makes it easy to take static data and provide it to the database as a derived table. I'm not sure if you can get more concise than that. Which is important because MySQL has a max packet size that it can accept. Granted, it's pretty huge (defaults to 64mb). But, the more noise that we can remove from these types of SQL statements, the more data we can provide.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4626

Reader Comments

15,996 Comments

@Scott,

Ah, great question. The main different is in what comes back when there is no matching record in the database. Given a list of emails, using the VALUES approach will yield the same list back even if the LEFT OUTER JOIN fails. However, by using IN(), you will only get back the list of emails that exist in the database.

It really just depends on what you're trying to do. In my case, I am often trying to "visually diff" the state of the database against a list that I have. Which is why I always want my list back; and then see which parts of it aren't reflected in the database itself.

Post A Comment — I'd Love To Hear From You!

Markdown formatting: Basic formatting is supported: bold, italic, blockquotes, lists, fenced code-blocks. Read more about markdown syntax »
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.
Cancel
I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
— Ben Nadel