Recently, I’ve blogged about how well jOOQ’s supported databases implement row value expressions and predicates formed from them. Some sample articles:

- Row value expressions and the BETWEEN predicate
- Row value expressions and the NULL predicate
- A Typesafety Comparison of SQL Access APIs

Row value expressions (or records, tuples) are useful to express more complex predicates, such as this one:

SELECT * FROM t WHERE (t.t1, t.t2) IN ( SELECT u.u1, u.u2 FROM u )

The above statement semi-joins `u`

to `t`

based on a tuple comparison, not just a single column comparison. This is useful, for example, when you want to select those users from a table whose first AND last name are also contained in another table (without any formal foreign key relationship, of course):

SELECT * FROM users u WHERE (u.first_name, u.last_name) IN ( SELECT a.first_name, a.last_name FROM addresses a )

Now, not all databases really support row value expression predicates. In fact, only very few really do. Here is a non-exhaustive list of databases, that will support some form of the above:

- DB2
- HSQLDB
- MySQL
- Oracle
- Postgres

And these databases pretend they implement row value expression predicates, but get it wrong:

- CUBRID (confusing them with sets)
- H2 (confusing them with arrays)

A feature comparison matrix was listed here:

https://blog.jooq.org/2012/12/26/row-value-expressions-and-the-between-predicate

### Can the above query be simulated?

Yes, it can! And it will be with jOOQ 3.1. Here’s how to transform the above query into an equivalent one, which doesn’t use row value expressions. So, here’s the original query, again:

SELECT * FROM t WHERE (t.t1, t.t2) IN ( SELECT u.u1, u.u2 FROM u )

The above can be transformed into the following query, using an EXISTS predicate

SELECT * FROM t WHERE EXISTS ( SELECT * FROM u WHERE t.t1 = u.u1 AND t.t2 = u.u2 )

Now, in the above simple transformation, we have modified the subselect by changing the projection and by adding a predicate. This can be difficult for more complex subselects, so lets avoid touching it, by introducing another derived table:

SELECT * FROM t WHERE EXISTS ( SELECT * FROM ( SELECT u.u1, u.u2 FROM u -- untouched ) v(v1, v2) -- derived table WHERE t.t1 = v.v1 AND t.t2 = v.v2 )

That’s better. Many databases require renaming derived tables, which is why a derived column list `v(v1, v2)`

was introduced. Not all databases support derived column lists, though, as can be seen in a previous blog post. So lets go on transforming the above into an equivalent query:

SELECT * FROM t WHERE EXISTS ( SELECT * FROM ( SELECT null v1, null v2 -- renaming FROM dual -- if necessary WHERE 1 = 0 -- without new rows UNION ALL SELECT u.u1, u.u2 FROM u -- untouched ) v -- derived table WHERE t.t1 = v.v1 AND t.t2 = v.v2 )

Now, we’ve reached our goal. The above query will run on all databases, retaining the original semantics of a row value expression predicate using a subselect! Note, you possibly have to replace the dual with something more appropriate, of course.

### Does this apply to all comparison predicates?

In principle, yes. Let’s look at a few examples.

**NOT IN**

SELECT * FROM t WHERE (t.t1, t.t2) NOT IN ( SELECT u.u1, u.u2 FROM u ) -- transforms into SELECT * FROM t WHERE NOT EXISTS ( SELECT * FROM ( SELECT null v1, null v2 FROM dual WHERE 1 = 0 UNION ALL SELECT u.u1, u.u2 FROM u ) v WHERE t.t1 = v.v1 AND t.t2 = v.v2 )

**Equality and non-equality**

Equality and non-equality work the same way as `IN`

and `NOT IN`

**IFF** you are operating on scalar subselects. While actual comparison predicates will raise an error if subselects return more than one row, the `EXISTS`

predicate will not. Beware!

**Ordering**

Just as with equality and non-equality, beware of non-scalar subselects!

SELECT * FROM t WHERE (t.t1, t.t2) > ( SELECT u.u1, u.u2 FROM u ) -- transforms into SELECT * FROM t -- EXISTS is not formally correct, -- if the subselect is a non-scalar one WHERE EXISTS ( SELECT * FROM ( SELECT null v1, null v2 FROM dual WHERE 1 = 0 UNION ALL SELECT u.u1, u.u2 FROM u ) v WHERE (t.t1 > v.v1) OR (t.t1 = v.v1 AND t.t2 > v.v2) )

See the previously cited blog post about the BETWEEN predicate to learn how to simulate “ordering” comparison predicates with row value expressions.

**Quantified comparison predicates**

Quantifiers now become quite useful. The `ANY`

quantifier removes the need for having scalar subselects, as in the previous example:

SELECT * FROM t WHERE (t.t1, t.t2) > ANY ( SELECT u.u1, u.u2 FROM u ) -- transforms into SELECT * FROM t -- EXISTS is now formally correct WHERE EXISTS ( SELECT * FROM ( SELECT null v1, null v2 FROM dual WHERE 1 = 0 UNION ALL SELECT u.u1, u.u2 FROM u ) v WHERE (t.t1 > v.v1) OR (t.t1 = v.v1 AND t.t2 > v.v2) )

The `ALL`

quantifier, on the other hand, can be expressed with its inverse `ANY`

quantifier, i.e.

SELECT * FROM t WHERE (t.t1, t.t2) > ALL ( SELECT u.u1, u.u2 FROM u ) -- first transforms into SELECT * FROM t WHERE NOT ((t.t1, t.t2) <= ANY ( SELECT u.u1, u.u2 FROM u )) -- and then transforms into SELECT * FROM t -- (NOT) EXISTS is now formally correct WHERE NOT EXISTS ( SELECT * FROM ( SELECT null v1, null v2 FROM dual WHERE 1 = 0 UNION ALL SELECT u.u1, u.u2 FROM u ) v WHERE (t.t1 < v.v1) OR (t.t1 = v.v1 AND t.t2 <= v.v2) )

### Conclusion

Happy transforming, and keep an eye out for jOOQ 3.1, conveniently implementing all of the above behind a type-safe Java API!

### Disclaimer

Yes, `NULL`

s. The above transformation deliberately left out edge cases where NULLs are involved.

Thanks for great blog posts! Looks like a small mistake has slipped in here

The ALL quantifier can be expressed with the _negation_ of its inverse ANY quantifier, that is

expression > ALL (expression)

becomes

not expression <= ANY (expression)

see http://sqlfiddle.com/#!17/7c27d/4

You’re right, thanks a lot for the hint. By consequence, the subsequently transformed version using EXISTS should use NOT EXISTS as well. Fixed!