How to Use SQL Row Value Expressions
In the original SQL standards, SQL-86 and SQL-89, most operations dealt with a single value or a single column in a table row. To operate on multiple values, you had to build complex expressions by using logical connectives.
SQL-92 introduced row value expressions, which operate on a list of values or columns rather than on 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 code these expressions to operate on an entire row at once or on a selected subset of the row.
To use the INSERT statement to add a new row to an existing table, 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 expressions in the VALUES clause, as follows:
INSERT INTO FOODS (FOODNAME, CALORIES, PROTEIN, FAT, CARBOHYDRATE) VALUES ('Lettuce', 14, 1.2, 0.2, 2.5), ('Butter', 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 yourself from having to enter comparisons manually. 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 following example:
SELECT * FROM FOODS WHERE FOODS.CALORIES = COMIDA.CALORIA AND FOODS.PROTEIN = COMIDA.PROTEINAS AND FOODS.FAT = COMIDA.GRASAS 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.PROTEINAS, COMIDA.GRASAS, 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. In practice, this operation is a difficult optimization that no DBMS currently can perform.