How to Use Value Expressions with SQL
If a Data Manipulation Language (DML) statement includes multiple expressions, understanding what that SQL statement is trying to do can be a challenge. You can use value expressions to combine two or more values. Several kinds of value expressions exist, corresponding to the different data types:
The Boolean, user-defined, row, and collection types were introduced with SQL:1999. Some implementations may not support them all yet. If you want to use these data types, make sure your implementation includes the ones you want to use.
Numeric value expressions
To combine numeric values, use the addition (+), subtraction (-), multiplication (*), and division (/) operators. The following lines are examples of numeric value expressions:
12 – 7 15/3 - 4 6 * (8 + 2)
The values in these examples are numeric literals. These values may also be column names, parameters, host variables, or subqueries — provided that those column names, parameters, host variables, or subqueries evaluate to a numeric value. The following are some examples:
SUBTOTAL + TAX + SHIPPING 6 * MILES/HOURS :months/12
The colon in the last example signals that the following term (months) is either a parameter or a host variable.
String value expressions
String value expressions may include the concatenation operator (||).
Some SQL implementations use + as the concatenation operator rather than ||. Check your documentation to see which operator your implementation uses.
Some implementations may include string operators other than concatenation, but ISO-standard SQL doesn’t support such operators. Concatenation applies to binary strings as well as to text strings.
Datetime and interval value expressions
Datetime value expressions deal with dates and times. Data of DATE, TIME, TIMESTAMP, and INTERVAL types may appear in datetime value expressions. The result of a datetime value expression is always another datetime. You can add or subtract an interval from a datetime and specify time zone information.
Here’s an example of a datetime value expression:
DueDate + INTERVAL '7' DAY
A library may use such an expression to determine when to send a late notice. The following example specifies a time rather than a date:
TIME '18:55:48' AT LOCAL
The AT LOCAL keywords indicate that the time refers to the local time zone.
Interval value expressions deal with the difference (how much time passes) between one datetime and another. You have two kinds of intervals: year-month and day-time. You can’t mix the two in an expression.
As an example of an interval, suppose someone returns a library book after the due date. By using an interval value expression such as that of the following example, you can calculate how many days late the book is and assess a fine accordingly:
(DateReturned - DateDue) DAY
Because an interval may be of either the year-month or the day-time variety, you need to specify which kind to use. (In the preceding example, DAY has been specified.)
Boolean value expressions
A Boolean value expression tests the truth value of a predicate. The following is an example of a Boolean value expression:
(Class = SENIOR) IS TRUE
If this were a condition on the retrieval of rows from a student table, only rows containing the records of seniors would be retrieved. To retrieve the records of all non-seniors, you could use the following:
NOT (Class = SENIOR) IS TRUE
Alternatively, you could use:
(Class = SENIOR) IS FALSE
To retrieve every row that has a null value in the CLASS column, use
(Class = SENIOR) IS UNKNOWN
User-defined type value expressions
If necessary, you can define your own data types instead of having to settle for those provided by stock SQL. Expressions that incorporate data elements of such a user-defined type must evaluate to an element of the same type.
Row value expressions
A row value expression, not surprisingly, specifies a row value. The row value may consist of one value expression, or two or more comma-delimited value expressions. For example:
('Joseph Tykociner', 'Professor Emeritus', 1918)
This is a row in a faculty table, showing a faculty member’s name, rank, and year of hire.
Collection value expressions
A collection value expression evaluates to an array.
Reference value expressions
A reference value expression evaluates to a value that references some other database component, such as a table column.