Operators

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks >

Operators

Previous pageReturn to chapter overviewNext page

Subquery Operators

Operators are used in queries and subqueries to combine or test data for various properties, attributes, or relationships.

The available operators are described in the topics that follow, grouped into the following categories:

Scalar Operators

oOperator Types

oPrecedence

Arithmetic Operators

String Operators

o (Concatenation)

oLIKE patterns

oSIMILAR TO patterns

Date, Timestamp, and Interval Operators, including Rules for Specifying Intervals

Logical Operators

o3-state boolean logic

oExamples

IN operator

As an operator in a condition test, IN tests a scalar or row value for membership in a list of values, a relational expression, or a subquery.

Examples:

1. --- IF column IN ('A','B','C')

2. --- IF (col1, col2) IN (

   select a, b from my_table

   )

Returns TRUE if the value being tested is found in the list, in the result of evaluating the relational expression, or in the rows returned by the subquery; returns FALSE otherwise.

(IN has a different meaning and use in CREATE FUNCTION or CREATE PROCEDURE.)

EXISTS operator

Tests whether a relational expression returns any rows; returns TRUE if any row is returned, FALSE otherwise.

Scalar Operators

Operator types

The two general classes of scalar operators are:

unary: A unary operator operates on only one operand. A unary operator typically appears with its operand in this format:

      operator operand

binary: A binary operator operates on two operands. A binary operator appears with its operands in this format:

operand1 operator operand2

 

A few operators that use a different format are noted specifically in the operand descriptions below.

If an operator is given a null operand, the result is almost always null (see the topic on logical operators for exceptions).

Precedence

Streaming SQL follows the usual precedence of operators:

1.Evaluate bracketed sub-expressions.

2.Evaluate unary operators (e.g., + or -, logical NOT).

3.Evaluate multiplication and divide (* and /).

4.Evaluate addition and subtraction (+ and -) and logical combination (AND and OR).

Arithmetic Operators

Operator

Unary/Binary

Description

+

U

Identity

-

U

Negation

+

B

Addition

-

B

Subtraction

*

B

Multiplication

/

B

Division

 

Each of these operators works according to normal arithmetic behavior, with the following caveats:

1.If one of the operands is NULL, the result is also NULL

2.If the operands are of different but comparable types, the result will be of the type with the greatest precision.

3.If the operands are of the same type, the result will be of the same type as the operands. For instance 5/2 = 2, not 2.5, as 5 and 2 are both integers.

Examples:

Operation

Result

1 + 1

2

2.0 + 2.0

4.0

3.0 + 2

5.0

5 / 2

2

5.0 / 2

2.500000000000

5*2+2

12