In the world of SQL, sometimes you need to conjure up rows of literal data out of thin air, instead of pulling data from an existing table. The context of this post is when you, the SQL coder, need to define each row of literals individually. Maybe you're populating a table for testing purposes - the precise use case isn't too important. Note I am not referring to the context of generating a series by calling a function (post about how to do that), or otherwise programmatically generating a number of random rows out of thin air. Rather, you want to list the rows out one by one.
That foundation laid out, my claim is that I've generally found it is better to use your DBMS of choice's row constructor syntax than what I've more commonly seen: a bunch of SELECTs containing only literals that are UNION ALL'ed together. For example:
SELECT 1,2,3
UNION ALL
SELECT 4,5,6
UNION ALL
SELECT 7,8,9
...
Rather than writing the above syntax, there are benefits to taking a row constructor approach, which looks like the following in MySQL.
VALUES
ROW(1,2,3),
ROW(4,5,6),
ROW(7,8,9),
...
Let me clarify that last bit: this post is specifically about MySQL and
the above syntax is MySQL specific. No specific tests or under-the-hood workings demonstrated below should be generalized
beyond the scope of MySQL. That said, I've found the principle does tend to apply elsewhere, but I will not try to support my claim for any other DBMS than MySQL in this post.
Without further ado...
Why use row constructors over a bunch of UNION ALL'ed SELECTs?
1) Performance
The UNION ALL approach takes greater wall clock time to execute. I quickly ran a not-super-scientific benchmark (MySQL 8.0.x, 2024 Macbook Pro, usual YMMV disclaimer applies) of the two approaches on 10,000 rows (the same exact data is constructed in each) and the difference was night and day:
Generating 10000 constant rows...
Generating UNION ALL SQL in union.sql...
Generating VALUES ROW(...) SQL in values.sql...
=== Running UNION ALL ===
real 3.95
user 0.04
sys 0.01
=== Running VALUES ROW ===
real 0.08
user 0.03
sys 0.00
Times were about consistent with repeated trials. The discrepancy would likely be less pronounced with smaller data sets. And of course, if you're cooking up a tiny number of rows, say 5, I doubt you'll notice any tangible difference.