Understanding SQL Expressions

From InterBase

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 (:).


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.


SELECT statements, typically nested in WHERE clauses, that return values to be compared with the result set of the main SELECT statement.


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:

(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:

INTO :wellpaid

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


Advance To: