Using Row Constructor Comparisons In MySQL
In his High Performance SQLite course, Aaron Francis made reference to a feature he referred to as "Row value syntax". This syntax allowed a list of values to be compared directly to another list of values. I had never seen this before; and just assumed it was a SQLite-specific concept. But then, he referenced this syntax once again in his Mastering Postgres course. At that point, I wondered if this was a baseline SQL feature that I didn't know about; and, more specifically, was this something available in MySQL?
Identifying the name of this feature in the MySQL database was a bit of a challenge because any Google search for "Row values" bring you to articles about the VALUES ROW() syntax for creating derived tables. Thankfully, with the help of ChatGPT, I was able to identify the MySQL equivalent as the "Row constructor".
The row constructor syntax allows for multiple values to be compared at the same time. For example:
SELECT
( 1, 2, 7 ) <= ( 1, 4, 3 )
;
This yields TRUE because the second value in the first row constructor, 2, is smaller than the second value in the second row constructor, 4. To rewrite this without the row constructor syntax, it would look like this:
SELECT
( 1 < 1 )
OR
( 1 = 1 AND 2 < 4 ) -- Yields TRUE.
OR
( 1 = 1 AND 2 = 4 AND 7 <= 3 )
;
In these snippets, I'm comparing hard-coded values; but, these could have been column references. To demonstrate, I'll use the VALUES / ROW() syntax that I mentioned above to create a derived table with 3 columns, each of which is in the range, 1...3. This gives us a common table expression (CTE) with 27 rows:
WITH test ( a, b, c ) AS (
VALUES
ROW( 1, 1, 1 ),
ROW( 1, 1, 2 ),
ROW( 1, 1, 3 ),
ROW( 1, 2, 1 ),
ROW( 1, 2, 2 ),
ROW( 1, 2, 3 ),
ROW( 1, 3, 1 ),
ROW( 1, 3, 2 ),
ROW( 1, 3, 3 ),
ROW( 2, 1, 1 ),
ROW( 2, 1, 2 ),
ROW( 2, 1, 3 ),
ROW( 2, 2, 1 ),
ROW( 2, 2, 2 ),
ROW( 2, 2, 3 ),
ROW( 2, 3, 1 ),
ROW( 2, 3, 2 ),
ROW( 2, 3, 3 ),
ROW( 3, 1, 1 ),
ROW( 3, 1, 2 ),
ROW( 3, 1, 3 ),
ROW( 3, 2, 1 ),
ROW( 3, 2, 2 ),
ROW( 3, 2, 3 ),
ROW( 3, 3, 1 ),
ROW( 3, 3, 2 ),
ROW( 3, 3, 3 )
)
Now, I'll query for a chunk of this derived table using the row constructor syntax:
WITH test ( a, b, c ) AS (
-- ... truncated ...
)
SELECT
*
FROM
test
WHERE
( a, b, c ) >= ( 2, 2, 2 )
AND
( a, b, c ) < ( 3, 1, 1 )
;
When we run this MySQL query, we get the following results:
As you can see, the row constructor syntax in MySQL effectively compared all three columns (a, b, and c) at the same time. This syntax is significantly more concise that the expanded equivalent:
WITH test ( a, b, c ) AS (
-- ... truncated ...
)
SELECT
*
FROM
test
WHERE
(
( a > 2 )
OR
( a = 2 AND b > 2 )
OR
( a = 2 AND b = 2 AND c >= 2 )
)
AND
(
( a < 3 )
OR
( a = 3 AND b < 1 )
OR
( a = 3 AND b = 1 AND c < 1 )
)
;
In his courses, Aaron Francis uses this technique to paginate over a table using multiple column values as a virtual cursor. I don't have any immediate need to do this; but, it's good to know that this syntax exists in MySQL as well.
Want to use code from this post? Check out the license.
Reader Comments
As a fast-follow here, given the fact that this syntax is called the "row constructor", I started to wonder if it has any relation to the
ROW()function I used in the derived table. As an experiment, I tried rewriting myWHEREclause as such:... which yields the same results.
One more fast-follow comment, apparently you can use this in row subqueries as well. That is, you can have a subquery that returns multiple columns and then you can compare those multiple columns to another row constructor:
This yields a single result, where the CTE derived table has
(2,2,2)which matches the derived table in the sub-query.This is pretty cool shorthand, but I have absolutely no clue what a practical use for it might be. Zero. Zilch. Nada clue! Have you thought of any use cases for this or did he mention any in the course?
@Chris,
I'm in the same boat—interesting, but not sure how I would actually use something like this. In the video course, he did talk about it in the context of paginating through a table using multiple columns as the "cursor". Basically, doing a
>=comparison on several columns and then combining that with aLIMITto only bring back the next page of results. But, even as he was talking about it, it didn't quite connect with me.@Ben Nadel,
Interesting. I didn't really understand how this could be helpful in pagination, so I consulted the Oracle (ChatGPT) and learned that it would be useful when paginating a filtered or sorted list. No that's interesting! Here's some use cases it came back with...
Row constructor comparisons in MySQL can be highly applicable in real-world scenarios like:
Data Range Filtering: Checking multiple column conditions simultaneously, such as querying orders with specific item IDs and quantities.
Pagination: Ensuring a consistent sort order while fetching the next page of results based on multiple key values (e.g., ID and timestamp).
Data Integrity Checks: Comparing multiple-column combinations between two datasets for validation.
Conditional Joins: Matching rows based on complex multi-column conditions in joins.
These use cases streamline queries, improving readability and efficiency.
@Chris,
Yeah, it's certainly interesting stuff. I don't think I'll be able to make it part of my ongoing mental model until I apply it in a real-world use-case. So, for now, this is here for reference. Hopefully, I'll be able to remember it's here when I need it 😜
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →