Using VALUES / ROW To Create Derived Table From Static Values In MySQL 8.0.19
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'
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
;
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
;
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
;
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
;
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.
Reader Comments
How is that more concise than:
select email, id from member where email in ({list of email addresses})?
@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
VALUESapproach will yield the same list back even if theLEFT OUTER JOINfails. However, by usingIN(), 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! ❤️
Post a Comment →