### The BETWEEN predicate

The BETWEEN predicate is a convenient form of expressing the fact that one expression A should be in BETWEEN two other expressions B and C. This predicate was defined already in §8.4 of SQL-1992, and then refined in SQL-1999 (adding ASYMMETRIC/SYMMETRIC):8.3 <between predicate> Function Specify a range comparison. Format <between predicate> ::= <row value expression> [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] <row value expression> AND <row value expression>While ASYMMETRIC is just a verbose way of expressing the default behaviour of the BETWEEN predicate, SYMMETRIC has the useful property of indicating that the order of B and C is irrelevant. Knowing this, the following transformations can be established:

### BETWEEN predicate transformations

The following statements are all equivalent:```
A BETWEEN SYMMETRIC B AND C
(A BETWEEN B AND C) OR (A BETWEEN C AND B)
(A >= B AND A <= C) OR (A >= C AND A <= B)
```

```
-- The original statement
(A1, A2) BETWEEN SYMMETRIC (B1, B2) AND (C1, C2)
-- Transforming away BETWEEN SYMMETRIC
( (A1, A2) >= (B1, B2)
AND (A1, A2) <= (C1, C2) )
OR ( (A1, A2) >= (C1, C2)
AND (A1, A2) <= (B1, B2) )
-- Transforming away the row value expressions
( ((A1 > B1) OR (A1 = B1 AND A2 > B2) OR (A1 = B1 AND A2 = B2))
AND ((A1 < C1) OR (A1 = C1 AND A2 < C2) OR (A1 = C1 AND A2 = C2)) )
OR ( ((A1 > C1) OR (A1 = C1 AND A2 > C2) OR (A1 = C1 AND A2 = C2))
AND ((A1 < B1) OR (A1 = B1 AND A2 < B2) OR (A1 = B1 AND A2 = B2)) )
```

### Native SQL support for row value expressions and BETWEEN SYMMETRIC

Here’s a comprehensive list of the 14 SQL dialects supported by jOOQ, and what is natively supported by them:Database | BETWEEN SYMMETRIC | RVE = RVE | RVE < RVE | RVE BETWEEN |
---|---|---|---|---|

CUBRID ^{[1]} |
no | yes | no | no |

DB2 | no | yes | yes | yes |

Derby | no | no | no | no |

Firebird | no | no | no | no |

H2 ^{[2]} |
no | yes | yes | yes |

HSQLDB | yes | yes | yes | yes |

Ingres | yes | no | no | no |

MySQL | no | yes | yes | no |

Oracle | no | yes | no | no |

Postgres | yes | yes | yes | yes |

SQL Server | no | no | no | no |

SQLite | no | no | no | no |

Sybase ASE | no | no | no | no |

Sybase SQL Anywhere | no | no | no | no |

- The
**BETWEEN SYMMETRIC**column indicates, whether the database supports the SYMMETRIC keyword in general - The
**RVE = RVE**column indicates, whether the database supports row value expressions in general (e.g. in equal comparison predicates) - The
**RVE < RVE**column indicates, whether the database supports “ordering” comparison predicates (<, <=, >, >=) along with row value expressions - The
**RVE BETWEEN**column indicates, whether the database supports the BETWEEN predicates along with row value expressions

## 4 thoughts on “Row value expressions and the BETWEEN predicate”