Operators

Arithmetic Operators

The expressions and the result of arithmetic operations are each numeric values.

Addition

<expression-1> + <expression-2>

Adds the two expressions.

Division

<expression-1> / <expression-2>

Divides the 1st expression by the 2nd expression.

Modulo

<expression-1> % <expression-2>

Determines the remainder when dividing the 1st expression by the 2nd expression.

Multiplication

<expression-1> * <expression-2>

Forms the product of the two expressions.

Subtraction

<expression-1> - <expression-2>

Subtracts the 2nd expression from the 1st expression.

Sign -

- <expression>

Returns the expression with reversed sign.

Sign +

+ <expression>

Returns the expression unchanged.

Character Operators

The expressions and the result of character operations are each character strings.

Concat

<expression-1> || <expression-2>

Concatenates the two expressions into a character string.

Comparing Two Expressions of the Same Data Type

The results of comparison operations are each boolean values.

The expressions to be compared must have the same data type.

Equal

<expression-1> = <expression-2>
  • NULL, if one of the two expressions is NULL
  • True if both expressions are not NULL and equal
  • False if both expressions are not NULL and unequal

Identical

<expression-1> == <expression-2>
  • True if both expressions are equal (both not null and equal or both NULL)
  • NULL, if only one of the two expressions is NULL
  • False otherwise (both unequal and not NULL)
SELECT NULL = NULL, NULL == NULL, 'a' == NULL, 'a' == 'b', 'a' == 'a';

Output

column_1 column_2 column_3 column_4 column_5
-------- -------- -------- -------- --------
null     true     null     false    true

Not Equal

<expression-1> != <expression-2>
or
<expression-1> <> <expression-2>
  • NULL, if one of the two expressions is NULL
  • True if both expressions are not NULL and unequal
  • False if both expressions are not NULL and equal

Greater Than

<expression-1> > <expression-2>
  • NULL, if one of the two expressions is NULL
  • True if both expressions are not NULL and the first expression is greater than the 2nd
  • False if both expressions are not NULL and the first expression is less than or equal to the 2nd

Greater Than or Equal

<expression-1> >= <expression-2>
  • NULL, if one of the two expressions is NULL
  • True if both expressions are not NULL and the first expression is greater than or equal to the 2nd
  • False if both expressions are not NULL and the first expression is less than the 2nd

Less Than

<expression-1> < <expression-2>
  • NULL, if one of the two expressions is NULL
  • True if both expressions are not NULL and the first expression is less than the 2nd
  • False if both expressions are not NULL and the first expression is greater than or equal to the 2nd

Less Than or Equal

<expression-1> <= <expression-2>
  • NULL, if one of the two expressions is NULL
  • True if both expressions are not NULL and the first expression is less than or equal to the 2nd
  • False if both expressions are not NULL and the first expression is greater than the 2nd

Boolean Operators

The expressions and the result of boolean operations are each of type Boolean.

Logical And

<expression-1> AND <expression-2>

And for boolean expressions

  • False, if one of the two expressions is False
  • NULL, if one of the two expressions is NULL
  • True otherwise

Logical Or

<expression-1> OR <expression-2>

Or for boolean expressions

  • True, if one of the two expressions is True
  • NULL, if one of the two expressions is NULL
  • False otherwise

Logical Not

NOT <expression>

Negation of a boolean expression

  • NULL, if the expression is NULL
  • False, if the expression is True
  • True otherwise

Other Comparison Operators

Comparison with NULL

<expression> IS [ NOT ] NULL

The expression can have any data type.

NOT is optional and negates the result

  • True if the expression is NULL
  • False if the expression is not NULL

The result has the data type BOOLEAN.

SELECT NULL is NULL, 'a' IS NULL, 'b' is not null;

Output

column_1 column_2 column_3
-------- -------- --------
true     false    true

Pattern Comparison with "%" and "_"

<expression-1> LIKE <expression-2>

Both expressions are character strings.

Result of the comparison:

  • Null if one of the two operands is NULL
  • True if the 1st expression matches the 2nd expression. Only the SQL standard placeholders "%" and "_" can be used.
  • False otherwise.
SELECT 'huhu.xlsx' LIKE '%.xlsx', not 's12' like '_' ;

Output

column_1 column_2
-------- --------
true     true

To negate the expression, use "not a like b" instead of "a not like b".

Pattern Comparison with Regular Expressions

<expression-1> REGEXP_LIKE <expression-2>

Both expressions are character strings.

Result of the comparison:

  • Null if one of the two operands is NULL
  • True if the 1st expression matches the 2nd expression. The comparison uses the Java Pattern class.
  • False otherwise.
SELECT 'huhu.xlsx' REGEXP_LIKE '.*\.xlsx', '18237636' REGEXP_LIKE '[0-9]*', not 's12' regexp_like '.' ;

Output

column_1 column_2 column_3
-------- -------- --------
true     true     true

Case Distinctions

CASE
  WHEN <boolean-expression-1> THEN <expression-1>
  ...
  WHEN <boolean-expression-n> THEN <expression-n>
  [ ELSE <expression-else> ]
END

The expressions in THEN and ELSE must all have the same data type. The result also has this data type.

SELECT CASE WHEN 73*54 = 72 THEN 'Is 72' WHEN 73*54 = 3942 THEN 'Is 3942' ELSE 'No idea' END, CASE WHEN 1 IS NULL THEN 7 END;

Output

column_1 column_2
-------- --------
Is 3942 null