Understanding SQL Expressions

Go Up to Working with Data

All SQL data manipulation statements support SQL expressions, SQL syntax for comparing and evaluating columns, constants, and host-language variables to produce a single value.

In the SELECT statement, for example, the WHERE clause is used to specify a search condition that determines if a row qualifies for retrieval. That search condition is a SQL expression. DELETE and UPDATE also support search condition expressions. Typically, when an expression is used as a search condition, the expression evaluates to a Boolean value that is True, False, or Unknown.

SQL expressions can also appear in the INSERT statement VALUE clause and the UPDATE statement SET clause to specify or calculate values to insert into a column. When inserting or updating a numeric value via an expression, the expression is usually arithmetic, such as multiplying one number by another to produce a new number which is then inserted or updated in a column. When inserting or updating a string value, the expression may concatenate, or combine, two strings to produce a single string for insertion or updating.

The following table describes the elements that can be used in expressions:

Element	Description
Column names	Columns from specified tables, against which to search or compare values, or from which to calculate values.
Host-language ­variables	Program variables containing changeable values. Host-language variables must be preceded by a colon (:).
Constants	Hard-coded numbers or quoted strings, like 507 or “Tokyo”.
Concatenation operator	Used to combine character strings.
Arithmetic operators	+, –, *, and /, used to calculate and evaluate values.
Logical operators	Keywords, NOT, AND, and OR, used within simple search conditions, or to combine simple search conditions to make complex searches. A logical operation evaluates to true or false. Usually used only in search conditions.
Comparison operators	<, >, <=, >=, =, and <>, used to compare a value on the left side of the operator to another on the right. A comparative operation evaluates to true or false. Other, more specialized comparison operators include ALL, ANY, BETWEEN, CONTAINING, EXISTS, IN, IS[NOT]NULL, LIKE, SINGULAR, SOME, and STARTING WITH. These operators can evaluate to True, False, or Unknown. They are usually used only in search conditions.
COLLATE clause	Comparisons of CHAR and VARCHAR values can sometimes take advantage of a COLLATE clause to force the way text values are compared.
Stored procedures	Reusable SQL statement blocks that can receive and return parameters, and that are stored as part of a database metadata.
Subqueries	SELECT statements, typically nested in WHERE clauses, that return values to be compared with the result set of the main SELECT statement.
Parentheses	Used to group expressions into hierarchies; operations inside parentheses are performed before operations outside them. When parentheses are nested, the contents of the innermost set is evaluated first and evaluation proceeds outward.
Date literals	String values that can be entered in quotes, that will be interpreted as date values in SELECT, INSERT, and UPDATE operations. Possible strings are ‘TODAY’, ‘NOW’, ‘YESTERDAY’, and ‘TOMORROW’.
The USER pseudocolumn	References the name of the user who is currently logged in. For example, USER can be used as a default in a column definition or to enter the current user name in an INSERT. When a user name is present in a table, it can be referenced with USER in SELECT and DELETE statements.

Complex expressions can be constructed by combining simple expressions in different ways. For example the following WHERE clause uses a column name, three constants, three comparison operators, and a set of grouping parentheses to retrieve only those rows for employees with salaries between $60,000 and $120,000:

WHERE DEPARTMENT = 'Publications' AND
(SALARY > 60000 AND SALARY < 120000)

As another example, search conditions in WHERE clauses often contain nested SELECT statements, or subqueries. In the following query, the WHERE clause contains a subquery that uses the aggregate function, AVG(), to retrieve a list of all departments with bigger than average salaries:

EXEC SQL
DECLARE WELL_PAID CURSOR FOR
SELECT DEPT_NO
INTO :wellpaid
FROM DEPARTMENT
WHERE SALARY > (SELECT AVG(SALARY) FROM DEPARTMENT);

For more information about using subqueries to specify search conditions, see Using Subqueries. For more information about aggregate functions, see Retrieving Aggregate Column Information.

Topics

• NOT Operator Optimizations
• Using the String Operator in Expressions
• Using Arithmetic Operators in Expressions
• Using Logical Operators in Expressions
• Using Comparison Operators in Expressions
• Determining Precedence of Operators
• Using CAST( ) for Data Type Conversions
• Using UPPER() on Text Data

Advance To:

• NOT Operator Optimizations