4.1.2. SQL Operators

SQL operators comprise operators for comparing, calculating, evaluating and concatenating values.

Operator Precedence

SQL Operators are divided into four types. Each operator type has a precedence, a ranking that determines the order in which operators and the values obtained with their help are evaluated in an expression. The higher the precedence of the operator type is, the earlier it will be evaluated. Each operator has its own precedence within its type, that determines the order in which they are evaluated in an expression.

Operators with the same precedence are evaluated from left to right. To force a different evaluation order, operations can be grouped by means of parentheses.

Table 12. Operator Type Precedence
Operator TypePrecedenceExplanation

Concatenation

1

Strings are concatenated before any other operations take place

Arithmetic

2

Arithmetic operations are performed after strings are concatenated, but before comparison and logical operations

Comparison

3

Comparison operations take place after string concatenation and arithmetic operations, but before logical operations

Logical

4

Logical operators are executed after all other types of operators

Concatenation Operator

The concatenation operator, two pipe characters known as “double pipe” — ‘||’ — concatenates (connects together) two character strings to form a single string. Character strings can be constants or values obtained from columns or other expressions.

Example

  1. SELECT LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME
  2. FROM EMPLOYEE

Arithmetic Operators

Table 13. Arithmetic Operator Precedence
OperatorPurposePrecedence

+signed_number

Unary plus

1

-signed_number

Unary minus

1

*

Multiplication

2

/

Division

2

+

Addition

3

-

Subtraction

3

Example

  1. UPDATE T
  2.     SET A = 4 + 1/(B-C)*D

Where operators have the same precedence, they are evaluated in left-to-right sequence.

Comparison Operators

Table 14. Comparison Operator Precedence
OperatorPurposePrecedence

=

Is equal to, is identical to

1

<>, !=, ~=, ^=

Is not equal to

1

>

Is greater than

1

<

Is less than

1

>=

Is greater than or equal to

1

<=

Is less than or equal to

1

!>, ~>, ^>

Is not greater than

1

!<, ~<, ^<

Is not less than

1

This group also includes comparison predicates BETWEEN, LIKE, CONTAINING, SIMILAR TO, IS and others.

Example

  1. IF (SALARY > 1400) THEN
  2. …

See also

Other Comparison Predicates.

Logical Operators

Table 15. Logical Operator Precedence
OperatorPurposePrecedence

NOT

Negation of a search condition

1

AND

Combines two or more predicates, each of which must be true for the entire predicate to be true

2

OR

Combines two or more predicates, of which at least one predicate must be true for the entire predicate to be true

3

Example

  1. IF (A < B OR (A > C AND A > D) AND NOT (C = D)) THEN …

NEXT VALUE FOR

Available

DSQL, PSQL

NEXT VALUE FOR returns the next value of a sequence. SEQUENCE is an SQL-compliant term for a generator in Firebird and its ancestor, InterBase. The NEXT VALUE FOR operator is equivalent to the legacy GEN_ID (…​, 1) function and is the recommended syntax for retrieving the next sequence value.

Syntax for NEXT VALUE FOR

  1. NEXT VALUE FOR sequence-name

Example

  1. NEW.CUST_ID = NEXT VALUE FOR CUSTSEQ;

Unlike GEN_ID (…​, 1), the NEXT VALUE FOR variant does not take any parameters and thus, provides no way to retrieve the current value of a sequence, nor to step the next value by more than 1. GEN_ID (…​, <step value>) is still needed for these tasks. A step value of 0 returns the current sequence value.

See also

SEQUENCE (GENERATOR), GEN_ID()