This section 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 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/values, just like a row of a table is composed of a series of fields.
Row value constructors, like individual values, may be used in comparisons. For example, just as you may have expressions like:
WHERE x > y;
WHERE 2 > 1;
you also may have expressions like:
WHERE (2, 3, 4) > (1, 2, 3);
WHERE (t1.last_name, t1.first_name) = (t2.last_name, t2.first_name);
Comparisons using row value constructors must be done carefully. Rather than give the technical definition of comparisons (which you can find in section 8.2 (comparison predicates) of the SQL-92 standard), we will give examples and an analogy to help you see the pattern.
The following expressions are true:
(9, 9, 9) > (1, 1, 1)
('Baker', 'Barbara') > ('Alpert', 'Andy')
(1, 1) = (1, 1)
(3, 2, 1) <> (4, 3, 2)
The examples above 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:
(9, 1, 1) > (1, 9, 9)
('Zoomer', 'Andy') > ('Alpert', 'Zelda')
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 examples below, the first elements are identical, but the expressions overall are true:
(1, 1, 2) > (1, 1, 1)
(1, 2, 1) > (1, 1, 1)
('Baker', 'Zelda') > ('Baker', 'Allison')
Again, in a row value constructor, the further left an element is, the more significance it has. This is similar to the way that we compare multi-digit numbers. In a 3-digit number, such as 911, the hundreds-place digit is more significant than the tens-place digit, and the tens-place digit is more significant than the ones-place digit. Thus, the number 911 is greater than the number 199, even though not all digits of 911 are greater than the corresponding digits of 199.
This is useful when comparing multiple columns that are related. A practical application of this is when comparing people’s names. For example, suppose that we have 2 tables, each of which has an lname (last name) and fname (first name) column. Suppose that we want to find all people whose names are less than Michael Morley’s. In this situation, we want the last name to have more significance than the first name. The following names are shown in the correct alphabetical order (by last name):
Adams, Zelda Morley, Michael Young, Anna
If we want to list all persons whose names are less than Michael Morley’s, then wedo NOT want to use the following:
table1.lname < 'Morley' and table1.fname < 'Michael'
If we used this expression, we would reject Zelda Adams because her first name is alphabetically after Michael Morley’s first name. One correct solution is to use the row value constructor approach: