You are currently viewing Using VALUES / ROW To Create Derived Table From Static Values In MySQL 8.0.19

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

[ad_1]

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.


https://bennadel.com/go/4626

[ad_2]
Source link

Gadget Explorer Pro

Gadget Explorer Pro is an expert writer specializing in comprehensive reviews, insightful articles, and detailed comparisons of consumer electronics. With a passion for technology and years of experience, they offer unbiased analysis of the latest gadgets, from cameras to smart home devices. Known for making complex tech understandable, Gadget Explorer Pro helps readers make informed decisions. Follow them for expert advice and the latest trends in the world of technology.

Leave a Reply