SQL Series...25

Row Value Expressions

In SQL-86 and SQL-89, most operations deal with a single value or a single column in a table row. To operate on multiple values, you must build complex expressions by using logical connectives.

SQL-92 introduced row value expressions, which operate on a list of values or columns rather than a single value or column. A row value expression is a list of value expressions that you enclose in parentheses and separate by commas. You can operate on an entire row at once or on a selected subset of the row.

A previous article covers, how to use the INSERT statement to add a new row to an existing table. To do so, the statement uses a row value expression. Consider the following example:


INSERT INTO FOODS

(FOODNAME, CALORIES, PROTEIN, FAT, CARBOHYDRATE)
VALUES
(‘Cheese, cheddar’, 398, 25, 32.2, 2.1) ;

In this example, (‘Cheese, cheddar’, 398, 25, 32.2, 2.1) is a row value expression. If you use a row value expression in an INSERT statement this way, it can contain null and default values. (A default value is the value that a table column assumes if you specify no other value.) The following line, for example, is a legal row value expression:


(‘Cheese, cheddar’, 398, NULL, 32.2, DEFAULT)

You can add multiple rows to a table by putting multiple row value expres-sions in the VALUES clause, as follows:

INSERT INTO FOODS

(FOODNAME, CALORIES, PROTEIN, FAT, CARBOHYDRATE)

VALUES

(‘Lettuce’, 14, 1.2, 0.2, 2.5),

(‘Margarine’, 720, 0.6, 81.0, 0.4),

(‘Mustard’, 75, 4.7, 4.4, 6.4),

(‘Spaghetti’, 148, 5.0, 0.5, 30.1) ;

You can use row value expressions to save typing in comparisons. Suppose you have two tables of nutritional values, one compiled in English and the other in Spanish. You want to find those rows in the English language table that correspond exactly to the rows in the Spanish language table. Without a row value expression, you may need to formulate something like the follow-ing example:

SELECT * FROM FOODS

WHERE FOODS.CALORIES = COMIDA.CALORIA

AND FOODS.PROTEIN = COMIDA.PROTEINA

AND FOODS.FAT = COMIDA.GORDO

AND FOODS.CARBOHYDRATE = COMIDA.CARBOHIDRATO ;

Row value expressions enable you to code the same logic as follows:

SELECT * FROM FOODS

WHERE (FOODS.CALORIES, FOODS.PROTEIN, FOODS.FAT,

FOODS.CARBOHYDRATE)

=

(COMIDA.CALORIA, COMIDA.PROTEINA, COMIDA.GORDO, COMIDA.CARBOHIDRATO) ;

In this example, you don’t save much typing. You would benefit slightly more if you were comparing more columns. In cases of marginal benefit like this example, you may be better off sticking with the older syntax because its meaning is clearer.

You gain one benefit by using a row value expression instead of its coded equivalent — the row value expression is much faster. In principle, a clever implementation can analyze the coded version and implement it as the row value version, but in practice, this operation is a difficult optimization that no DBMS currently on the market can perform.