SQL Operators

This topic provides selected notes and discussion on operators that are built into the Manifold query engine.  The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands, functions, and constants

Constants

See the Identifiers, Constants and Literals topic for some useful constants.

Operators

- <value>

Negation

<value> ^ <value>

Exponentiation

<value> % <value>

Modulo.  Divides the first value by the second value and returns the remainder.

<value> MOD <value>

Modulo.  Divides the first value by the second value and returns the remainder.  4 MOD 3 is 1.  22 MOD 5 is 2.

<value> DIV <value>

Integer division. Divides the first value by the second value and returns the integer part of the result.   3 DIV 4 is zero.  7 DIV 5 is 1.  22 DIV 5 is 4.

<value> / <value>

Division

<value> * <value>

Multiplication

<value> - <value>

Subtraction

<value> + <value>

Addition for numbers, concatenation of strings.

<value> & <value>

Concatenation of strings.

BITNOT <value>

Bitwise NOT: Result is opposite of the corresponding bit in the argument.

<value> BITAND <value>

Bitwise AND: Result is 1 if corresponding bits in both arguments are 1.

<value> BITOR <value>

Bitwise OR: Result is 1 unless corresponding bits in both arguments are 0.

<value> BITXOR <value>

Bitwise XOR: Result is 1 if corresponding bits in both arguments are different.  Result is 0 if corresponding bits in both arguments are the same.

<value> LIKE <value>

Matches first value to a string pattern that may contain wild cards such as  '%' (matches any number of characters, including zero characters) and '_' (a single character).  See the LIKE Operator topic for examples.

 

SELECT [NAME] FROM [Mexico Table]

  WHERE [NAME] LIKE 'Dur%';

 

Returns Durango.

 

SELECT [NAME] FROM [Mexico Table]

  WHERE [NAME] LIKE '%an%';

 

Returns Guanajuato, Michoacan de Ocampo, Yucatan, Quintana Roo, Durango and San Luis Potosi.

 

<value> <> <value>

Not equal to.   

 

Either <value> can be a number or a numeric tile, allowing comparisons between two numbers, two tiles, or between a tile and a number.  When a tile is an operand, the result will be a numeric tile with values of 0 for FALSE or 1 for TRUE, giving the result of the comparison between that tile value and the number or corresponding tile value.  

<value> = <value>

Equal to.   

 

Either <value> can be a number or a numeric tile, allowing comparisons between two numbers, two tiles, or between a tile and a number.  When a tile is an operand, the result will be a numeric tile with values of 0 for FALSE or 1 for TRUE, giving the result of the comparison between that tile value and the number or corresponding tile value.  

<value> >= <value>

Greater than or equal to.   

 

Either <value> can be a number or a numeric tile, allowing comparisons between two numbers, two tiles, or between a tile and a number.  When a tile is an operand, the result will be a numeric tile with values of 0 for FALSE or 1 for TRUE, giving the result of the comparison between that tile value and the number or corresponding tile value.  

<value> > <value>

Greater than.   

 

Either <value> can be a number or a numeric tile, allowing comparisons between two numbers, two tiles, or between a tile and a number.  When a tile is an operand, the result will be a numeric tile with values of 0 for FALSE or 1 for TRUE, giving the result of the comparison between that tile value and the number or corresponding tile value.  

<value> <= <value>

Less than or equal to.   

 

Either <value> can be a number or a numeric tile, allowing comparisons between two numbers, two tiles, or between a tile and a number.  When a tile is an operand, the result will be a numeric tile with values of 0 for FALSE or 1 for TRUE, giving the result of the comparison between that tile value and the number or corresponding tile value.  

<value> < <value>

Less than.   

 

Either <value> can be a number or a numeric tile, allowing comparisons between two numbers, two tiles, or between a tile and a number.  When a tile is an operand, the result will be a numeric tile with values of 0 for FALSE or 1 for TRUE, giving the result of the comparison between that tile value and the number or corresponding tile value.  

<value> BETWEEN <lower> AND <upper>

Returns True (1) if the <value> is greater than or equal to the <lower> bound and is less than or equal to the <upper> bound.   For sensible results, the <lower> value should be less than the <upper> value.  

 

The function returns False (0) if the <lower> value is greater than the <upper> value, no matter what the <value> may be.  That is an annoying way to enforce the assumption that the <lower> value should be lower than the <upper> value, but since virtually all other databases do that for BETWEEN, Manifold does that as well.  

 

? 5 BETWEEN 4 AND 7

 

Returns true.

 

? 5 BETWEEN 7 AND 4

 

Returns false.

 

? 9 BETWEEN 4 AND 7

 

Returns false.

 

Any or all of the <value>, <lower>, and <upper> operands can be numbers or numeric tiles.   When one or more operands is a tile,  the result will be a numeric tile with values of 0 for FALSE or 1 for TRUE, giving the result of the BETWEEN comparison using that tile value and the numbers or corresponding tile values that are the other operands.

 

For example, if Height is a tile field,

 

[Height] BETWEEN 300 AND 500 

 

would create a numeric tile where 1 values mark pixels where the value in the Height tile was greater than or equal to 300 and less than or equal to 500, with  0 values marking pixels otherwise.

 

If OldHeight and Height are both tile fields,

 

500 BETWEEN [OldHeight] AND [Height] 

 

would create a numeric tile where 1 values mark pixels where the value in the OldHeight tile was less than or equal to 500 and  the value in the Height tile was greater than or equal to 500, with 0 values otherwise.   

 

Keep in mind that since the <lower> bound should be less than the <upper> bound, any pixels in the above example where OldHeight values are greater than Height values will have a result tile pixel value of 0 no matter how either of those height values compare to 500.

 

<value> IN (<query>)

Returns True (1) if the value occurs in the results of the query.

 

? 'Durango' IN (SELECT [NAME] FROM [Mexico Table])

 

Returns 1.

 

? 'Vermont' IN (SELECT [NAME] FROM [Mexico Table])

 

Returns 0.

<value> IN (<value>, ...)

Returns True (1) if the first value occurs in the list of values.

 

? 'Tom' IN ('Tom', 'Dick', 'Harry')

 

Returns 1.

 

? 'John' IN ('Tom', 'Dick', 'Harry')

 

Returns 0.

<value> IS NULL

Returns True (1) if the value is NULL.

 

? (3 DIV 0) IS NULL

 

Returns 1.

 

? (3 DIV 2) IS NULL

 

Returns 0.

NOT <value>

Returns True (1) if the value is boolean False (0)

 

? NOT (7 < 5)

 

It is False that 7 is less than 5, so the above returns 1.

 

? NOT (3 < 5)

 

It is True that 3 is less than 5, so the above returns 0.

 

Works with numeric tiles as well, where the tile contains values of 0 or any other number.  0 is interpreted as FALSE and any other value is TRUE.   The result is a numeric tile with 0 values for FALSE and 1 values for TRUE.

<value> AND <value>

Returns True (1) if both values are True.  The classic AND logic operator.

 

Works with numeric tiles as well, where the tile contains values of 0 or any other number.  0 is interpreted as FALSE and any other value is TRUE.   The result is a numeric tile with 0 values for FALSE and 1 values for TRUE.

<value> OR <value>

Returns True (1) if either value is True.  The classic OR logic operator.

 

Works with numeric tiles as well, where the tile contains values of 0 or any other number.  0 is interpreted as FALSE and any other value is TRUE.   The result is a numeric tile with 0 values for FALSE and 1 values for TRUE.

<value> XOR <value>

Returns True (1) if one and only one value is True.    Another way to say the same thing:  Returns True (1) if the values are different, that is, one is True and one is False.     Returns False (0) if both values are True or both values are False.  The classic XOR logic operator.

 

Works with numeric tiles as well, where the tile contains values of 0 or any other number.  0 is interpreted as FALSE and any other value is TRUE.   The result is a numeric tile with 0 values for FALSE and 1 values for TRUE.

CAST (<value> AS <type>)

Converts the data type of the value into the specified type.

CASTV (<value> AS <type>)

Takes a vector or tile value and converts the data type of all contained values to the specified type.

 

CASTV ([vector-of-float64] AS INT32)

 

Produces a vector of INT32 values.

 

CASTV ([tile-of-uint8x3] AS FLOAT32)

 

Produces a tile of FLOAT32X3.   The number of channels does not change.

CASE WHEN <condition> THEN <value> ... ELSE <value> END

Provides If-Then-Else logic for queries.  When the condition evaluates to True (1) the THEN value is returned.  The ELSE part is optional, and if provided when the condition does not evaluate to True (1) the ELSE value is returned.  

 

? CASE WHEN (1 > 0) THEN 5 ELSE 9 END

 

Returns 5, since the condition of one being greater than zero is always true.

 

? CASE WHEN (0 > 1) THEN 5 ELSE 9 END

  

Returns 9, since the condition of zero being greater than one is never true.

CASE <compared-value> WHEN <value> THEN <value> ... ELSE <value> END

Similar to CASE WHEN <condition> THEN <value> ... ELSE <value> END  except that it allows a series of WHEN <value> THEN <value> pairs to test against the compared value.

 

Suppose we would like to set the number of THREADS to be used to a larger number with more CPUs but not so large as to use all CPUs, so we leave some CPUs free for other processes.   The fragment below returns a value based on the number of CPUs reported:

 

?   CASE SystemCpuCount()

        WHEN 8 THEN 5

        WHEN 4 THEN 3

        ELSE 1

    END

 

The compared value above is the number of CPUs returned by the SystemCpuCount function.  When the number of CPUs reported is 8 then the expression evaluates to 5,  when the number of CPUs is 4 the expression evaluates to 3, and otherwise the expression evaluates to 1.  The expression above evaluates to 5 for a typical Core i7 machine with hyperthreading enabled.

 

The above expression could be used as the argument to the THREADS command:

 

THREADS

    CASE SystemCpuCount()

        WHEN 8 THEN 5

        WHEN 4 THEN 3

        ELSE 1

    END

 

(A useful example written by SQL master Tim Baigent.)

EXISTS <table>

Returns True (1) if the argument contains at least one record.

 

Vector (Tuple) Operators

Vector numeric values, also known as composite numeric values, are pairs or triples or quads of numeric values of types XXXx2, XXXx3, XXXx4 respectively, where XXX can be any of the supported numeric data types. for example, float32.   We refer to vector values as x2, x3, x4, or, taken together, xN vector values. Vector values are very useful for representing points in 2D, 3D and 4D spaces, coordinates of rectangles, values in various color spaces like BGR, BGRA when adding an alpha channel to RGB color space, and so on.  Comparison operators for vectors compare the component parts of the vector in order.

 

Example:  For the numeric type float64 the xN types available are float64, float64x2, float64x3, and float64x4.   float64x3 is a data type consisting of a triplet of numbers each of which is a float64.

 

(<value>, ...) <> (<value>, ...)

Not equal to

(<value>, ...) = (<value>, ...)

Equal to

(<value>, ...) >= (<value>, ...)

Greater than or equal to

(<value>, ...) > (<value>, ...)

Greater than

(<value>, ...) <= (<value>, ...)

Less than or equal to

(<value>, ...) < (<value>, ...)

Less than

(<value>, ...) BETWEEN (<value>, ...) AND (<value>)

Returns True (1) if the first value is between the second and third values.

(<value>, ...) IN (<query>)

Returns True (1) if the vector value occurs in the results of the query.

CASTV (<value> AS <type>)

Takes a vector or tile value and converts the data type of all contained values to the specified type.

 

CASTV ([vector-of-float64] AS INT32)

 

Produces a vector of int32 values.

 

CASTV ([tile-of-uint8x3] AS FLOAT32)

 

Produces a tile of float32x3 values.   The number of channels does not change.

 

Notes

New operators - The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of operators, commands and functions.

 

Operators for UUID and Binary Values -  How selected operators and functions support Universally Unique Identifier (UUID) and binary (VARBINARY) values:

 

 

 

Precedence - The order of precedence for operators is:  arithmetic operators > BITxxx > comparison operators > IS NULL > BETWEEN > logical operators

 

Division by zero - returns NULL.

 

Always False, when not True - Operators that return True (1) when the specified condition is satisfied will always return False (0) otherwise.

 

All types of values - Operators and functions generally  support all types of values so that, for example, we can use comparison or boolean operators on tiles and not just on scalar values.  

 

Everything Math - For a handy reference to anything in mathematics, see the Wolfram MathWorld site.   Thank you, Wolfram!

 

See Also

Tables

 

Add an Index to a Table

 

Functions

 

Queries

 

Regular Expressions

 

Command Window

 

Command Window - Query Builder

 

Identifiers, Constants and Literals

 

SQL Statements

 

SQL Functions

 

Temporary Databases

 

EXECUTE