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

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

`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
)
```

- DB2
- HSQLDB
- MySQL
- Oracle
- Postgres

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

### 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
)
```

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

```
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
)
```

`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
)
```

### 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)
)
```

**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)
)
```

`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!