SQL syntax term for 'WHERE (col1, col2) < (

问题:

As my question states, I would like to know what we call types of queries with that type of condition in the WHERE clause, i.e.:

SELECT * FROM mytable
WHERE (col1, col2) < (1, 2);

In other words:
Give me all records where col1 is less than '1' or if it equals '1' then col2 must be less than '2' - and none of the values are NULL.

I really like this type of syntax, but don't know what the naming convention is on how to refer to this type of condition. It looks like a tuple conditional but that name is not giving me anything from my searches.

My question stems from needing to know what this syntax is called in order to research how to write this using Criteria API with Hibernate and JPA2 and Postgres.

EDIT

I was able to write this using Criteria API using CriteriaBuilder's function() call:

//Our left expression (date, id)
Expression leftVal = criteriaBuilder.function("ROW", Tuple.class,     
        from.get("date").as(java.util.Date.class),
        from.get("id").as(Long.class));

//Our right expression ex: ('2015-09-15', 32450)
ParameterExpression<Date> dateParam = criteriaBuilder.parameter(Date.class);
ParameterExpression<Long> idParam = criteriaBuilder.parameter(Long.class);
Expression rightVal = criteriaBuilder.function("ROW", Tuple.class, dateParam, idParam)

//build the first predicate using ROW expressions
Predicate predicate = criteriaBuilder.greaterThan(leftVal, rightVal);

//more query building happens
... 

//construct final query and add parameters to our param expressions
TypedQuery<MyEntity> typedQuery = em.createQuery(criteriaQuery);
typedQuery.setParameter(dateParam, current.getDate());
typedQuery.setParameter(idParam, current.getId());

current in this case is the record I retrieve as the row we want to get records BEFORE or AFTER. In this example I do after as noted by the greaterThan function call.

回答1:

The common short term is just "Row values". Or "Row value comparison" for the operation you demonstrate. That feature has been in the SQL standard since SQL-92 (!). Postgres is currently the only major RDBMS that supports it in all aspects - especially also with optimal index support.

In particular, the expression (col1, col2) < (1, 2) is just shorthand for ROW(col1, col2) < ROW(1, 2) in Postgres. The expression ROW(col1, col2) is also called row constructor - just like ARRAY[col1, col2] is an array constructor.

It is conveniently short for the more verbose, equivalent expression:

col1 < 1 OR (col1 = 1 AND col2 < 2)

... and Postgres can use an index on (col1, col2) or (col1 DESC, col2 DESC) for this.

And notably distinct from (!)

col1 < 1 AND  AND col2 < 2

Consider example: (1,1) ...

Here is a presentation by Markus Winand that discusses the feature for pagination in detail:

"Pagination done the PostgreSQL way" on use-the-index-luke.com.

Row value comparison starts on page 20. The support matrix I have been referring to is on page 45.

_{I am in no way affiliated to any of the sources I quoted.}

回答2:

WHERE (col1, col2) < (val1, val2)

Above syntax is called Row Value Constructor/tuple syntax/Row Subquery.

From doc

ANSI SQL row value constructor syntax, sometimes referred to AS tuple syntax, even though the underlying database may not support that notion. Here, we are generally referring to multi-valued comparisons, typically associated with components

Alternatively it can be called Row Subqueries

回答3:

Expression Lists

Use of Row Value Constructors (RVC) in Comparison Predicates - quite a long term

RVCs are usually seen in INSERT statements, but rarely as part of WHERE clauses.

I doubt that this syntax has a direct support in JPA or Hibernate Criteria API, but there is always a workaround to achieve the same logic.