Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Operators


Operators manipulate individual data items (operands) and return a result. Mimer SQL uses the following operators:

Set Operators

UNION or UNION ALL

Derives a final result set by combining two other result sets.

If you specify UNION ALL, the result consists of all rows in both results sets.

If you only specify UNION, the final result set is the set of all rows in both of the result sets, with duplicate rows removed.

See The UNION Operator for more information.

EXCEPT or EXCEPT ALL

Mimer SQL Experience
The except operator is used to combine two result sets to one where the combined result set is all records from the first result which is not present in the second result set. If except is specified without the ALL quantifier, duplicates are removed from the combined result set. If ALL is specified, duplicates are not removed.
See The EXCEPT Operator for more information.

INTERSECT or INTERSECT ALL

Mimer SQL Experience
The intersect operator is used to combine two result sets to one where the combined result set is the records that are present in both result sets. If intersect is specified without the ALL quantifier, duplicates are removed from the combined result set. If ALL is specified, duplicates are not removed.
See The INTERSECT Operator for more information.

Arithmetical Operators

Arithmetical operators are used in forming expressions, see Expressions.

The operators are:

Unary Arithmetical

+
leaves operand unchanged
-
changes sign of operand

Binary Arithmetical

+
addition
-
subtraction
*
multiplication
/
division
%
modulo

Mimer SQL Engine
Mimer SQL Engine does not support the % operator for modulo. Use the MOD function instead. (See MOD.)
 select 13 % 5 from...
 
is equal to
 select MOD(13, 5) from...

String Operators

String operators are used in forming expressions, see Expressions.

String

||
concatenation

Bit Operators

Bit operations:

&
bitwise AND operation
|
bitwise OR operation
~
bitwise complement
<<
left shift
>>
right shift

Bit operations are support on the data types INTEGER and BIGINT. I.e. the operations are performed on the integer bit representation. When right shift is performed a so called arithmetic shift is performed. This means that the sign bit will be used to replace the bits as the shift to the right is made.

Examples

Expression
Result
set ? = 9 | 3
11
set ? = cast(x'0f23' as int) &
        cast(x'fff0' as int)
3872
set ? = cast(cast(x'0f23' as int) &         cast(x'fff0' as int) as binary(4))
X'00000F20'
select cast(~(1 << 7) as binary(8))
from information_schema.ext_onerow
X'FFFFFFFFFFFFFF7F'

Mimer SQL Engine
Mimer SQL Engine does not support the bit operators.

Comparison and Relational Operators

Comparison operators are used to compare operands in basic and quantified predicates. Relational operators are used to compare operands in all other predicates. See Predicates.

Both comparison and relational operators perform essentially similar functions. However, comparison operators are common to most programming languages, while the relational operators are more or less specific to SQL.

Comparison Operators

Comparison operator
Explanation
=
equal to
<>
not equal to
<
less than
<=
less than or equal to
>
greater than
>=
greater than or equal to

Quantifiers

Quantifier
ALL
SOME
ANY

Relational Operators

Relational operator
BETWEEN
EXISTS
IN
IS
LIKE
OVERLAPS

Logical Operators

Logical operator
AND
OR
NOT

The operators AND and OR are used to combine several predicates to form search conditions, see Search Conditions.

The operator NOT may be used to reverse the truth value of a predicate in forming search conditions. This operator is also available in predicate constructions to reverse the function of a relational operator, see Search Conditions.

Operator Precedence

Category
SQL
Postfix
(, )
Unary, complement
+, -, ~
Multiplicative
*, /, %
Additive, concatenation
+, -, ||
Shift
<<, >>
Bitwise AND
&
Bitwise OR
|
Comparison operators
=, <>, <, <=, >, >=, IS, IS DISTINCT FROM
Negation
NOT
Boolean AND
AND
Boolean OR
OR
Assignment
=

Examples

Expression
Parenthesized execution order
not false = true
not (false = true)
a | b & c | d = 110
(a | (b & c) | d) = 110
~10 + 5 * 8
(~10) + (5 * 8)

Please note that the precedence of operators vary between different database vendors. Use parenthesis to make sure the operations are executed as intended.

Standard Compliance

This section summarizes standard compliance concerning operators.

Standard
Compliance
Comments
SQL-2011
Core
Fully compliant.

Mimer SQL extension
Mimer SQL Experience:
Support for % modulo operator is a Mimer SQL extension.
Support for bit operators is a Mimer SQL extension.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX