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.

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

Reader Comments

Ben Nadel Dec 10, 2024 at 11:55 AM

15,902 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 my WHERE clause as such:

WHERE
	ROW( a, b, c ) >= ROW( 2, 2, 2 )
AND
	ROW( a, b, c ) < ROW( 3, 1, 1 )
;

... which yields the same results.

Ben Nadel Dec 10, 2024 at 11:59 AM

15,902 Comments

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:

FROM
	test
WHERE
	( a, b, c ) = (

		SELECT
			2, -- a
			2, -- b
			2  -- c

	)
;

This yields a single result, where the CTE derived table has (2,2,2) which matches the derived table in the sub-query.

Chris G Dec 11, 2024 at 9:53 PM

248 Comments

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?

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

Post a Comment →