This topic explains one of the less familiar types of expressions, the row value constructor (RVC), and how it is used with relational operators, such as greater than, less than, and so on.
A row value constructor is an ordered sequence of values that are delimited by parentheses, for example:
(1, 4, 9) ('Smith', 'Lisa')
You can think of this as constructing a row based on a series of elements or values, just like a row of a table is composed of a series of fields.
Row value constructors, like individual values, can be used in comparisons. For example, just as you might have expressions like:
WHERE x > y; WHERE 2 > 1;
you also might have expressions like:
WHERE (2, 3, 4) > (1, 2, 3); WHERE (t1.last_name, t1.first_name) = (t2.last_name, t2.first_name);
Comparisons that use row value constructors must be done carefully.
The previous examples are simple because the expression is correct for each corresponding pair of elements and is therefore true for the RVCs. For example,
'Baker' > 'Alpert' and 'Barbara' > 'Andy', and therefore ('Baker', 'Barbara') > ('Alpert', 'Andy')
However, when comparing row value constructors, it is not necessary that the expression be true for each corresponding element. In a row value constructor, the further left an element is, the more significance it has. Thus the following expressions are also true:
In these examples, since the most significant element of the first RVC is greater than the corresponding element of the second RVC, the expression is true, regardless of the values of the remaining elements. Similarly, in the following examples, the first elements are identical, but the expressions overall are true:
This is useful when comparing multiple columns that are related. For example, suppose that you have 2 tables, each of which has an lname (last name) and an fname (first name) column. The following names are shown in the correct alphabetical order (by last name):
Adams, Zelda Morley, Michael Young, Anna
To find all people whose names come before Michael Morley in the alphabet, you want the last name to have more significance than the first name.
If you want to list all persons whose names come before Michael Morley in the alphabet, then you do NOT want to use the following expression:
table1.lname < 'Morley' and table1.fname < 'Michael'
If you used this expression, you would reject Zelda Adams because the first name is alphabetically after the first name of Michael Morley. One solution is to use the row value constructor approach: