SQL Value Expressions

A SQL expression may be simple or complex. The expression can contain literal values, column names, parameters, host variables, subqueries, logical connectives, and arithmetic operators. Regardless of its complexity, an expression must reduce to a single value.

For this reason, SQL expressions are commonly known as value expressions. Combining multiple value expressions into a single expression is possible, as long as the component value expressions reduce to values that have compatible data types.

SQL has five kinds of value expressions:

  • String value expressions

  • Numeric value expressions

  • Datetime value expressions

  • Interval value expressions

  • Conditional value expressions

String value expressions

The simplest string value expression specifies a single string value. Other possibilities include a column reference, a set function, a scalar subquery, a CASE expression, a CAST expression, or a complex string value expression.

Only one operator is possible in a string value expression: the concatenation operator. You may concatenate any of the value expressions with another expression to create a more complex string value expression. A pair of vertical lines (||) represents the concatenation operator. The following table shows some examples of string value expressions.

Expression Produces
‘Peanut ‘ || ‘brittle’ ‘Peanut brittle’
‘Jelly’ || ‘ ‘ || ‘beans’ ‘Jelly beans’
FIRST_NAME || ‘ ‘ || LAST_NAME ‘Joe Smith’
B’1100111' || B’01010011' ’110011101010011'
‘’ || ‘Asparagus’ ‘Asparagus’
‘Asparagus’ || ‘’ ‘Asparagus’
‘As’ || ‘’ || ‘par’ || ‘’ || ‘agus’ ‘Asparagus’

If you concatenate a string to a zero-length string, the result is the same as the original string.

Numeric value expressions

In numeric value expressions, you can apply the addition, subtraction, multiplication, and division operators to numeric-type data. The expression must reduce to a numeric value. The components of a numeric value expression may be of different data types as long as all the data types are numeric.

The data type of the result depends on the data types of the components from which you derive the result. Even so, the SQL standard doesn’t rigidly specify the type that results from any specific combination of source-expression components. That’s because of the differences among hardware platforms. Check the documentation for your specific platform when you’re mixing numeric data types.

Here are some examples of numeric value expressions:

  • –27

  • 49 + 83

  • 5 * (12 – 3)

  • PROTEIN + FAT + CARBOHYDRATE

  • FEET/5280

  • COST * :multiplierA

Datetime value expressions

Datetime value expressions perform operations on data that deal with dates and times. These value expressions can contain components that are of the types DATE, TIME, TIMESTAMP, or INTERVAL. The result of a datetime value expression is always a datetime type (DATE, TIME, or TIMESTAMP). The following expression, for example, gives the date one week from today:

CURRENT_DATE + INTERVAL '7' DAY

Times are maintained in Universal Time Coordinated (UTC) — known in the UK as Greenwich Mean Time — but you can specify an offset to make the time correct for any particular time zone. For your system’s local time zone, you can use the simple syntax given in the following example:

TIME '22:55:00' AT LOCAL

Alternatively, you can specify this value the long way:

TIME '22:55:00' AT TIME ZONE INTERVAL '-08.00' HOUR TO MINUTE

This expression defines the local time as the time zone for Portland, Oregon, which is eight hours earlier than that of Greenwich, England.

Interval value expressions

If you subtract one datetime from another, you get an interval. Adding one datetime to another makes no sense, so SQL doesn’t permit you to do so. If you add two intervals together or subtract one interval from another interval, the result is an interval. You can also either multiply or divide an interval by a numeric constant.

SQL has two types of intervals: year-month and day-time. To avoid ambiguities, you must specify which to use in an interval expression. The following expression, for example, gives the interval in years and months until you reach retirement age:

(BIRTHDAY_65 - CURRENT_DATE) YEAR TO MONTH

The following example gives an interval of 40 days:

INTERVAL '17' DAY + INTERVAL '23' DAY

The example that follows approximates the total number of months that a mother of five has been pregnant (assuming that she’s not currently expecting number six!):

INTERVAL '9' MONTH * 5

Intervals can be negative as well as positive and may consist of any value expression or combination of value expressions that evaluates to an interval.

Conditional value expressions

The value of a conditional value expression depends on a condition. The conditional value expressions CASE, NULLIF, and COALESCE are significantly more complex than the other kinds of value expressions.

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

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.