Row Value Expressions
A row value expression is a list of expressions, separated by commas and enclosed in parentheses. Row value expressions can be used with most comparison predicates and allow for row result comparisons. For example, this WHERE clause:
WHERE empname ='Jones' AND empage = 36 AND salary = 45000
can be written using a row value comparison as follows:
WHERE (empname, empage, salary)=('Jones', 36, 45000)
To test if there is at least one row in employees that has a 36 year old Jones can be achieved with either of the following examples, first written with an EXISTS predicate and then with a row value comparison:
WHERE EXISTS(SELECT 1 FROM employee WHERE empname='Jones' AND empage=36)
WHERE ('Jones', 36) IN (SELECT empname, empage FROM employee)
In the case of equality operator, the rows are considered equal only if all the expressions compared are equal and non-null. For the inequality operator, the rows are considered unequal if all the expressions compared are unequal and non-null. In either case if any of the expressions compared are null, the row test becomes undefined and this row will not be included in the result set.
The row test behaves differently for the comparison operators (<, <=, >=, >). In such cases, the row elements are compared in the text order specified with the evaluation stopping at the first true case. If the expressions compared in the first case are equal and non-null, the row test proceeds to consider the second case and continues in this fashion through the specified row value expression. If the expressions compared are null, the row test becomes undefined, will stop, and this row will not be included in the result set.
WHERE (a, b, c) < (1, 2, 3)
is equivalent to:
WHERE (a < 1) OR (a = 1 and b < 2) OR (a = 1 and b = 2 and c < 3)
In contrast, since columns are tested successively, left-to-right, a null may not be tested if the row is already qualified based on the left-most columns. For example:
WHERE (1,2,3) < (1,3,null)
is treated as true because the 3 < null test is never attempted.
Last modified date: 08/29/2024