Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.