How Does the SQL WHERE Clause's Row-Wise Comparison `(col1, col2) < (val1, val2)` Work?

SQL WHERE Clause Row-Wise Comparisons: Understanding (col1, col2) < (val1, val2)

The SQL expression (col1, col2) < (val1, val2) within a WHERE clause performs a row-wise comparison. This means:

• col1 is compared to val1. If col1 is less than val1, the entire condition is true.
• If col1 equals val1, then col2 is compared to val2. The condition is true only if col2 is less than val2.

Terminology

This type of comparison is often referred to as:

• Row value comparison
• Row constructor comparison
• Row-wise comparison

Applications

A common use case for this syntax is "keyset pagination," an efficient method for retrieving subsets of data from a database table.

Database Support

PostgreSQL stands out among major relational database management systems (RDBMS) for its comprehensive support of row-wise comparisons, including full index utilization.

PostgreSQL Concise Syntax

In PostgreSQL, (col1, col2) < (val1, val2) is the standard and most efficient way to express this comparison.

Equivalent Longer Form

The row-wise comparison is functionally equivalent to a more complex, less efficient expression:

<code class="language-sql">(col1 < val1) OR (col1 = val1 AND col2 < val2)</code>

Multi-Column Index Support (PostgreSQL)

PostgreSQL can leverage multi-column indexes on (col1, col2) or (col1 DESC, col2 DESC) to optimize row-wise comparisons. Note that indexes with mixed ascending and descending columns (e.g., (col1 ASC, col2 DESC)) are not supported for this type of comparison.

Difference from AND Condition

It's crucial to distinguish row-wise comparison from a logical AND condition:

<code class="language-sql">col1 < val1 AND col2 < val2</code>

The AND condition requires both inequalities to be true independently. The row-wise comparison only evaluates the second condition if the first is true.

The above is the detailed content of How Does the SQL WHERE Clause's Row-Wise Comparison `(col1, col2) < (val1, val2)` Work?. For more information, please follow other related articles on the PHP Chinese website!